ISBLANK Function

  • Thread starter Thread starter angy345
  • Start date Start date
A

angy345

Hi, i want the function to evalute the range B3:13 as empty or not, if
it is blank to display 0 otherwise to compute the Average of the
specified range. Below is the function:

=IF(ISBLANK(B3:I3),"0",AVERAGE(B3:I3))

when the range is not empty, the Average is calculated but when it is
empty i get error #DIV0!

Anyone can help.
Many thanks
 
angy345 > said:
Hi, i want the function to evalute the range B3:13 as empty or not, if
it is blank to display 0 otherwise to compute the Average of the
specified range. Below is the function:

=IF(ISBLANK(B3:I3),"0",AVERAGE(B3:I3))

when the range is not empty, the Average is calculated but when it is
empty i get error #DIV0!

Anyone can help.
Many thanks
Click to expand...

ISBLANK(B3:I3) will return an array of 8 values, not a single TRUE/FALSE as
required for IF. You need to AND these together to get a single result:
=IF(AND(ISBLANK(B3:I3)),"0",AVERAGE(B3:I3))
But note that this makes it an array-formula, so it has to be entered using
Ctrl+Shift+Enter rather than just Enter.

Note also that putting the quotes in "0" will mean it returns a text
character rather than the value zero. Why not use this (also array-entered)?
=IF(AND(ISBLANK(B3:I3)),0,AVERAGE(B3:I3))
 
Back
Top