averaging formula for blanks and zeros across two cells

[Ben H]

Hi

I have the following formula for averaging cells or just bring back a number from one cell ...

=IF(AND(BK5="",BU5=""),"",IF(AND(BK5<>"",BU5<>""),AVERAGE(BK5,BU5),IF(BK5="",BU5,IF(BU5="",BK5))))

This works if the cells have blanks in them, but i also need it to do similar if any of the cells there is a 0.

Can you help?

Kind Regards

Ben

 

[chirayu]

=IF(OR(AND(BK5="",BU5=""),AND(BK5=0,BU5=0)),"",IF(OR(AND(BK5<>"",BU5<>""),AND(BK5<>0,BU5<>0)),AVERAGE(BK5,BU5),IF(OR(BK5="",BK5=0),BU5,IF(OR(BU5="",BU5=0),BK5))))

 

[Khalid NGO]

Hi Ben,
I think you can use simple formula for average:

=IFERROR(AVERAGE(BK5,BU5),"")

i also need it to do similar if any of the cells there is a 0.

for this, you can use the following:
=IF(AND(BK5>0,BU5>0),AVERAGE(BK5,BU5),0)

use custom format if you want blank for 0:
#,##0.00;-#,##0.00;

Regards,