How to average for cells with values only (exclude 0 or blank cells) in Excel?
Average function is quite useful for our daily works in Excel. This article will talk about how to calculate the average for cells with values only (excluding blank cells) in Excel easily.
- Average with Ignoring/excluding blank cells with formula
- Average for cells with values only (exclude blank cells) with Kutools for Excel (only 1 step)
Average with Ignoring/excluding blank cells with formula
The Average function will ignore the truly blank cells automatically. However, if you have disabled the option of Show a zero in cells that have zero value in Excel Options dialog box (clicking File > Options > Advanced > Display options for this worksheet), some blank cells may have zero values in Excel. In this case, the Average function will not exclude these blank cells with zero values.
Actually we can exclude the cells with zeros to solve this problem. For example, you want to average the Range A1:D13 with ignoring/excluding blank cells and zeros, you can apply the formula =AVERAGEIF(A1:D13,"<>0") as below screenshot shown:
Microsoft Excel can automatically average selected cells and show calculated results in the Status bar. If we can select only nonblank cells in a range, we can get the average of this range excluding blank cells. Kutools for Excel's Select Nonblank Cells utility can help you get it done at once.
Select the range where you want to average excluding blank cells, and click the Kutools > Select > Select Un blank Cells.
Now only cells with values are selected in the specified range, and you will get the average excluding blank cells in the Status bar as below screenshot shown:
Calculate special averages in Excel
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agoif the entire range has zeros, it still errors out. Would be nice if that defaulted to zero as well. Mathematically not correct but ok for most reasons.
- To post as a guest, your comment is unpublished.· 5 months agothis is omitting an entire field. the "average range"
Looking for value L1 within A:H, averaging the values in H:H that match L1 criteria. Where does the (<>) go huh?
- To post as a guest, your comment is unpublished.· 2 months agoHi Bob,
Your formula makes me puzzle. Do you mean to look for a value in the Range A:H by L1, and then average cells in H:H if they match the found value? If so, you can apply VLOOKUP or MATCH function to look for the value in Range A:H, and then use AVERAGEIF function to get the average of H:H.