Adding Formulas to Subtotal rows

[Guest]

I have a large amount of data on a spread sheet which has been sorted and subtotaled (using the “Subtotal†function on the data menu). On each subtotal row I would like to have formulas that use the results of that row’s subtotals (ie divide the value of one subtotal by the value of another subtotal on the same row). At the moment I am creating the formulas on one subtotal row and then copying them to each of the remaining subtotal rows – one row at a time. This seems to be cumbersome – is there a faster way?

Thanks

 

[Frank Kabel]

Hi
one way: use a formula like the following
=IF(ISNUMBER(FIND("Total",A1)),your_calculation,"")
and copy this down for all rows

 

[Ken Wright]

Assuming you have NO blanks in your data other than the ones in the subtotal
row, select the column you wish to put your formula, do Edit / Go To / Special /
Blanks, then type = use mouse/cursor to select 1st subtotal, type / then use
mouse/cursor to select 2nd subtotal, then hit CTRL+ENTER once to enter the
relative formula to all the blanks in the other subtotal rows.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I have a large amount of data on a spread sheet which has been sorted and

subtotaled (using the "Subtotal" function on the data menu). On each subtotal
row I would like to have formulas that use the results of that row's subtotals
(ie divide the value of one subtotal by the value of another subtotal on the
same row). At the moment I am creating the formulas on one subtotal row and then
copying them to each of the remaining subtotal rows - one row at a time. This
seems to be cumbersome - is there a faster way?

 

[Guest]

Thanks Frank and Ken - by using both of your suggestions I was able to speed up the process significantly. Much appreciated!!