Adding an IsBlank to an existing formula

AmyD

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

AmyD

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"))))), "")

Genevieve P.

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

Genevieve P.

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

AmyD

@Genevieve P. Thanks, that formula helped with the first column. But adding in the iferror to the status column results in me having "Not Started-Behind" 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.

Genevieve P.

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

AmyD

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"))))), "")

Genevieve P.

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