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

"JDaywalt" wrote:

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.