Hi:
Look at the example of using the TAGATTR attribute in a STYLE override with TAGSETS.EXCELXP here:
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#formats
Notice that the formula is in Row/Column notation and not A1..A14 notation.
I believe you will also need to download an updated copy of the ExcelXP tagset. I don't believe that formulas worked in the original tagset that was shipped with SAS 9.1.3 -- but Tech Support could help you with that.
The issue with the example is that they are using proc print to touch every row with a formula and you want to change the subtotal line. So it's good that you're using PROC REPORT. You can send a formula along with the value -- so that if somebody changes a number, the subtotal automatically changes -- using the same TAGATTR syntax as shown in the Tech Support note.
I'm not a real whiz with Excel, so I don't know how you get around needing to know how many rows to include in the formula, but this example worked for me (assuming only 2 report rows and 1 header and 2 subtotal/total line. I always use =SUM in Excel, so I'm not sure what you mean by the SUBTOTAL function.
Perhaps Tech Support can provide more help.
cynthia
[pre]
data prdsale;
set sashelp.prdsale;
where country = 'GERMANY';
run;
ods tagsets.excelxp file='formula2.xls' style=statistical;
proc report data=prdsale nowd split='*'
style(header)={font_size=10pt font_weight=bold};
column country region predict actual;
where country = 'GERMANY';
define country / group;
define region / group;
define predict /'Predicted*Sales' style={tagattr='format:Currency'};
define actual / 'Actual*Sales' style={tagattr='format:Currency'};
compute predict;
** note for this formula, you have to know how report rows there are;
** and how many header rows there are in the final worksheet;
if _break_ = '_RBREAK_' then do;
Country = 'Total';
call define (_col_,'STYLE',
'style={tagattr="format:Currency formula:=SUM(R[-2]C:R[-1]C) "');
end;
endcomp;
compute actual;
if _break_ = '_RBREAK_' then do;
call define (_col_,'STYLE',
'style={tagattr="format:Currency formula:=SUM(R[-2]C:R[-1]C) "');
end;
endcomp;
rbreak after /summarize;
run;
ods tagsets.excelxp close;
[/pre]