formulae to calculate sub totals
 

Hi everyone, please can someone help me with the following, not sure if
a formulae or vba is the best option....?

I have a table, with data ranging from D16 to G364. In column C is a
row heading, and at various times it sub-totals the data and includes
the word total, eg: Type 1 Total, Type 2 Total etc etc.
to give you some idea, it looks similar to the output from a pivot
table.

In column H is a formulae that gives the ratio of the data in cols D to
the sub total for that section.
Is there some way of creating a formulae that can be copied down easily
to create a dynamic calculation for each row.
For example. D16 to D22 contains the individual data, and D23 the sub
total, D24 then has the first figure for the next section which might
go down to D28, and D29 would include the next subtotal.
Is there someway to write a formula that will calculate only the ratio
of the figure to that section, and then go onto the next section if it
is a new section.

Hope this makes sense !?
Thanks
Love amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=530856

formulae to calculate sub totals
 

Try this

=IF(ISNUMBER(FIND("Total",C16)),"",D16/INDEX(D16:$D$32,MIN(IF(ISNUMBER(FIND(
"Total",C16:$C$32)),ROW(C16:$C$32)))-MIN(ROW(C16:$C$32))+1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Adjust the 32 to your last row.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor" wrote
in message ...

Hi everyone, please can someone help me with the following, not sure if
a formulae or vba is the best option....?

I have a table, with data ranging from D16 to G364. In column C is a
row heading, and at various times it sub-totals the data and includes
the word total, eg: Type 1 Total, Type 2 Total etc etc.
to give you some idea, it looks similar to the output from a pivot
table.

In column H is a formulae that gives the ratio of the data in cols D to
the sub total for that section.
Is there some way of creating a formulae that can be copied down easily
to create a dynamic calculation for each row.
For example. D16 to D22 contains the individual data, and D23 the sub
total, D24 then has the first figure for the next section which might
go down to D28, and D29 would include the next subtotal.
Is there someway to write a formula that will calculate only the ratio
of the figure to that section, and then go onto the next section if it
is a new section.

Hope this makes sense !?
Thanks
Love amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=530856

formulae to calculate sub totals
 

Thanks Bob, will give it a go.
Love Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=530856