computations at odd locations

[Utkarsh]

Hi

I have data available as follows:
Segment Value Percentage
A 2
B 7
C 5
Total
X 3
Y 2
Total
Q 4
W 6
E 3
R 8
Total

Unforrtunately, as you can see, the rows are unequal. I need to put
totals in the cell adjacent to where "Total" occurs. Also, based on
the totals percentage break-ups for each group needs to be computed.
Can someone suggest a way out?

 

[Bernie Deitrick]

Utkarsh,

You could, of course, use a macro, but here is another way, without using macros.

I have assumed that your data is in columns A and B. starting in row 1 with headers, and row 2 with
data.

Select column B, then use Edit / Go to... Special Blanks, OK. Make a mental note of the row
number of the first blank cell.

Then type this formula (based on an old accounting trick...)

=SUM($B$2:B???)-2*SUMIF($A$2:A???,"Total",$B$2:B???)

replacing the ??? with the row number of the row ABOVE the first blank cell - for your example
table, witht he first blank in B5, you would use

=SUM($B$2:B4)-2*SUMIF($A$2:A4,"Total",$B$2:B4)

and then press Ctrl-Enter to enter that formula into every blank cell in column B.

In cell C2, enter this formula

=IF(A2<>"Total",B2/VLOOKUP("Total",A2:B20,2,FALSE),"")

and copy down to match column B.


HTH,
Bernie
MS Excel MVP

 

[Daryl S]

Utkarsh -

Assuming your data is in columns A-C with headers in row 1 and data starting
in row 2, you can get what you want by adding two columns.
Do not put anything in the normal header row D1.
In D2, enter
=IF(A1="Total",B2,B2+D1)
In E2, enter
=IF(A2="Total",B2,E3)
In C2, enter
=IF(A2="Total",1,B2/E2)
Drag these down the columns. column C will have the correct percents.
This all depends on the word Total being in column A at the end of each block.