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!

=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" <(E-Mail Removed)> wrote in message
news:B892649A-684A-41DF-A5C6-(E-Mail Removed)...
> 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!

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

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" <(E-Mail Removed)> wrote in message
> news:B892649A-684A-41DF-A5C6-(E-Mail Removed)...
> > 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!
>
>
>

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:B100>0,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!

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

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