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:
=AVERAGE(F348:M348)

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:

=AVERAGE(IF(A1:H1,A1:H1))

assuming your grades to be in A1:H1.

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

Aladin

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

WOW! Thanx Aladin!