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