IF Formula with multiple choices and values
I am trying to write a formula that will have the following conditions:
If the "Number of Affected Applications" equals 0 to 1, then the value in the CALC column would be "1"
If the "Number of Affected Applications" equals 2 to 4, then the value in the CALC column would be "2"
If the "Number of Affected Applications" equals 5 to 7, then the value in the CALC column would be "3"
If the "Number of Affected Applications" equals 8 to 9, then the value in the CALC column would be "4"
If the "Number of Affected Applications" equals 10 or more, then the value in the CALC column would be "5"
I had the formula working when it was only 1 condition, but adding these conditions, I can't figure out.
Best Answer

Good morning.
Thank you for the clarification.
Here is an updated formula:
=IF([Number of Affected Applications]@row = "0 to 1", "1", IF([Number of Affected Applications]@row = "2 to 4", "2", IF([Number of Affected Applications]@row = "5 to 7", "3", IF([Number of Affected Applications]@row = "7 to 9", "4", "5"))))
You do not need the "Error" statement in your formula above.
Frank Smith, PMP
Assistant Director  IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎
Answers

Greetings @Katherine Simpson,
Here is an example of the formula:
=IF(OR([Number of Affected Applications]@row >= 0, [Number of Affected Applications]@row >= 1), "1", IF(OR([Number of Affected Applications]@row >= 2, [Number of Affected Applications]@row >= 4), "2", IF(OR([Number of Affected Applications]@row >= 5, [Number of Affected Applications]@row >= 7), "3", IF(OR([Number of Affected Applications]@row >= 8, [Number of Affected Applications]@row >= 9), "4", "5"))
So, if any of the four conditions are met, then the desired number otherwise 5.
I hope this gets you on the right track.
Frank Smith, PMP
Assistant Director  IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎

If you are pulling both sets of data from the same sheet, the following would work:
=IF([Number of Affected Applications]@row <= 1, 1, IF([Number of Affected Applications]@row <= 4, 2, IF([Number of Affected Applications]@row <= 7, 3, IF([Number of Affected Applications]@row <= 9, 4, 5))))
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

I should have stated that the "0 to 1" is an option, not numbers. How would that change the formula?

@Frank S. Can you please help me by modifying the formula you provided where the equals are text such as "0 to 1", etc. Not numbers?

I have this, but it isn't working.
=IF([Number of Affected Applications]@row = "0 to 1", "1", "Error" IF([Number of Affected Applications]@row = "2 to 4", "2", "Error" IF([Number of Affected Applications]@row = "5 to 7", "3", "Error" IF([Number of Affected Applications]@row = "7 to 9", "4", "Error" IF([Number of Affected Applications]@row = "10 or more", "5", "Error")))))

Good morning.
Thank you for the clarification.
Here is an updated formula:
=IF([Number of Affected Applications]@row = "0 to 1", "1", IF([Number of Affected Applications]@row = "2 to 4", "2", IF([Number of Affected Applications]@row = "5 to 7", "3", IF([Number of Affected Applications]@row = "7 to 9", "4", "5"))))
You do not need the "Error" statement in your formula above.
Frank Smith, PMP
Assistant Director  IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎

Thank you so much! That works perfectly!
Help Article Resources
Categories
Check out the Formula Handbook template!