Shortening a formula with multiple ranges.

**Jonathan78** · 10-02-2011, 10:43 AM

Hello all,

I use the following formula for doing some calculations. now I need to extent it to G59:M59.
This will make the formula monstrous.

Is there a better way to do this?

Please Login or Register  to view this content.

The whole formula is:

Please Login or Register  to view this content.

**daddylonglegs** · 10-02-2011, 10:54 AM

Hello Jonathan78,

You can replace your current long formula with this version - should give you the same result

=52/SUBTOTAL(104, C22:C42)*SUMPRODUCT(G49:M52*$AF$6:$AF$9)/52/J20

so assuming you want to extend that to row 59 (and presumably the AF range would extend to AF16?) then try

=52/SUBTOTAL(104, C22:C42)*SUMPRODUCT(G49:M59*$AF$6:$AF$16)/52/J20

Note: you could also cancel out the 2 52s and use this version...

=1/SUBTOTAL(104, C22:C42)*SUMPRODUCT(G49:M59*$AF$6:$AF$16)/J20