Combining Formulas

milbournr

Hi! I need help with trying to combine 2 formulas into the same column.

In the highlighted column below, I am looking to do the following:

1. =IFERROR(IF(AND([Start Date]$16 > [Target End Date]@row, [Actual End Date]@row = ""), "Open-At Risk", [Actual Duration]@row - [Target Duration]@row), "Open-In Review")
2. =IF([Actual vs. Target Duration]@row <= 0, "Exceeded Target", "Missed Target")

The goal is as follows:

• Show the Open-In Review if the Actual Duration has an error but the target duration is in the future.
• Show Open-At Risk if the Actual Duration has an error but the target duration is in the past.
• Show Exceeded Target if the Actual vs. Target Duration is < or = 0.
• Show Missed Target if the Actual vs. Target Duration is > 0.

KPH

Hi @milbournr

Can you change the formula in Actual Duration to return an error message if Actual End Date is blank and then use that error message, rather than relying on the system error message? The change would be something like this:

=IF(ISBLANK([Actual End Date]@row), "Error", (NETDAYS([Start Date]@row, [Actual End Date]@row)))

I made some slight changes to the goal (main thing in bold) and have written the formula in the same order as the goal.

• IF the Actual Duration shows error but the target end date is after today return Open-In Review.
• IF the Actual Duration shows error but the target end date is not after today return Open-At Risk.
• IF the Actual Duration does not show an error...
• if the Actual vs. Target Duration is < or = 0 return Exceeded Target
• if not return Missed Target

If that is what you want, this formula should do the trick

=IF([Actual Duration]@row = "Error", IF([Target End Date]@row > TODAY(), "Open-In Review", "Open-At Risk"), IF([Actual vs. Target Duration]@row <= 0, "Exceeded Target", "Missed Target"))

If you don't want to use TODAY to automatically find the date of today and instead want to pull the date from row 16 then you could use this:

=IF([Actual Duration]@row = "Error", IF([Target End Date]@row > [Start Date]$16, "Open-In Review", "Open-At Risk"), IF([Actual vs. Target Duration]@row <= 0, "Exceeded Target", "Missed Target"))

The result is: