Filters and totals

[Dallman Ross]

I have a sheet with data that includes totals in the bottom row.
I would like to be able to invoke automatic filtering such that
the totals change dynamically to cover only the visible rows.
I can't figure out any reasonably easy ways to do this. Folks?

A second question: how can I create filters that include my Totals
row without having to go in manually each time and change something?
E.g., I go to Custom in the filter menu and select the main
filter selection, then add "or Does not contain ?", and that
plops my Totals row in, since it doesn't have any value supplied
in the column I'm filtering, but all other rows do have a value
in that column. But that's too much work.

Dallman

 

[Bill Kuunders]

Have a look at the subtotal function

=subtotal(9,A2:A100)

The 9 is to add
There are other numbers to get average , min, max etc.

Greetings from NZ

 

[Dallman Ross]

Have a look at the subtotal function

=subtotal(9,A2:A100)

Thank you. That works just as I'd hoped for the columns where
I need a defined function such as sum, average, etc. Cool beans.

However, how can I do a SUMPRODUCT on just the rows that are
showing in my filter? E.g., a weighted average using data
from two columns.

-----------------

 

[Bill Kuunders]

You could multiply the two columns and take an average of the result using
the subtotal function..?

Bill K

 

[Dallman Ross]

You could multiply the two columns and take an average of the
result using the subtotal function..?

Okay, but I don't really want extra data sitting there that
I don't want to be looking at (the multiplication totals).
I do lots of sorting and stuff on these columns with macros,
too, and extra or hidden rows will mess me up.

I'd have to have a spare column for this that I don't have
right now. Yes, I could hide it, but it's an ugly kludge
and causes me to have to revise all sorts of macros, etc.
I suppose I could put it in a hidden worksheet, but even
that is not my idea of a clean solution.

Any other ideas? Or any refinements that will let me keep
the multiplication subtotals without having to eyeball them
constantly?


----------------

 

[Roger Govier]

Hi

Could you not carry out the Sumproduct based upon the two columns of
data and the same criteria used for your Filter, then divide that answer
by the result of Subtotal(9,data)

With Names in Column J, Count in Column K and Value in column L,
if the Filter was applied for Name "A" in column J then
=SUMPRODUCT((J3:J100)*(K3:K100)*(I3:I100="A"))/SUBTOTAL(9,J3:J100)
will return the result you are seeking.

 

[Dallman Ross]

Could you not carry out the Sumproduct based upon the two columns
of data and the same criteria used for your Filter, then divide
that answer by the result of Subtotal(9,data)

With Names in Column J, Count in Column K and Value in column L,
if the Filter was applied for Name "A" in column J then
=SUMPRODUCT((J3:J100)*(K3:K100)*(I3:I100="A"))/SUBTOTAL(9,J3:J100)
will return the result you are seeking.

That would work, yes, and I thought of that first thing. But I
don't see how to have the formula know what "A" is. I want
to change the filtered data on the fly and still have the weighted
average come out right.

If I have data like so -- I'm using Column "A" for filtering, btw --

A B C D ...
...
22 EEK
23 FOO
24 FOO
25 FOO
26 BAR
27 BAR
28 BAZ
...
101 Totals ...

I can filter for FOO. How does the formula know that I3:I100="FOO"?
I can't use OFFSET from 101, because I don't know how far away in
rows the last FOO is. Also, I might filter on more than one name
at a time.

Dallman

 

[Roger Govier]

Hi Dallman

Then try the following
=SUMPRODUCT((J4:J100)*(K4:K100)
*(SUBTOTAL(3,OFFSET($J$4,ROW($J$4:$J$100)-ROW($J$4),,1))))
/SUBTOTAL(9,J4:J100)

This is an adaptation from a posting by the Subtotal "maestro" (Bob
Phillips) made in Dec 2005
http://snipurl.com/x4qw

 

[Roger Govier]

Hi

from a posting by the Subtotal "maestro"

meant to say, the SUMPRODUCT "maestro"

 

[Dallman Ross]

Then try the following
=SUMPRODUCT((J4:J100)*(K4:K100)
*(SUBTOTAL(3,OFFSET($J$4,ROW($J$4:$J$100)-ROW($J$4),,1))))
/SUBTOTAL(9,J4:J100)

This is an adaptation from a posting by the Subtotal "maestro" (Bob
Phillips) made in Dec 2005
http://snipurl.com/x4qw

Roger,

Interesting, and a head-scratcher. Despite trying at length, I can't
get it to work. :-( But also, I don't see how it could, because there
seems to be a column missing.

Here is some actual sample data. Note that Column A contains the
string I'm filtering on; Column N contains a whole number comprising
days held; and Column G contains the dollar cost. I want to average
the days held as weighted by the cost. The data starts in Row 2.
I've left out some columns that are not relevant here (but kept
others for an overall picture). Numbers at far left are actual row
numbers for the filtered information in this example.

A G K L N O P
------------ ---------- -------------------- -----------------------
Days Running Nominal
1 Descrip. Cost Proceeds G/L Held Retn Retn
------------ ---------- -------------------- -----------------------
473 SOHU.COM INC 5,006.00 5,347.83 341.83 4 6.83% 6.83%
474 SOHU.COM INC 5,006.00 5,047.84 41.84 28 3.83% 0.84%
475 SOHU.COM INC 482.80 504.78 21.98 7 3.87% 4.55%
476 SOHU.COM INC 4,345.20 4,712.25 367.05 11 5.21% 8.45%
477 SOHU.COM INC 241.40 261.79 20.39 11 5.26% 8.45%
478 SOHU.COM INC 465.20 523.59 58.39 10 5.48% 12.55%
479 SOHU.COM INC 4,652.00 4,525.86 (126.14) 65 3.59% -2.71%
480 SOHU.COM INC 965.60 905.17 (60.43) 41 3.14% -6.26%
------------ ---------- -------------------- -----------------------
620 Totals 21,164.20 21,829.11 664.91 59.55 3.14%


The wrong value "59.55" in the Totals row for Days Held is actually the
value for all my data. The value that ought to appear here as a
"subtotal" is 26.49 days.

 

[Dallman Ross]

Roger,

I figured it out. I saw a message here by Ron Rosenfeld,
Message-ID: <[email protected]>,
suggesting:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Well, I did. The formula that works for me now is this:
=SUMPRODUCT(ARRAY.FILTER(N1:N619),ARRAY.FILTER(G1:G619))/SUBTOTAL(9,G1:G619)


Woo-hoo! In any case, I found your help refreshing and worth
cogitating over. So thank you.

Dallman

 

[Roger Govier]

Hi Dallman

Glad you managed to get a result using Laurent Longre's very powerful
addin.

But, just out of interest, I also get a result of 26.49 with your data
if I use

=SUMPRODUCT((N2:N619)*(G2:G619)
*(SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1))))
/SUBTOTAL(9,G2:G619)

I don't see how it could .. (work) ..because there seems to be a column
missing.

The formula is using the 2 columns G and N just like your solution.

The part
SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1)))
is returning an array of 0's for the filtered rows, and 1's for the
visible rows which therefore only gives the product of G and N for the
visible rows.

Maybe you used
SUBTOTAL(9,N2:N619)
as your divisor, which would have given you the weighted Cost of 3167.49
rather than the weighted days.

 

[Dallman Ross]

I also get a result of 26.49 with your data
if I use

=SUMPRODUCT((N2:N619)*(G2:G619)
*(SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1))))
/SUBTOTAL(9,G2:G619)

The part
SUBTOTAL(3,OFFSET($G$2,ROW($G$2:$G$619)-ROW($G$2),,1)))
is returning an array of 0's for the filtered rows, and 1's for the
visible rows which therefore only gives the product of G and N for the
visible rows.

Maybe you used
SUBTOTAL(9,N2:N619)
as your divisor, which would have given you the weighted Cost of 3167.49
rather than the weighted days.

Bingo! Good intuition. All I can say it, it was 2:30 in the morning.

I'm still having trouble seeing what the OFFSET stuff is about in the part
you further elucidated. Maybe you can help me there, too. I do see that
the "width" value (last '1' in the OFFSET statement) is not required in
my case, as the default is the same as the width of the reference, also 1.

I'm also wondering if I should put $ anchors on the array delimiters that
don't have them. It seems odd that some do and some don't.

Thanks again!

Dallman

 

[Roger Govier]

Hi Dallma

As you say the width element isn't necessary in this particular case, so
the final 1 isn't strictly necessary.
What the Offset returns is an array of numbers that occurs in a single
column between G2 and G619.
If you try in on a much smaller subset, and highlight that part of the
formula in the formula bar and press F9, you will see the array.
Wrapping that in Subtotal(3,array) carries out a CountA function on the
whole array, but as Subtotal only deals with visible rows, it will give
a positive count for any visible row that has a value (1) and a 0 for
any row that is either hidden, or has no value.

So you have for example 5006*4*1 for row 473, whereas you could have had
5006 *4 * 0 fro row 472 (as it is hidden - I don't know what the actual
values were, but it is not important, as the final 0 makes the value
zero anyway.)

As far as absolute and relative references are concerned, as the formula
is not being dragged across to any other cells, then it makes no
difference in this particular case. I guess I did it out of habit in
part of the formula, but was lazy in the rest.
Normally I would have made them all absolute.

 

[Dallman Ross]

What the Offset returns is an array of numbers that occurs in
a single column between G2 and G619. If you try in on a much
smaller subset, and highlight that part of the formula in the
formula bar and press F9, you will see the array. Wrapping that
in Subtotal(3,array) carries out a CountA function on the whole
array, but as Subtotal only deals with visible rows, it will give
a positive count for any visible row that has a value (1) and a 0
for any row that is either hidden, or has no value.

So you have for example 5006*4*1 for row 473, whereas you could
have had 5006 *4 * 0 fro row 472 (as it is hidden - I don't know
what the actual values were, but it is not important, as the
final 0 makes the value zero anyway.)

Yes, thank you, Roger! I understand the basic principle. I
was (and still am) having some trouble visualizing the functionality
of the OFFSET function itself in the larger formula. I do use
OFFSET frequently in my own formulas, so I know in general how
it works; but this advanced use of it was beyond me. When I
tried manually calculating the values in individual cells, it
seemed always to be zero. (Maybe I did something wrong.) When
I tried removing the OFFSET and just letting the SUBTOTAL(3,...)
work alone, it didn't work. So I'm a bit confused. Oh, well,
it's a slick, if very complex, approach to a problem, and I am
very grateful to have learned of it.

I have a couple of other questions that I will post seperately
when I am able to work through them. Thanks again!

Dallman