Need IF Formula to ignore empty fields

[epinske]

I working on a formula that will provide me with 4 different outcomes depending on a products Variance from our target cost of product/FOB. Any products that have a variance less than -5% need to output "Red -5% or More Variance", products with a variance between -1% and -4.99% need to output "Orange -1% to -4.99% Variance", products with a variance greater than -0.99% need to output "Green - Meeting or Exceeding Target", and any products that have an empty value need to return an empty value. I have figured out how to return most of these responses, but I'm hung up on how to return the empty value. Any help will be greatly appreciated!

IF({P1 FOB % Variance From Target}<= -.05, "Red -5% or More Variance",

IF(AND({P1 FOB % Variance From Target}>= -.0499, {P1 FOB % Variance From Target}<= -.01), "Orange -1% to -4.99% Variance",

IF({P1 FOB % Variance From Target}>= -0.0099 , "Green - Meeting or Exceeding Target")))

[Mike_AutomaticN · Accepted solution]

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

[Kenneth_Raghuna]

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.

[epinske]

Thank you so much Mike. I've been tinkering with the formula the past few days, and the revision worked perfectly. 

[Mike_AutomaticN]

I'm glad to hear that @epinske!!

Mike, Consultant @ Automatic Nation