Drop zeros from averages
Drop zeros from averages
Q I use an Excel worksheet to track time billed by consultants, and then I use the Average function to calculate the average bill. However, I find that this calculation includes zero values. I need to compute the average bill but exclude those consultants who didn't work any hours. Is it possible to calculate the average of a range and ignore cells that contain a zero? - Bryan Davis A A simplified version of Bryan's worksheet is shown. As you can see, the calculated average (Cell D14) does indeed include values of zero. In this case, Excel's Average function returns 601 instead of the desired value of 751.25.  Ignore zeros when finding an average -- convert them to blanks, or use the Countif function There are at least two ways to calculate the average of only the non-zero cells. First, you can modify the formulas in range D2:D11 so they return a blank or a text string instead of a zero, then calculate an average -- the Average function ignores blank cells and text. For example, the following formula (in Cell D2) returns a blank if B2*C2 returns zero: =if(B2*C2=0,"",B2*C2) If you'd rather display the zeros in Column D, try the second approach, which uses the Countif function to return the number of cells that don't contain a zero. The following formula, entered in Cell D14, computes the sum of the values in Column D divided by the number of non-zero cells. =sum(D2:D11)/countif(D2:D11,"<>0") - John Walkenbach | Category: Spreadsheet Issue: Mar 1998 Pages: 164-166 |
These Web pages are produced by Australian PC World © 1997 IDG Communications