Excel: Average Without Zero Values
While Excel's AVERAGE function ignores blank cells, it doesn't ignore cells that contain 0 (zero). This can result in inaccurate analysis of the data.
Example, let's say you want to calculate the average score for all students who took the final exam. Using the AVERAGE function, you might enter: =AVERAGE(B3:B23).
But if two students were absent and received a grade of 0, the result of the formula wouldn't give a true picture of the average grade. However, you can calculate the average and exclude the absentee students' grades, and this is how:
- Enter the following formula: =AVERAGE(IF(B3:B23<>0,B3:B23))
- Press [Ctrl][Shift][Enter].
The entered formula first creates an array that includes only nonzero values in the range. The AVERAGE function then uses this array as its argument.
- Printer-friendly version
- Log in to post comments
