Adding an IsBlank to an existing formula
Hello,
I am using the following formula to determine the expected complete % based on dates in the project sheet. I am setting this up as a template so would like the formula to exist in the column even though the rest of the row data is blank, awaiting the user to populate. Once they populate it, it would automatically apply the expected % complete. . . Today I get an error: #Divide By Zero
Expected % complete Formula:
=IF(Start@row <= TODAY(), MIN((TODAY()  Start@row) / Duration@row, 1), 0)
I have tried adding is blank to start of the formula, but then get different errors each time.
The same issue applies for the column next to it, Status is driven through a formula, and i would like to say if % complete is blank or Start date is blank, then status = Blank until there is data.
Status Formula today:
=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")), "Behind", IF(AND(Start@row < TODAY(), ([% Complete]@row = 0)), "Not Started/Behind", "Not Started"))))
Thank you,
AmyD
Best Answers

Great, thanks @Genevieve P. I ended up having to add in one additional " [ " at the start of the formula provided. Final formula that worked:
=IFERROR(IF([Planned Start]@row= "", "", IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")), "Behind", IF(AND([Planned Start]@row < TODAY(), ([% Complete]@row = 0)), "Not Started/Behind", "Not Started"))))), "")

Oops! My apologies! That's what happens when I don't check what I copy/pasted 😂
Good catch! I'm glad the final formula works for you.
Cheers,
Genevieve
Answers

Hi @AmyD
We could write in a statement about blank rows, but I think it would be easier just to add an IFERROR around each formula, like so:
=IFERROR(formula, "")
So in your first one:
=IFERROR(IF(Start@row <= TODAY(), MIN((TODAY()  Start@row) / Duration@row, 1), 0), "")
See: IFERROR Function Let me know if that will work for you!
Cheers,
Genevieve

@Genevieve P. Thanks, that formula helped with the first column. But adding in the iferror to the status column results in me having "Not StartedBehind" on all blank tasks. I suspect this is an issue the main part of the formula, not the iferror section, any suggestions?
Note I have changed column names to include "Planned" Revised formula is as follows:
=IFERROR(IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")), "Behind", IF(AND([Planned Start]@row < TODAY(), ([% Complete]@row = 0)), "Not Started/Behind", "Not Started")))), "")
I would like it if there is no start date that we aren't expecting a status.

Hi @AmyD
Good point! Yes, I believe this is happening because formulas see blank date cells as "in the past", which means that [Planned Start]@row < TODAY() is seen as true.
Let's add this in:
=IFERROR(IF(Planned Start]@row= "", "", IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")), "Behind", IF(AND([Planned Start]@row < TODAY(), ([% Complete]@row = 0)), "Not Started/Behind", "Not Started"))))), "")
It will check to see if the Planned Start is blank, and if it is, return a blank cell.
Cheers!
Genevieve

Great, thanks @Genevieve P. I ended up having to add in one additional " [ " at the start of the formula provided. Final formula that worked:
=IFERROR(IF([Planned Start]@row= "", "", IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")), "Behind", IF(AND([Planned Start]@row < TODAY(), ([% Complete]@row = 0)), "Not Started/Behind", "Not Started"))))), "")

Oops! My apologies! That's what happens when I don't check what I copy/pasted 😂
Good catch! I'm glad the final formula works for you.
Cheers,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!