If cell is empty or blank, I want to automatically enter a 0.

I'm not sure how to automatically enter a value of zero if a cell is empty. Is this possible?

I tried to enter the formula below (and various iterations of it) but I get errors. The error for below was "unparseable".

=IF([% Complete]1:[% Complete]17, ISBLANK, ("0"))

Anyone know if this is possible?

Thx,

Deb

Find more posts tagged with

Formulas

Comments

Mike Wilday

You'll need to create the formula in each individual cell, here is the formula you would use in the first cell and drag down to the rest of the cells.

Question though, if you have data already in those cells the formula will overwrite the data. Just make sure that is what you want before you commit to it! Take a screenshot or backup your data before you go.

=IF(ISBLANK([% Complete]1, 0, "")

One question, What do you want it to be if the Complete cell isn't blank?

Deb Gordinier

Thanks for the quick response!! The cells already have data in them, so for those that have a value I went them to remain. Those that are empty should default to zero.

In the string you provided, I see that it reads if it's empty in the $ Complete location, then... what are the 1, 0 representing? I tried to play with a replace option too using value of "0"... that didn't work either.

Mike Wilday

The 1 - represents the first row. The 0 is the returned result.

Modify it like this...

=IF(ISBLANK([% Complete]@row), 0)

Paste that into the rows that are currently blank and have no data... leave the other ones alone.

Mike Wilday

The @row means whatever row the formula is on.

Deb Gordinier

=IF(ISBLANK([% Complete]@row), 0)

I took your string and pasted it directly into the cell that didn't have any data. Got a circular error.

Tried to modify it to add the row # after the task name:

=IF(ISBLANK([% Complete]17@row), 0)

I get an unparseable error.

Tried this, just because I didn't know what else to do;

=IF(ISBLANK([% Complete]@row17), 0)

And I get another unparseable error.

Mike Wilday

Are you putting the formula in the % complete column? If that's the case, just enter 0. No need to put in a formula.

Deb Gordinier

I was just wondering if I should do that instead. For some reason, most of my formulas just aren't working. BUT - I'm probably making it more complicated than it needs to be.

THANK YOU for your help

Mike Wilday

You're absolutely welcome! Feel free to post any questions you have about formulas into the community there are several members who are always ready to help!

Some things to note about Smartsheets, Columns are referenced by the Column Title and the row number. If you column end in a number or has a space in it you will want to surround your title with brackets as in the following example which references a column called Column Title and row number of 3...

=IF([Column Title]3 = "Complete", "add this information")

In this instance the title had spaces so I added brackets, it references row 3, and "Add this information is what is placed in the current cell (in a new column) if Column Title, row 3 has the text "Complete".

LizardPanda6

I used the following to make this work. I ended up going a different route with my sheet but thought I would post this.

Also, if it works with your data, you can use a find and replace. Just click Ctrl + f and replace all #N/A with a 0.

=IF(ISNUMBER(cell to check), VALUE IF TRUE, VALUE IF FALSE)

C_Herrell

Isn't there a simpler way to formulate an entire column to read zero if the cells are empty?

Sign in to join the conversation!

Quick Links

Trending discussions

Compare Previous Week
Hello, I am trying to be able to say whether hours went up or down as compared to the previous week but am having trouble referencing the previous week. I have tried to add a helper column but am still getting an error. Any help would be greatly appreciated!
SUMIFS Formula with Date Column
Hello All, I am using this formula " =SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, ">=" + TODAY(), {RCX- Kit Forecast - 2025 Range 2}, "<=" + (TODAY() + 5)) " Range 1 is the number column Range 2 is a date column But the value is coming as zero
Workflow to trigger comms when number exceeded but not in between.
Hi, trying to work out how I can get a workflow to trigger only when certain milestone numbers are met. For example, I'd like an email comm to be sent once the sum of a column reaches 50, 100, 150, 200 etc. The number will be entered via a form, very basic, so for example 4 entries could be sent via 4 form inputs (15, 11,…