average if (different cells) with formulas

[Anon]

Hi there,

I was wondering if there a simple way to average approx. 5 different
cells (not a range) in these different cells there are formulas I
don't want to average them if they are "" or 0.

ex. A1 = 1 B3 = "" C5=4 D9=4 AVERAGE SHOULD BE 3
AVERAGE(A1,B3,C5,D9;<>"") something like this.

IS this possible.

Thanks.
CD

 

[Murray]

Hi there,

I was wondering if there a simple way to average approx. 5 different
cells (not a range) in these different cells there are formulas I
don't want to average them if they are "" or 0.

ex.  A1  = 1     B3 = ""   C5=4       D9=4      AVERAGE SHOULD BE  3
AVERAGE(A1,B3,C5,D9;<>"") something like this.

IS this possible.

Thanks.
CD

Try it using just the average of the 4 cells (ie =average(A1, B3, C5,
D9). I think you will find it gives you the answer 3 anyway. If the
cell is genuinely blank (blank or formula evalutes to "") it will
ignore it. Worked for me when I tested it.

Regards

Murray

 

[FSt1]

hi
=sum(A1+B3+C5+D9)/Count(A1,B3,C5.D9)

Regards
FSt1

 

[FSt1]

yes, i did mean exactily what you pointed out.
thanks for catching that.

Regards
FSt1

I think that instead of
=sum(A1+B3+C5+D9)/Count(A1,B3,C5.D9) you may have meant
=(A1+B3+C5+D9)/Count(A1,B3,C5,D9) or
=sum(A1,B3,C5,D9)/Count(A1,B3,C5,D9)
[you need a comma instead of a full stop between C5 and D9, and if there
isn't anything that you wish to add to A1+B3+C5+D9 you don't need the sum
function.]
but you could equally well have used =AVERAGE(A1,B3,C5,D9)

But note that any of those formulae will ignore the empty string "" or a
genuinely empty cell, but it won't ignore zero.
--
David Biddulph

hi
=sum(A1+B3+C5+D9)/Count(A1,B3,C5.D9)

Regards
FSt1

 

[Anon]

yes, i did mean exactily what you pointed out.
thanks for catching that.

Regards
FSt1

I think that instead of
 =sum(A1+B3+C5+D9)/Count(A1,B3,C5.D9) you may have meant
 =(A1+B3+C5+D9)/Count(A1,B3,C5,D9) or
 =sum(A1,B3,C5,D9)/Count(A1,B3,C5,D9)
[you need a comma instead of a full stop between C5 and D9, and if there
isn't anything that you wish to add to A1+B3+C5+D9 you don't need the sum
function.]
but you could equally well have used =AVERAGE(A1,B3,C5,D9)

But note that any of those formulae will ignore the empty string "" or a
genuinely empty cell, but it won't ignore zero.

- Show quoted text -

Thank you to all you are soo helpful - thanks a bunch.

CD

 

[Anon]

yes, i did mean exactily what you pointed out.
thanks for catching that.

Regards
FSt1

I think that instead of
 =sum(A1+B3+C5+D9)/Count(A1,B3,C5.D9) you may have meant
 =(A1+B3+C5+D9)/Count(A1,B3,C5,D9) or
 =sum(A1,B3,C5,D9)/Count(A1,B3,C5,D9)
[you need a comma instead of a full stop between C5 and D9, and if there
isn't anything that you wish to add to A1+B3+C5+D9 you don't need thesum
function.]
but you could equally well have used =AVERAGE(A1,B3,C5,D9)
But note that any of those formulae will ignore the empty string "" or a
genuinely empty cell, but it won't ignore zero.
--
David Biddulph
hi
=sum(A1+B3+C5+D9)/Count(A1,B3,C5.D9)
Regards
FSt1
:
Hi there,
I was wondering if there a simple way to average approx. 5 different
cells (not a range) in these different cells there are formulas I
don't want to average them if they are "" or 0.
ex.  A1  = 1     B3 = ""   C5=4       D9=4     AVERAGE SHOULD BE  3
AVERAGE(A1,B3,C5,D9;<>"") something like this.
IS this possible.
Thanks.
CD- Hide quoted text -

- Show quoted text -

Thank you to all you are soo helpful - thanks a bunch.

CD- Hide quoted text -

- Show quoted text -

Hi there me again. I tried it but the problem is I can just average
the 4 cells I need to include the 5th one also because I will be
copying formula down and I don't know which cell will be empty .

thanks