Adjusting formula used across multiple columns to reflect change in ID along rows

[Wooblah]

Hi there,

Apologies in advance for any lack of etiquette but I would appreciate some help - I'm using a pretty basic, but quite long, formula to calculate weighted averages;

=((E13*($AH$13/SUM($AH$13:$AH$18)))+(E14*($AH$14/SUM($AH$13:$AH$18)))+(E15*($AH$15/SUM($AH$13:$AH$18)))+(E16*($AH$16/SUM($AH$13:$AH$18)))+(E17*($AH$17/SUM($AH$13:$AH$18)))+(E18*($AH$18/SUM($AH$13:$AH$18))))

So in this case the data is from rows 13 to 18, there are also several columns containing data as well so I'm dragging across after calculating for E. If for example, I then wanted to calculate the weighted averages for the same columns from rows 19 to 27, 28 to 43, .... 3767 to 3776 and so on.. is there a quicker way to apply this formula than altering the formula each time?

Thanks

 

[ukmikeb]

Hi

Welcome to MrExcel Forum.

Does this formula :-

=SUMPRODUCT(E$13:E$18*$AH$13:$AH18/SUM($AH$13:$AH$18))

give you the same results as your formula?

 

[Wooblah]

It does, cheers for that!

So is there any way to apply this formula to a different range of cells other than typing in changes or starting from scratch each time?

Thanks

 

[ukmikeb]

Hi

That's great and it reduce the formula too.

You don't say what cell that formula is in, other than it is dragged across to reflect on columns F/AI..etc.

Would you want the formula

=SUMPRODUCT(E$19:E$27*$AH$19:$AH27/SUM($AH$19:$AH$27))

to be on the row immediately below the other formula?

Also, I notice that the next few areas are 9 and 16 rows as opposed to 6 rows, is there any pattern there?

Whatever that case the following variation of my formula could represent a less laborious way of replicating it :-

=SUMPRODUCT(OFFSET(E$13,,,6,1)*OFFSET(AH$13,,,6,1)/SUM(OFFSET(AH$13,,,6,1)))

albeit that it is now volatile.

hth