Re: Sumproduct with Autofilter
- From: "Bob Phillips" <bob.NGs@xxxxxxxxxxxxx>
- Date: Fri, 23 May 2008 18:35:16 +0100
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)
"JDaywalt" <JDaywalt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F9C5B220-36E3-4BCE-8007-EA44D7F041DC@xxxxxxxxxxxxxxxx
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.
.
- Prev by Date: Re: Macro - move active cell to another column; same row
- Next by Date: Re: Referencing a row with a maximum value array?
- Previous by thread: Re: Sumproduct with Autofilter
- Next by thread: Duplicate in Row
- Index(es):
Relevant Pages
|
