J

#### JDaywalt

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.