Solved

Coorect way to average

Posted on 2014-07-21
9
75 Views
Last Modified: 2014-07-21
Folks,
In A1:A10 there are 8 values (2,4,6,8,10,12,14,16) and 2 cells with zero in each.
The average should be 9. The average I'm getting is 7.2. How does one average with zero value(s) that you do no want to be included in the average
0
Comment
Question by:Frank Freese
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 12

Expert Comment

by:duttcom
ID: 40210391
Try this -

=AVERAGEIF(A1:A10,">0")
0
 
LVL 28

Accepted Solution

by:
chilternPC earned 500 total points
ID: 40210393
you can use

=AVERAGEIF( A1:A10 , "<>0")
0
 
LVL 28

Expert Comment

by:chilternPC
ID: 40210394
duttcom you beat me by 1.20 :-(  but mine allows for not zero i.e. minus ;-)
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 92

Expert Comment

by:John Hurst
ID: 40210396
If the cells are mathematically and legitimately zero then the average is 7.2. That is just standard mathematics.

If the cells are not relevant and just there then the formula provided above is what you need.
0
 
LVL 12

Expert Comment

by:duttcom
ID: 40210397
Ah- but you used "<>0" which is more correct if there are going to be negative values!
0
 

Author Comment

by:Frank Freese
ID: 40210422
John,
You'd be correct if zero was part of the data stream. In this instance zero simply means that there was nothing in a particular month which would not would not be out of the ordinary.
I'd forgot that a negative value needs to be included. Although duttcome had a great solution, I'm going to go with chilternPC on this one.
0
 

Author Closing Comment

by:Frank Freese
ID: 40210424
thanks to all
0
 
LVL 12

Expert Comment

by:duttcom
ID: 40210428
No assist points? Oh well...
0
 

Author Comment

by:Frank Freese
ID: 40210437
Assist point? Did not know we could give assist points. I do now - thanks for letting me know.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I get SLA using Excel Raw Data 12 32
Excel VBA User Form Help 21 27
Excel format formula for currency 15 22
Excel Drop Down List 13 29
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now