Hey @epinske!
To handle the case where the variance field is empty, you can include an additional IF condition at the start of your formula to check if the field is empty and return an empty value accordingly. Here’s the revised formula:
IF(
{P1 FOB % Variance From Target} = BLANK(), BLANK(),
IF({P1 FOB % Variance From Target} <= -0.05, "Red -5% or More Variance",
IF(AND({P1 FOB % Variance From Target} >= -0.0499, {P1 FOB % Variance From Target} <= -0.01), "Orange -1% to -4.99% Variance",
IF({P1 FOB % Variance From Target} >= -0.0099, "Green - Meeting or Exceeding Target")
)
)
)
Let me know if that solves the issue!
Mike, Consultant @ Automatic Nation
You can just return an empty string if the target field is empty, ie:
IF(Variance = "", "")
For this use case, you can just return an empty string as your default return when no other conditions have been met (the false return in your last IF statement).
Additionally, you can simplify your formula (removing the AND condition check) if you reverse the order that the formula checks the values against:
IF({P1 FOB % Variance From Target}>= -0.0099 , "Green - Meeting or Exceeding Target",
IF({P1 FOB % Variance From Target}>= -.0499, "Orange -1% to -4.99% Variance",
IF({P1 FOB % Variance From Target}<= -.05, "Red -5% or More Variance", ""
)))
While some may consider that the simplest way to write it, it'd probably be more in alignment with best practices to check if the string is empty first (see guard clauses):
IF({P1 FOB % Variance From Target}= "" , "",
IF({P1 FOB % Variance From Target}>= -0.0099 , "Green - Meeting or Exceeding Target",
IF({P1 FOB % Variance From Target}>= -.0499, "Orange -1% to -4.99% Variance",
"Red -5% or More Variance"
)))
In this case, we don't need an explicit check to see if the value is less than -5% , since we have already ruled out every other case, including the empty value.
Hey @epinske!
To handle the case where the variance field is empty, you can include an additional IF condition at the start of your formula to check if the field is empty and return an empty value accordingly. Here’s the revised formula:
IF(
{P1 FOB % Variance From Target} = BLANK(), BLANK(),
IF({P1 FOB % Variance From Target} <= -0.05, "Red -5% or More Variance",
IF(AND({P1 FOB % Variance From Target} >= -0.0499, {P1 FOB % Variance From Target} <= -0.01), "Orange -1% to -4.99% Variance",
IF({P1 FOB % Variance From Target} >= -0.0099, "Green - Meeting or Exceeding Target")
)
)
)
Let me know if that solves the issue!
Mike, Consultant @ Automatic Nation
Thank you so much Mike. I've been tinkering with the formula the past few days, and the revision worked perfectly.
