×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Averaging in Execl

## Averaging in Execl

(OP)
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!
-pmover

### RE: Averaging in Execl

(OP)
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 :)

Thanks!!!

Steve

### 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
2
3    3
4    1
5    5
6    1
7    7
8    1
9    9
10    1

average    3.222222222
count    9
sum    29

TTFN

### RE: Averaging in Execl

(OP)
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.

Steve

### 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.

TTFN

### RE: Averaging in Execl

(OP)
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.

Steve

### 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

=SUM(A1:A7)/COUNTIF(A1:A7,">0")

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!