How do i find the average, but ignore 0 whenever it appears?

hi spcbnd,

AVERAGEIF is your friend.

For example, =AVERAGEIF(B,">0",B)

Regards,

Ian.

Two methods:

1. Use AVERAGEIF, as suggested above by Ian.

2. If the zeroes are being the result of formulas, revise the formulas to return a null string instead of a zero where a zero result is inappropriate to the average calculation.

The second method would be my preference for these reasons:

Some zeroes are genuine gain/loss results where a transaction has been made which has resulted in no profit (and no loss). Zeroes that are the result of actual transactions should be included in the calculation of 'average profit per transaction.'

Other zeroes are the result of empty cells in the transaction list, including lines where neither the cost nor the selling price are known and lines where the cost is known and has been recorded, but no sale has yet occurred. The profit/loss formula for these lines may produce a zero result in the first case, and either a zero result or a negative (loss) value in the second case, depending on how the formula has been constructed.

Using a test for entered values for both cost and sale price cells, and producing a null string result when either is not yet present will leave the profit/loss cell in that row appearing empty, but actually containing a text value (the null string), which will be ignored by AVERAGE

Example:

Numbers in the example were chosen to give a result that can be easily calculated without paper and pencil. There are six completed transactions; the total profit is 600; so the average is 100.

Formulas

E2: =AVERAGE(D)

D2 (and filled down to all cells in column D): =IF(OR(LEN(B)<1,LEN(C)<1),"",C-B)

The two LEN() functions test for an entered value in the cost and sale price columns of the current row. If either is missing (ie. there are zero characters in the cell), IF places a null string in its cell (as in D5, D6 and D7). None of these not-yet-completed transactions are included in the average calculation.

If a transaction has been completed (there are values entered in both the cost and the sale price cells), the profit/loss on the transaction is calculated and placed in the cell. Every completed transaction is included in the average calculation, including the one in row 9, which resulted in a zero profit.

Regards,

Barry