Another Math calculation

[JB]

=(A1+B1+C1+D1)/4

That is an average. So far, so good. But what happens if I do not have one
of the values and want the average of the ones that I have?

Something like

= { if A1 A1+ if B1 B1 + if C1 C1 + if D1 D1}/ ???? divided by what?

JB

 

[Bernd P]

AVERAGE(A11)

Regards,
Bernd

 

[Gord Dibben]

=AVERAGE(A1:A4)

Empty cells will be ignored and Excel averages just the numbers that are
present.


Gord Dibben MS Excel MVP

 

[JB]

Great. Thanks

But, now another problem I have two tables. The second converts readings
from the first x/18. However, some value in the first table are
missing(empty cells), and the second table assumes that it must divide 0/18
and inserts '0' where it should be empty. How can I get the second table to
ignore epty cells in the first and so not convert?

Thank you

JB

 

[Gord Dibben]

Generally to prevent zeros from interfering you can trap.

=IF(A1/B1=0,"",A1/B1)

Or with your example data =IF(0/18=0,"",0/18)

AVERAGE function ignores the "" that is returned if result is zero.


Gord

 

[JB]

Thanks!

JB

Generally to prevent zeros from interfering you can trap.

=IF(A1/B1=0,"",A1/B1)

Or with your example data =IF(0/18=0,"",0/18)

AVERAGE function ignores the "" that is returned if result is zero.


Gord