• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to average non zero values in excel 2000

M

mccauvery

New Member
Hello,


My name is Cauvery and I was trying to average all the non zero values. The data has values like say(1, 2, 0, 5, 6,0,7,0,9,0). Just need to take the average of the non zero values. How do I do it. The version i am using is Excel 2000.


Please advise.


Regards

Cauvery
 
Cauvery


Firstly, Welcome to the Chandoo.org forums.


In Excel 97-2003 the Averageifs() function didn't exist


Assuming your data was in A1:J1

You can however use an array formula like:

Code: 
=AVERAGE(IF(A1:J1>0,A1:J1,FALSE))
Ctrl+Shift+Enter
 
Hi, mccauvery!


I don't remember if Excel 2000 had the COUNTIF function, but I assume id did. So if I'm not wrong here's an approach with normal -not array- formulas:

=SUMA(A1:J1)/CONTAR.SI(A1:J1;">0") -----> in english: =SUM(A1:J1)/COUNTIF(A1:J1,">0")


Regards!
 
You must log in or register to reply here.
Back
Top