Calculate Count of Days & Average WHERE Amount <0
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(F20,G2/F2,"")
--
Biff
Microsoft Excel MVP
"amg0657" wrote in message
...
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.
|