Ifblank

[dunners]

Hi,

A colleague of mine is trying to get a formula working in excel. It reads in various cells. Occassionally, one of these cells is a blank and this throws a '####' error.
We tried using =IF(ISBLANK(G9),0,G9 which didn't work then we tried =IF(OR(AND(G1>-100000,G1<0), AND(G1>0,G1<100000)),G1,0) which then seemed to return a value of 0 whether there was a value in G1 or not.
Any ideas?

 

[lcaretto]

Hi,

A colleague of mine is trying to get a formula working in excel. It reads in various cells. Occassionally, one of these cells is a blank and this throws a '####' error.
We tried using =IF(ISBLANK(G9),0,G9 which didn't work then we tried =IF(OR(AND(G1>-100000,G1<0), AND(G1>0,G1<100000)),G1,0) which then seemed to return a value of 0 whether there was a value in G1 or not.
Any ideas?

The ISBLANK function is only true if the cell has no entries. If the cell has a formula that returns a blank, the ISBLANK function returns FALSE. One workaround is to test for a blank string. Rewrite your first test above as =IF(G9="",0,G9).

WARNING: This may create a zero data point for a cell in which the original worksheet was designed to simply have a blank. You may want some other true alternative (like "") in the if test above.