Counting NONZERO Cells - Filtered and Pivoted
To both array answers (first of all, thank you): Will that work with Pivots?
"Bob Phillips" wrote:
=AVERAGE(IF(rng<0,rng))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"RJB" wrote in message
...
So I've got a huge sheet with sales numbers for top 400 clients over last
four years, broken into segments (i.e., PRODUCE, CANNED GOODS, FROZEN
GOODS,
BAKERY).
There's a TOTAL column that adds the segments for each year
(2006TOTAL=2006PRODUCE+2006CANNED_GOODS+2006FROZEN _GOODS+2006BAKERY).
Not every client has sales in every year. In those years, the "YEARTOTAL"
= 0.
Nonetheless, I want to get an average sale, for customers that have sales.
So:
A 10
B -
C 8
D 3
E -
The average for that year of customers that have sales is 7 - (10+8+3)/3.
If I do "AVERAGE" in a Pivot table, it gives 4.1 - (10+8+3)/5.
I tried filters - less than desirable, but better than nothing - and it
still gives 4.1. Because there's a formula in there, it counts as an entry
in
the calc. COUNT and COUNTA (SUBTOTAL(2,[]) and SUBTOTAL (3,[]),
respectively)
both give me 5 for the list.
I know I can do a COUNTIF, but how can I build that into filters, etc.?
The
list of clients is changing, and I want this to be robust, not
flashfrozen.
Thanks!
|