Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Averaging in Execl

Status
Not open for further replies.

SteveWag

Civil/Environmental
Dec 11, 2003
348
US
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
 
Replies continue below

Recommended for you

try the averagea function and not the average function.

good luck!
-pmover
 
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
 
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
 
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
 
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
 
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
 
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.
 
Or you can try this

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

Where ALL data (including 0's) is in cells A1 to A7
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Top