Sumproduct with Autofilter

[JDaywalt]

I have seen several posts that provide recommendations for calculating
Sumproduct when using an autofilter. All of the recommendations require use
of the Offset function to achieve the result. While I have tried several of
the suggested calculations and they do work correctly, the issue I have is
that using Offset makes the recalculation process much lengthier---and
because Offset is a "volatile" function it triggers a recalculation anytime I
make a change anywhere in the spreadsheet. Two questions:

Here is an example of what I'm trying to do:

Range E5:E1000 contains unit volume in whole numbers (i.e. 1,200)
Range L5:L1000 contains dollar values (i.e. $12.45)

I need to calculate the Sumproduct for these two columns, then divide this
result by the total unit volume to achieve a weighted average "rate". Here
is the formula I am using in row 4 -- again, it does work correctly:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E5:E65536)-ROW(E4),1)),E5:E65536,L5:L65536)/E3

Is there any other way to calculate this"weighted average rate" without
using the Offset function---or any other 'volatile' function? Perhaps using
VB in some creative way? I realize a simple solution would be to could carry
out the Volume * Rate calculation for each row & then perform the subtotal
calculation at the top, but this would require me to insert 64 calculated
columns into a worksheet that already contains 205 columns---not very
efficient

Any assistance or ideas would be greatly appreciated.

 

[Bob Phillips]

If you want speed, calculate the sums in another column, say M, =E5*L5 etc.,
and use

=SUBTOTAL(9,M:M6)/E3

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Typo, should be

=SUBTOTAL(9,M:M)/E3

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JDaywalt]

Yes, I do realize this would be an option, but I have 64 extra columns that
would have to be created due to the number of "weighted average"
calculations/columns I need. Any other options you can think of (i.e. even
using VB) that might be more efficient for me? If there is no other option,
then I'll just have to figure out which is the lesser of two evils!!

Thanks for your help

 

[Bob Phillips]

Well let's start by stating that an array formula, and SP is an array
formula, it just isn't array entered, is the worst choice in your
circumstances. You have too much data, too many formulae to ever make it
efficient.

The helper columns, whilst many, would be more efficient.

VBA is probably the best solution, but if you want some help on that you
need to map out the data clearly and determine what will trigger the VBA.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)