Weighted Average witha filter

[Shawn]

To all

Can anyone help me debug the formula below.

I want to do a weighted average

=SUMPRODUCT(F5:F630,$E5:$E630)/SUM($E5:$E630)

but with a twist. Am trying to get the average but only of those fields
that have not been filtered

Trying the subtotal function but not having much luck.

(some things I tried are below!)

=SUMPRODUCT((SUBTOTAL(9,E5:E629)),(SUBTOTAL(9,F5:F628)))/SUBTOTAL(9,E5:E629)
plus reversing the sumtotal and subproduct functions etc.

AHGA

Shawn

 

[hgrove]

I'm going to cut to the chase: you can't do this without VBA
Specifically, you'd need a udf that takes a range argument and return
a single column, multiple row array with 1s for visible rows within th
range and 0s for other rows. That's the only alternative to SUBTOTAL fo
performing calculations on filtered records.


Function vr(rng As Range) As Variant
Dim i As Long, n As Long, rv As Variant

Application.Volatile 'practical necessity

n = rng.Rows.Count
ReDim rv(1 To n)

For i = 1 To n
rv(i) = IIf(rng.Rows(i).Hidden, 0, 1)
Next i

vr = rv
End Function


Use it as follows.

=SUMPRODUCT(vr($E5:$E630),F5:F630,$E5:$E630)
/SUMPRODUCT(vr($E5:$E630),$E5:$E630)

If I've misunderstood and you want to perform calculations on th
hidden values, change this to

=SUMPRODUCT(1-vr($E5:$E630),F5:F630,$E5:$E630)
/SUMPRODUCT(1-vr($E5:$E630),$E5:$E630

 

[Aladin Akyurek]

[...]

If I've misunderstood and you want to perform calculations on the
hidden values, change this to

=SUMPRODUCT(1-vr($E5:$E630),F5:F630,$E5:$E630)
/SUMPRODUCT(1-vr($E5:$E630),$E5:$E630)

This seems to be what the OP wants. The following would also work:

=SUMPRODUCT(1-SUBTOTAL(3,OFFSET($E5:$E630,ROW($E5:$E630)-MIN(ROW($E5:$E630))
,,1)),$E5:$E630,$F$5:$F$630)/SUMPRODUCT(1-SUBTOTAL(3,OFFSET($E5:$E630,ROW($E
5:$E630)-MIN(ROW($E5:$E630)),,1)),$E5:$E630)

 

[Shawn]

Thanks for the reply.

I had deduced the same thing and wrote a vba sub linked to a button but
yours is far more elegant.

One question about the sumproduct (the part I don;t understand very well)

With VR you test what in the range is true or false then make an array out
of the responses. I assume that if value is 1 then when multiplied it give
the value but if 0 then # * 0 = 0 (Very slick!!) Performing SUMs only
where particular conditions are met!


One other thing, I get a #value error when I place the function on a line.
The vr array is correct but the array doesn't evaluate

tried

=SUMPRODUCT(vr($E5:$E629),F5:F629,$E5:$E629)/SUBTOTAL(9,E4:E629)

since the subtotal should work at this point but.. no go.
( I know that "=SUMPRODUCT(F5:F629,$E5:$E629)/SUBTOTAL(9,$E5:$E629)" works
with non filtered columns)

tried
=SUMPRODUCT(vr($E5:$E629)*(F5:F629)*($E5:$E629))/SUMPRODUCT(vr($E5:$E629),$E
5:$E629) but still #value
tried
=SUMPRODUCT(vr($E5:$E629)*(F5:F629)*($E5:$E629)/SUMPRODUCT(vr($E5:$E629)*($E
5:$E629)))
but it gives the same value as if nothing is filtered??


(had to specify the multiply to get the formula to function)

Almost have it, any further enlightenment would be appreciated

Shawn

 

[Shawn]

Aladin,

Wow, that is some magic....

It does seem to work but the value doesn't seem to be correct, the response
above does give me a value but still debugging. Thanks for the help, I'll
need a week just to understand the formula you wrote..

Thanks for the help!

Shawn

[...]

If I've misunderstood and you want to perform calculations on the
hidden values, change this to

=SUMPRODUCT(1-vr($E5:$E630),F5:F630,$E5:$E630)
/SUMPRODUCT(1-vr($E5:$E630),$E5:$E630)

This seems to be what the OP wants. The following would also work:

=SUMPRODUCT(1-SUBTOTAL(3,OFFSET($E5:$E630,ROW($E5:$E630)-MIN(ROW($E5:$E630)),,1)),$E5:$E630,$F$5:$F$630)/SUMPRODUCT(1-SUBTOTAL(3,OFFSET($E5:$E630,ROW($E
5:$E630)-MIN(ROW($E5:$E630)),,1)),$E5:$E630)

 

[hgrove]

Shawn wrote...
...

One other thing, I get a #value error when I place the function
on a line. The vr array is correct but the array doesn't evaluate

...

I didn't test the udf enough. Here's a bug fix.



Code
-------------------

Function vr(rng As Range) As Variant
Dim i As Long, n As Long, rv As Variant

Application.Volatile 'practical necessity

n = rng.Rows.Count
ReDim rv(1 To n, 1 To 1)

For i = 1 To n
rv(i, 1) = IIf(rng.Rows(i).Hidden, 0, 1)
Next i

vr = rv
End Function