formulae to calculate sub totals

[AmyTaylor]

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

[Bob Phillips]

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" <AmyTaylor.25w261_1144404014.0823@excelforum-nospam.com> wrote
in message news:AmyTaylor.25w261_1144404014.0823@excelforum-nospam.com...
>
> 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
>

[AmyTaylor]

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