Counting NONZERO Cells - Filtered and Pivoted
 

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!

Counting NONZERO Cells - Filtered and Pivoted
 

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

Counting NONZERO Cells - Filtered and Pivoted
 

Think you could try something like this,
array-entered by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF(B1:B1000,B1:B100))
assuming B1:B100 contains the formulas which may return zeros
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RJB" wrote:
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!

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!

Counting NONZERO Cells - Filtered and Pivoted
 

AND, that gives me a #VALUE! error.

"Max" wrote:

Think you could try something like this,
array-entered by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF(B1:B1000,B1:B100))
assuming B1:B100 contains the formulas which may return zeros
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RJB" wrote:
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!

Counting NONZERO Cells - Filtered and Pivoted
 

Try this heavier duty one then, array-entered as befo
=AVERAGE(IF(ISNUMBER(B1:B100),IF(B1:B1000,B1:B100 )))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RJB" wrote:
AND, that gives me a #VALUE! error.

Counting NONZERO Cells - Filtered and Pivoted
 

If you want to make it work for a Pivot Table,
add another column, Client2, with the formula
=IF(Sales0,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