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
Comments
-
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?
-
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.
-
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.
-
The @row means whatever row the formula is on.
-
=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.
-
Are you putting the formula in the % complete column? If that's the case, just enter 0. No need to put in a formula.
-
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
-
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".
-
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)
-
Isn't there a simpler way to formulate an entire column to read zero if the cells are empty?
Help Article Resources
Categories
Check out the Formula Handbook template!
