Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct with Autofilter
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help using autofilter & Sumproduct | Excel Worksheet Functions | |||
Autofilter & Sumproduct | Excel Discussion (Misc queries) | |||
Using Sumproduct in Autofilter | Excel Worksheet Functions | |||
SUMPRODUCT with Autofilter | Excel Worksheet Functions | |||
SUMPRODUCT with AutoFilter | Excel Programming |