Forum Discussion

RickGatorB's avatar
RickGatorB
Copper Contributor
Jan 24, 2019

Blank Not equal to ""

In  a formula for one cell it tests to see if value ="". If so it set a different cell to "". Now in a separate formula that is applying a mathematics formula based on several cell values. For the ones where the value has never been set i.e. blank the calculation returns 0 as the cell value as the expression is evaluated. But for the cell where the value was set to "", the value returned as part of the expression evaluation is #VALUE!

 

Why is that?

 

Trying to work around if a cell was blank instead of setting to "", I tried setting the new cell's value G7 to that of the blank cell. That set the new cell (G7) to 0 not blank e.g. =IF(ISBLANK(F7),F7,C7-F7) where F7 is blank.

 

My goal is to do further math using G7, but I want the blank to be treated like 0, but not show as 0. I am trying not to have to create messy formula that has to test every cell in the formula for blank.

 

Any help will be appreciated.

 

Thanks

excel
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Jan 25, 2019
    Could you change the original formula to show 0 and then just format it as a blank?

    Alternatively wrap your reference to the cell containing "" in N( ) e.g, N( A2 ) will convert "" in A2 to 0

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jan 25, 2019
    Could you change the original formula to show 0 and then just format it as a blank?

    Alternatively wrap your reference to the cell containing "" in N( ) e.g, N( A2 ) will convert "" in A2 to 0

Resources