Excel formula help needed

[SteveFox]

Could someone tell me the excel formula to do the below?

If the 1st word (in cell A1) is the same as the 1st word of the 2nd row (A2) then add the 3rd word of the 1st row (1C) and the 3rd word of the 2nd row (2C) and divide the sum of the 4th word of the 1st and 2nd row (D1 and D2) and put the result into a cell with only the 1st word and result of the division. And I want this to loop until it reachs the last row.

e.g. I have a file which contains 6 rows below:

AAA Unit1 60 39
AAA Unit7 30 15
BBB Unit3 80 60
CCC Unit4 50 25
CCC Unit8 90 45
CCC Unit9 70 40

I want it to output 3 row below:

AAA 0.6
BBB 0.75
CCC 0.52


The result for AAA 0.6 was calculated by (39+15)/(60+30) taken from 1st and second row.


Any help will be greatly appreciated.

[Barb Reinhardt]

Let's say your six rows are in A1-D6

A9 = "AAA"
A10 = "BBB"
A11 = "CCC"

Try this
B9 =SUMIF(A$1:A$6,A8,D$1:D$6)/SUMIF(A$1:A$6,A8,C$1:C$6)

"SteveFox" <SteveFox.1yvl1m_1132624502.6058@excelforum-nospam.com> wrote in
message news:SteveFox.1yvl1m_1132624502.6058@excelforum-nospam.com...
>
> Could someone tell me the excel formula to do the below?
>
> If the 1st word (in cell A1) is the same as the 1st word of the 2nd row
> (A2) then add the 3rd word of the 1st row (1C) and the 3rd word of the
> 2nd row (2C) and divide the sum of the 4th word of the 1st and 2nd row
> (D1 and D2) and put the result into a cell with only the 1st word and
> result of the division. And I want this to loop until it reachs the
> last row.
>
> e.g. I have a file which contains 6 rows below:
>
> AAA Unit1 60 39
> AAA Unit7 30 15
> BBB Unit3 80 60
> CCC Unit4 50 25
> CCC Unit8 90 45
> CCC Unit9 70 40
>
> I want it to output 3 row below:
>
> AAA 0.6
> BBB 0.75
> CCC 0.52
>
>
> The result for AAA 0.6 was calculated by (39+15)/(60+30) taken from 1st
> and second row.
>
>
> Any help will be greatly appreciated.
>
>
> --
> SteveFox
> ------------------------------------------------------------------------
> SteveFox's Profile:
> http://www.excelforum.com/member.php...o&userid=28975
> View this thread: http://www.excelforum.com/showthread...hreadid=487078
>

[SteveFox]

Thanks Barb!