Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Averaging in Execl

Averaging in Execl

Averaging in Execl

When using Quattro Pro, I can use a function "PureAverage" to average a series, even if some cells of the series contain unsatisfied formulas. "PureAverage" ignors these cells and averages the remaining cell values.  Does Excel have a similar function??

Steve Wagner

Stiffler, McGraw & Assoc.
Hollidaysburg PA

RE: Averaging in Execl

try the averagea function and not the average function.

good luck!

RE: Averaging in Execl

I tried that, but it includes unsatisfied arguements, assigning a "zero" to them, while Quattro's "PureAverage" excludes such cells from the average.

I think :)



RE: Averaging in Execl

What do you mean by "unsatisfied"?

RE: Averaging in Execl

So long as the "unsatisfied" cells are non-numeric, AVERAGE should ignore them.  AVERAGEA does not.

1    1
3    3
4    1
5    5
6    1
7    7
8    1
9    9
10    1

average    3.222222222
count    9
sum    29


RE: Averaging in Execl

I am asking this question for a co-worker, and I stated the first post as he presented it to me. I looked further into his work and I think the fault is not with the "average" function, but rather that Excel treats a blank cell as having a "ZERO" value. Now, if the value of a cell is determined by a calculation (+a2 * b2), and a cell is left blank (+a2), the calculation is still performed, and in the example, the result is zero. Excel "help" says to enter N/A or #N/A in unfilled cells, but then the average of a series that contains an N/A or a #N/A won't work. These cells are to contain lab analysis data, but some days a lab test may not be run, yet he still wants a monthly average as well as monthly HI's and LOW's.


RE: Averaging in Execl

That shouldn't matter.  

If your friend uses the COUNT function, it ignores the blank cells, so the fact that you added a bunch of zeros is irrelevant.  The only reason it's screwing up is because your friend is explicitly defining the number of items to average.


RE: Averaging in Execl

I suppose COUNT may work if the cells are blank, but if the cell contents is the result of a calculation involving a blank cell, then it's not a blank cell, it's a zero, and gets included.

It seems to come down to the fact that Quattro treats blank cells as blank (and won't complete a calculation) while Excel treats blank cells as zero.

Sometimes the data actually is a zero, so checking for zero won't work.


RE: Averaging in Execl

Instead of =A2*B2, use =IF(OR(ISBLANK(A2),ISBLANK(B2)),"",A2*B2) then AVERAGE() will work on the calculated values as intended with blanks in column A or column B.  AVERAGEA() will count the "" as a zero.

RE: Averaging in Execl

Or you can try this


Where ALL data (including 0's) is in cells A1 to A7

RE: Averaging in Execl

I've never had a problem with the AVERAGE function in Excel.  I just typed in 1, 2, and ="" into three cells, and the AVERAGE function returned 1.5.  I also tried other characters in the third cell, and still got 1.5.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close