Need to work out an average

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

I need to work out an average of some cells that are not next to each
other....the cells may have errors, be empty, or contain "0"s. These cells
should not be counted when working out the average.....

Any Ideas?
 
OK, this is in german, but i know the excel translations.....

{=MITTELWERT(WENN(ISTZAHL(N11,Q11,T11,W11)))}

When I close the function the cursor highlights Q11 in the function, and the
cell that the function is in has #NAME?
 
Not knowing German but it looks like you should have something like:
MITTELWERT(WENN(ISTZAHL(N11:W11),N11:W11))
Isnumber is looking for a range in this example,Average will ignore
anything that is not a number in the range.
 
Hi,

Your translations are correct......but the cells I need the average of arent
next to each other, which is why I didnt have N11:W11. It seems like XL
doesnt like individual cells tho in this sort of function.....
 
Lol, no i didnt do that :o)


Chris Leonard said:
did you enter the function by just pressing return ?

I bet you did.

You need to type the function into the cell and press Control-Shift-Enter
all at once as the function is an array function.

If you did do this ......... sorry, ignore me!

Chris
Click to expand...
 
Adam,

As slight adjustment to Thomas' formula eliminates zero also:

=AVERAGE(IF(ISNUMBER(A1:A29),IF(A1:A29<>0,A1:A29)))

Ctrl -Shift-Enter

Don Pistulka
 
Hi Don,

You could eliminate the the ISNUMBER test as =AVERAGE()
ignores text and blank cells:

=AVERAGE(IF(A1:A29,A1:A29)) Ctrl+Shift+Enter

In this formula the explicit IF returns FALSE for 0 values
and does not include them in the calculation.

Biff
 
But it doesn't ignore cells with errors..
Hi Don,

You could eliminate the the ISNUMBER test as =AVERAGE()
ignores text and blank cells:

=AVERAGE(IF(A1:A29,A1:A29)) Ctrl+Shift+Enter

In this formula the explicit IF returns FALSE for 0 values
and does not include them in the calculation.

Biff
Click to expand...
 
Hi Thomas,

If there are #ERRORS, fix them! Or:

=AVERAGE(IF(NOT(ISERR(A1:A29)),A1:A29)) Ctrl+Shift+Enter

Biff
 
Read the OP,wasn't my question,However I believe that still counts the
error cells and 0's. average 0,100,#DIV/0!,#NAME? average 33.3333?
Average 5 cell range with 100 in 1 cell and 0 in the rest =20?
 
Thomas said:
Read the OP,wasn't my question,However I believe that still counts the
error cells and 0's. average 0,100,#DIV/0!,#NAME? average 33.3333?
Average 5 cell range with 100 in 1 cell and 0 in the rest =20?

Biff wrote: ....
Click to expand...
....

ISERR catches error values excluding #N/A while ISERROR catches error values
including #N/A. I'd think the OP would prefer ISERROR (once translated to
German). To exclude error values and zeros, try the array formula

=AVERAGE(IF(ISNUMBER(1/A1:A29),A1:A29))
 
Hi all,

I took Thomas advice, and got the errors fixed by replacing them with
0s....that way I can get my average, skipping the 0s at the same time.....

Thanks for all your help :o)
 
Hmmm, Yes your correct. I haven't been able to modify
that formula yet, but in the end it would be very 'ugly'.
With all these conditions, I think Don's solution is best
after all. I would ask the OP why are there #ERROR's in
the first place? And why not fix them? Would make things
much easier. Who wants to open a workbook and see #REF,
#N/A, and the like all over the place?

Biff
 
-----Original Message-----

....

ISERR catches error values excluding #N/A while ISERROR catches error values
including #N/A. I'd think the OP would prefer ISERROR (once translated to
German). To exclude error values and zeros, try the array formula

=AVERAGE(IF(ISNUMBER(1/A1:A29),A1:A29))


.
Click to expand...
There ya go, nice and tidy!

Biff
 
I need to work out an average of some cells that are not next to each
other....the cells may have errors, be empty, or contain "0"s. These cells
should not be counted when working out the average.....

Any Ideas?
Click to expand...

If there isn't a better option built in Excel today, along time ago I
started doing something like below, it could be modified as needed.

Public Function ZAverage(zRange)
Dim ztRange As Range, zCell As Range, zSum As Single
Dim zCount As Integer
Set ztRange = zRange
For Each zCell In ztRange
If IsNumeric(zCell.Value) Then
If zCell.Value <> 0 Then
zSum = zSum + zCell.Value
zCount = zCount + 1
End If
End If
Next
If zCount > 0 Then
ZAverage = zSum / zCount
Else
ZAverage = 0
End If
End Function

DJ
 
Back
Top