average

  • Thread starter Thread starter frank
  • Start date Start date
F

frank

Need help to determine in cell N21 the average (arithmetic
mean) of N4:N20 column of cells containing calculated
values. Values may be = or > zero. Desire the average of
the non-zero values only. Thanks.
 
Hi Frank!

One way:

=SUM(N4:N20)/COUNTIF(N4:N20,"<>0")
Averages all non-zero numbers

Another:

=AVERAGE(IF(N4:N20,N4:N20))
Entered by holding down Ctrl + Shift and then pressing Enter
Appears in the cell as:
{=AVERAGE(IF(N4:N20,N4:N20))}
Averages all non-zero numbers

And Another:
=AVERAGE(IF(A12:A15>0,A12:A15))
Entered by holding down Ctrl + Shift and then pressing Enter
Appears in the cell as:
{=AVERAGE(IF(A12:A15>0,A12:A15))}
Averages all numbers greater than zero.

Make sure that you have good reason for excluding the zero's
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top