conditional formula blank, value or average in cell

[Ben H]

Hi

i need an excel formula but cant figure out how to make it work.

=average(cell1,cell2) unless either cell is blank then just bring back the value in the cell which has a value, and if both cells are blank then I need a formula that will just make the cell blank.

Please can you help.

Thanks

Ben

 

[Asheesh]

Hi,

use the below

IFERROR(AVERAGE(cell1,cell2),"")

 

[NARAYANK991]

Hi Ben ,

The AVERAGE function ignores true blanks.

Thus , just using =IFERROR(AVERAGE(A1,B1),"") will return the results that you want , provided either or both of the cells are truly blank.

Narayan

 

[Ben H]

Hi

Thanks for this but while I want the formula to average, if either of the two cells is blank I just want the formula to pull back the cell with the data in it and not average that cell against the blank when it does.

Thanks

Ben

 

[NARAYANK991]

Hi Ben ,

That is what the AVERAGE function will do , if the blank cell is truly blank.

If the cell which is blank is merely displaying a blank cell as a result of a formula , then the AVERAGEIF function will need to be used. Can you clarify what exactly the two cells will contain ?

Narayan