I have a user request where they want a formula to be calculated within the resulting XLSX report, so that the calculated value updates when they manipulate the data in the sheet.

The problem I'm having is that the calculation is based on a MIN, MAX and AVE of several rows, hence why I used RECAP for the calculation, but no matter what I try, I seem to get fixed values in my RECAP formula instead of column references.

It there a way around this?

Here's a simplified example using the CAR table:

APP PREPENDPATH IBISAMP

SET CENT-ZERO = ON
SET CDN = OFF

TABLE FILE CAR
SUM
	MAX.SEATS		NOPRINT
	MIN.SEATS		NOPRINT
	AVE.SEATS		NOPRINT

BY COUNTRY			NOPRINT
BY CAR
	COMPUTE STV/D12.3% = 100 * (MAX.SEATS - MIN.SEATS)/AVE.SEATS; AS 'STV'

SUM
	MAX.SEATS


BY COUNTRY			NOPRINT
BY CAR
BY MODEL

ON CAR SUBFOOT
"STV: <STV"

ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE SET HTMLENCODE ON
END

This message has been edited. Last edited by: Wep5622,

Not 100% sure of what you are looking for but it seems that to have the formula referenced with the column and not the value it needs to be visible columns
Also, since you refer the COMPUTE in the subfoot, it only contain the result (value) and not the formula itself
To have all displayed, seems to be something such as below

SET CENT-ZERO = ON
SET CDN = OFF

TABLE FILE CAR
SUM	MAX.SEATS AS 'Max Seats,Per Car'
	MIN.SEATS AS 'Min Seats,Per Car'
	AVE.SEATS AS 'Ave Seats,Per Car'
    COMPUTE STV/D12.3% = 100 * (MAX.SEATS - MIN.SEATS)/AVE.SEATS; AS 'STV'
BY COUNTRY    NOPRINT
BY CAR
SUM	MAX.SEATS AS 'Max Seats,Per Model'
BY COUNTRY    NOPRINT
BY CAR
BY MODEL
ON CAR SUBFOOT
"STV: <STV"
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE SET HTMLENCODE ON
END

Then if I change a value in either column B, C or D of the Excel, the E column then change accordingly but not your subfoot which does not contain a formula

quote:

I seem to get fixed values in my RECAP formula instead of column references.

It seems like the formulas don't come through when dividing totals by totals. I would submit a case.

Also, as MartinY states, if a column is hidden, then there cannot be a cell reference to it in Excel because that cell doesn't exist in the Excel sheet. So, it will just put in the value of the hidden cell.

One thing that I did notice, is that you are using AVE.SEATS on an integer field. This causes the decimal of the average calculation to be truncated. If you want a true average, I would calculate it as SUM.SEATS/CNT.SEATS

Run the code below and see the differences in calculations

  
SET CENT-ZERO = ON
SET CDN = OFF
SET PAGE-NUM = OFF

TABLE FILE CAR
SUM 
SUM.SEATS WITHIN CAR AS 'SUM,SEATS'
CNT.SEATS WITHIN CAR
MAX.SEATS WITHIN CAR
MIN.SEATS WITHIN CAR
AVE.SEATS/D12.3 WITHIN CAR
COMPUTE AVGSEATS/D12.3=SUM.SEATS/CNT.SEATS; AS 'AVE,SEATS2'
COMPUTE STV/D12.3%  = 100 * (MAX.SEATS - MIN.SEATS)/AVE.SEATS;
COMPUTE STV2/D12.3% = 100 * (MAX.SEATS - MIN.SEATS)/AVGSEATS;
BY COUNTRY
BY CAR RECOMPUTE SUM. SUM.SEATS SUM. CNT.SEATS MAX. MAX.SEATS MIN. MIN.SEATS AVE. AVE.SEATS SUM. AVGSEATS SUM. STV SUM. STV2
BY MODEL
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE NOTOTAL
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/flat.sty,$
TYPE=DATA, COLUMN=N9, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=DATA, COLUMN=N10, BACKCOLOR=RGB(255 255 128), $
TYPE=DATA, COLUMN=N11, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=DATA, COLUMN=N12, BACKCOLOR=RGB(255 255 128), $
TYPE=TITLE, COLUMN=N9, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=TITLE, COLUMN=N11, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=SUBTOTAL,BACKCOLOR='SILVER',$
TYPE=SUBTOTAL, COLUMN=N9, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
TYPE=SUBTOTAL, COLUMN=N11, BORDER-LEFT=LIGHT,BORDER-LEFT-COLOR=BLACK,$
ENDSTYLE
END

This message has been edited. Last edited by: Hallway,

Thanks Hallway, adding the extra columns does seem to do the trick!

And indeed, being able to get column references in formulas on totals directly would be great. That looks like a good candidate for an NFR.

P.S. Good points on the hidden columns and the integer column - in our real scenario that's thankfully a value with precision.