Sum Range + Divide by another range

**bunny81255** · 08-28-2007, 11:09 PM

Having trouble with a formula. Not very experienced. Maybe you guys can help. Example: My data has a classifier in column J. If cell J34 and cell J42 has a "1", I want to multiply G34 times H34 and then add this to G42 times H42 and then divide this by the sum of G34 and G42 for a weighted average.

Thanks in advance for any help

**Special-K** · 08-29-2007, 05:10 AM

=IF(AND(J34=1,J42=1),(G34*H34+G42*H42)/(G34+G42),"J34 or J42 not equal 1")

You havent said what happens if J34 or J42 dont equal 1
so Ive just generated a piece of text to that effect

**bunny81255** · 08-29-2007, 09:51 PM

I should have been more specific. If any row in column J has a "1", I want to multiply the data in column G times column H of that row. This will repeat for any row that has a "1" and the "1's" will not be in consecutive rows. Then the sum of the multiplications will be added together and divided by the sum of data in column G that has a corresponding "1" in column J. I hope this is a better explanation.

Thanks

**deadlyduck** · 08-31-2007, 08:07 PM

Here's a formula which should work using the assumption that your data is in G1-G6, H1-H6 and your classifier is in J1-J6:

=SUMPRODUCT(G1:G6,H1:H6)/SUMIF(J1:J6,"=1",G1:G6)

Obviously you need to change the G1-G6 etc cell references to the actual references you're using in your spreadsheet.