Calculate Count of Days & Average WHERE Amount <>0

[amg0657]

Using: Excel 2003 SP3

Question: How do I calculate and display the total COUNT (of week days) and
AVERAGE (amounts) where total count and average are calculated excluding
Amounts = 0. For example, in the data set below, notice how the (1) Raw Data
has zero amounts for the last W, Th and F. The averages for W, Th and F in
the (2) Summary including Amt = 0 are based upon the fact that they are
counted in the raw data. The end result I'm looking for is (3) Result
Excluding Amt = 0. Notice in (3) that the count and average are based upon
the fact that Amounts = 0 were excluding from the count and average.

(1) Raw Data
Day Amt
M $20.00
Tu $100.00
W $200.00
Th $25.00
F $65.00
M $10.00
Tu $20.00
W $-
Th $-
F $-

Total 10 $440.00

(2) Summary Including Amt = 0

CountAll Amt Avg
M 2 $30.00 $15.00
Tu 2 $120.00 $60.00
W 2 $200.00 $100.00
Th 2 $25.00 $12.50
F 2 $65.00 $32.50

Total 10 $440.00 $44.00

(3) Result Excluding Amt = 0

Count Amt Avg
M 2 $30.00 $15.00
Tu 2 $120.00 $60.00
W 1 $200.00 $200.00
Th 1 $25.00 $25.00
F 1 $65.00 $65.00

Total 7 $440.00 $62.86


Please advise. Thanks.

 

[T. Valko]

Try these:

A2:B11 = raw data

E2:E6 = M, Tu, W, Th, F

Enter this formula in F2 and copy down to F6:

=SUMPRODUCT(--(A$2:A$11=E2),--(ISNUMBER(B$2:B$11)))

Enter this formula in G2 and copy down to G6:

=SUMIF(A$2:A$11,E2,B$2:B$11)

Enter this formula in H2 and copy down to H6:

=IF(F2>0,G2/F2,"")