Blank cell only recognized as text

My spreadsheet has started recognizing blank cells as text and not zeroes.

Every row in the RequirementNumber column is filled in with this formula

=IF(ISBLANK([Requirement]2), "", [RequirementNumber]1 + 1)

The formula used to pick up "" cells in the row above it as a numeric zeros, now it is picking it up as blank text and making all of the increments in text not numeric values.

my numbers now look like this:

blank

111

1111

Instead of:

blank

I can manually fix this by deleting the formula in the blank cell and replacing it with a numeric zero. The formulas adjust below and the numbering corrects itself. If I delete the zero so the cell is blank again the numbering stays accurate.

Any ideas?

Find more posts tagged with

Comments

J. Craig Williams

Brett,

Did this just start happening, say since the last push?

The next release comes out on Saturday, I believe, but somethings things leak out before then.

If you hadn't said it worked before, I would have said it wouldn't.

I have seen some odd behavior with null and blanks -- can't say they have only been recently though. It is almost as if a blank cell that had data before (but not now) operates differently than a blank cell that has never had data.

I think there are some programming methods where that might be case.

Wouldn't it be nice to search, find & replace in formulas?

Craig

Brett Evans

I just noticed it this week. It had worked beautifully for month to auto-number my rows.

I could fix it by changing my formula to

=IF(ISBLANK([Requirement]2), 0, [RequirementNumber]1 + 1)

but it would mess with my steez.

J. Craig Williams

Well, I learned what "steez" is.

We have to wait for Shaine to pipe in. Maybe they will admit to something changing.

Craig

Andrew DeCounter

Brett,

With formulas "" is interpreted as text so we are seeing ""+1+1+1 return 111 instead of 3.

I don't have a clean solution for getting a Null return on the ISBLANK() evaluation when it returns "True". You could do =IF(ISBLANK(Requirement2),Requirement1,(RequirementNumber1)+1) -- but I don't think asking a cell to always be blank is a great solution.

There are a couple of work arounds you could try here to achieve the same goal. For example: ISTEXT() & LEN().

I have created some examples with those formulas that could work in this case...

https://app.smartsheet.com/b/publish?EQBCT=517f9519c74842b7b64458bab8986f5a