Counting NONZERO Cells - Filtered and Pivoted

[RJB]

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!

 

[Bob Phillips]

=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)

 

[Max]

Think you could try something like this,
array-entered by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF(B1:B100>0,B1:B100))
assuming B1:B100 contains the formulas which may return zeros

 

[RJB]

To both array answers (first of all, thank you): Will that work with Pivots?

=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)

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!

 

[RJB]

AND, that gives me a #VALUE! error.

Think you could try something like this,
array-entered by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF(B1:B100>0,B1:B100))
assuming B1:B100 contains the formulas which may return zeros

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!

 

[Max]

Try this heavier duty one then, array-entered as before:
=AVERAGE(IF(ISNUMBER(B1:B100),IF(B1:B100>0,B1:B100)))

 

[Herbert Seidenberg]

If you want to make it work for a Pivot Table,
add another column, Client2, with the formula
=IF(Sales>0,Client,"")
so your primary data looks like this:
Client Client2 Sales
B B 3
A 0
A A 6
B B 3
C C 9
D D 3
D D 9
A A 8
D 0
B B 9
Now use Client2 and Sales for the PT
The result might look like this:

Average of Sales
Client2 Total
A 7
B 5
D 6
C 9