average formula

[Jonsson]

Hi all,

How to get the real average, when you have cells containing formula
returning zero?

I have tried "AVERAGE" but that don't work when I have formulas in th
cells that I want to get average.

Example:

Without formula in A1:A5
A1 A2 A3 A4 A5
10 10 10= 10 average (right)

With formula in A1:A5
A1 A2 A3 A4 A5
10 10 10= 6 average (wrong)
I want that also to be 10 as average value.

What to be done?

Any idea's?

//Thoma

 

[jammy]

AVERAGE(A1:A5) equals SUM(A1:A5)/COUNT(A1:A5)
i.e. find the sum and divide by the number of entries.

if you don't want any 0 value entries to count, use

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

which will ignore any entries with 0 valu

 

[Don Guillett]

this is an ARRAY formula so must be entered/edited with control+shift+enter
vs just enter.
=AVERAGE(IF(H1:H5>0,H1:H5))

 

[Jonsson]

Hi, and thanks!!

I tried Jammys formula (I dont understand what to do when it's
arrayformula.)

Jammys formula works great until I have no values at all in tha
column. I get ######.

Any ideas not to get #####?

//Thoma

 

[Don Guillett]

You get that when the formula evaluates to VALUE. If you widen the column
you should see that. Testing did not produce that result for me. but that
formula did not work properly with blanks. To use the array formula, after
you type it in, hold down the CONTROL key and hold down the SHIFT key and
touch the ENTER key. It works. Try it!

 

[jammy]

#### usually means the cell isnt wide enough to display its contents s
maybe it is that.

my formula doesnt take into account empty cells however and count
those when it is taking the average.

however this is sorted by using

=SUM(a1:a5)/(COUNTIF(a1:a5,"<>0")-COUNTBLANK(a1:a5))

Don's formula works so you can use either. all you have to do (as h
mentions) with it is press ctrl+alt+enter after you type it to sho
that it is an array formula instead of just a regular one where yo
would just press enter.

both formulas fall over when it comes to completely empty column
however as then they divide by 0.

to sort that out you would need to put the formula of your choice in a
if statement like

=if(iserror(average(...)),0,average(...))

again if using Don's formula you would need to press ctrl+shift+ente
once you type it in

 

[Jonsson]

Hi,

I tried the array formula, but I still get ####(column is not t
small)

So, I do a summary first and then I go:

=IF(M3=0;"";(M3)/COUNTIF(C3:L3;"<>0"))

I get no ####, and I say thanks to Don and Jammy, for leading me to th
right track!!!

SUPERB!!

//Thoma

 

[Don Guillett]

OK. I'm back in the office now. Send me a SMALL file to the address below,
and I will have a look. Be sure you copy\paste these messages and subject
line so I don't have to go back to the ng to look at it. The formula I send
DOES work.