MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to omit Zero scores when Averaging?

Posted by J on March 30, 2001 9:08 AM

Iam attempting a simple average of a row of grade scores.
(ie. 100,90,70,0,80,0,100,70)
The scores sometimes are Zero,
and I want to exclude these Zero's from the average.
The numbers are derived from another place on my spreadsheet
which shows 0 when a test is not given or taken.
A sample of the formula being used now:

Is there a way to average and exclude any "0" entries
from the average calculation?

Posted by Aladin Akyurek on March 30, 2001 9:30 AM

A famous formula for this is the following array-formula:


assuming your grades to be in A1:H1.

You have to hit CONTROL+SHIFT+ENTER at the same time to enter it.


Posted by J on March 30, 2001 9:41 AM

WOW! Thanx Aladin!