@Tom wrote:
The issue seems to be with the extremely long string required by the formula. I cannot figure out if there is a way to prevent the length from causing the formula to be seen as text by Excel.
Perhaps using this FORMULA tag will work instead, that does not seem to be impacted by the use of a very long formula.
So assuming that the original request to sum every other column from column 23 to 73 could be recoded as meaning to sum every other relative cell from -1 to -51 then perhaps something like this will work.
define total_score / computed
style(column)={width=1in tagattr='formula:RC[-1]+RC[-3]+RC[-5]+RC[-7]+RC[-9]+RC[-11]+RC[-13]+RC[-15]+RC[-17]+RC[-19]+RC[-21]+RC[-23]+RC[-25]+RC[-27]+RC[-29]+RC[-31]+RC[-33]+
RC[-35]+RC[-37]+RC[-39]+RC[-41]+RC[-43]+RC[-45]+RC[-47]+RC[-49]+RC[-51]'};
compute diff;
Yes, the issue does seem to be the length of the formula. I experimented and found that I could add no more than 8 columns before Excel started displaying the formula instead of calculating it. So I created a work-around by putting portions of the formula in columns further out to the right, and then summing those columns. I used the absolute_column_width setting in the ODS Excel options to give these extra columns a width of 0 so they are not visible. It was tedious, but it works.
/*Maximum of 8 columns, or else Excel will display formula rather than value*/
formula1='=W3+Y3+AA3+AC3+AE3+AG3+AI3+AK3';
formula2='=AM3+AO3+AQ3+AS3+AU3+AW3+AY3+BA3';
formula3='=BC3+BE3+BG3+BI3+BK3+BM3+BO3+BQ3';
formula4='=BS3+BU3';
formula_comb='=BX3+BY3+BZ3+CA3';
Excelformula1=tranwrd(formula1,'3',cats(_n_+2)); /*Enter in Column BX*/
Excelformula2=tranwrd(formula2,'3',cats(_n_+2)); /*Enter in Column BY*/
Excelformula3=tranwrd(formula3,'3',cats(_n_+2)); /*Enter in Column BZ*/
Excelformula4=tranwrd(formula4,'3',cats(_n_+2)); /*Enter in Column CA*/
Excelformula_comb=tranwrd(formula_comb,'3',cats(_n_+2));
row_score = cats(Excelformula_comb,'*(20/26)'); /*Enter in Column BW*/
I didn't try the tagattr approach because I would basically have to start over, learning and programming a new approach. My deadline is too close to get into that. Maybe for next year's audit...
... View more
