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.