I have report where output is EXL07 FORMULA. The users generate the report in EXL07 FORMULA, then users input one of the fields and it calculates another field. After the spreadsheet generates, users want to be able to play with the numbers where totals automatically recalc in spreadsheet - this is after report is generated. I am trying to find a way to get a couple of columns to sum and another to recompute on the same row.

For example, the first column (COL1) gives a subtotal when report is generated. The second column (COL2) is a field user inputs after generation which automatically calcs the third column. I need the AVG_CHG column to take the subtotals for COL1 and COL3 (not the formula) and do a calc.. I can only get entire row to either SUBTOTAL all columns or RECOMPUTE all columns.

TABLE FILE ALL_DATA
SUM
FIELD1
FIELD2
COMPUTE COL1/D14.2M = FIELD1 * FIELD2;
COL2 (this field generates blank and user inputs to calculate COL3
COMPUTE COL3/D14.2M = FIELD1 * COL2;
COMPUTE AVG_CHG/D12.2% = ((COL3-COL1)/COL1);
BY SORT
-*I need subtotals for below adding the totals in columns
ON SORT SUBTOTAL
COL1
COL3
-*I need AVG_CHG column to be a formula taking subtotals of COL1 and COL3, not using the formulas for COL1 AND COL3
ON SORT RECOMPUTE
AVG_CHG
ON TABLE PCHOLD FORMAT EXL07 FORMULA
END
-EXIT

Any help is appreciated

This message has been edited. Last edited by: FP Mod Chuck,

If my understanding is good, here is the solution:

TABLE FILE CAR
SUM RETAIL_COST NOPRINT
    DEALER_COST NOPRINT
    COMPUTE NRETAIL  /P8.2C = RETAIL_COST / 4;   AS 'RETAIL_COST'
    COMPUTE NDEALLER /P8.2C = DEALER_COST / 100; AS 'DEALER_COST'
    COMPUTE AVG      /P6.2% = (RETAIL_COST - DEALER_COST) / RETAIL_COST * 100;
BY COUNTRY
ON COUNTRY SUMMARIZE
END