Your 3 formulas rearranged and made as consistent as possible in terms of FW80 and CH80 comparisons.
=IF(FW80="","Zero Forecast","Okay")
=IF(FW80>CH80,"Stay at Forecast","Adjust to pink")
=IF(AND(FW80>=GD80,GD80>=CH80),"Change to Open","Stay at Forecast")
BE VERY CLEAR that YOUR 1st formula allows FW80 = CH80 AND your 3rd formula allows FW80 = GD80 = CH80, so both formulas allow for equality. My experience leads me to believe that's likely a specification error.
You have these possibilities.
1. FW80 = "" (I figure FW80 would actually be blank).
The rest would then be FW80 <> ""/not blank.
2. CH80 < FW80 < GD80.
3. CH80 < GD80 < FW80.
4. FW80 < CH80 < GD80.
5. FW80 < GD80 < CH80.
6. GD80 < CH80 < FW80.
7. GD80 < FW80 < CH80.
8. CH80 = FW80 = GD80.
9. CH80 < FW80 = GD80.
10. CH80 = FW80 < GD80.
12. CH80 = GD80 < FW80.
13. FW80 = GD80 < CH80.
13. FW80 < CH80 = GD80.
14. GD80 < CH80 = FW80.
What should the results be for each of these cases? I can guess.
1. "Zero Forecast"
2. "Stay at Forecast"
3. "Change to Open"
4. "Adjust to pink"
5. "Adjust to pink"
6. "Stay at Forecast"
7. "Adjust to pink"
8. "Adjust to pink" OR "Change to Open" <- This is where the ambiguity in your original formulas strikes.
9. "Stay at Forecast"
10. "Adjust to pink"
11. "Stay at Forecast"
12. "Change to Open"
13. "Adjust to pink"
14. "Adjust to pink"
Is that correct? If so, and if NONE of these possibilities should return "Okay", then
=IF(COUNTBLANK(FW80),"Zero Forecast",IF(AND(CH80<=GD80,GD80<=FW80),"Change to Open",IF(FW80<=CH80,"Adjust to pink","Stay at Forecast")))
Bookmarks