Hello,

I am trying to achieve the following report in excel formula. Any help would be appreciated.

  
         A		B			C		 	D	
1	ENGLAND		225			10			=B1/C1
2	JAPAN		100			15			=B2/C2
3	USA		400			25			=B3/C3
4
5	Total		=SUM(B1:B3)	         =SUM(C1:C3)	         =SUM(D1:D3)
6
7	Last Month	550			35			=B7/C7
9
10	Diff		=B5-B7		         =C5-C7		         =D5-D7

Thanks,
Sayed

You could generate the report as shown in FRL, but attemppting to hold an FRL report as Excel With Formulas results in...

(FOC3273) EXL2K: FORMULAS WILL NOT BE GENERATED WITH OVER OR FOR.

Jack,

Thanks for the suggestion but I need to be able to hold the formulas. The user needs to be able to update cell values and the formulas will dynamically chage values of other cells.

Sayed

I suggest you use an Excel template for your report -- the Detail data filled in by WF in one tab, and your presentation (with precoded formulas referencing the detail data) in another.

I thought about that but cannot use it for this case because the # of rows can change in my report.

Sayed

Sayed,

I think this could be an answer for you:

  
-* File sayed1.fex
DEFINE FILE CAR
XCOUNTRY/A10 = IF SEATS EQ 2 OR 5 THEN COUNTRY ELSE 'L. Month';
XSORT/I1S=IF SEATS EQ 2 OR 5 THEN 0 ELSE 1;
RC/D7=IF SEATS EQ 2 OR 5 THEN RCOST ELSE -RCOST;
DC/D7=IF SEATS EQ 2 OR 5 THEN DCOST ELSE -DCOST;
END
TABLE FILE CAR
SUM RC DC  COMPUTE RATIO/D7.2=IF XSORT EQ 0 THEN DC/RC ELSE -DC/RC;
BY XSORT AS '' 
BY XCOUNTRY AS Country
ON XSORT SUBTOTAL AS 'Total' 
WHEN XSORT EQ 0;

ON TABLE PCHOLD FORMAT EXL2K FORMULA

ON TABLE SET STYLE *
TYPE=REPORT, COLUMN= XSORT, COLOR=WHITE, $
ENDSTYLE
END

Danny,

Thanks for the input. Unfortunately, the excel formula the accounting department is trying to use is far more complex and manipulates too many cells. Therefore we decided to go with excel macros. We are getting the excel dump from WebFOCUS and then enable macro to divide the report into tabs with template and formula.

Thanks,
Sayed