Solved

Coorect way to average

Posted on 2014-07-21
9
72 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
 
LVL 90

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 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