IF Formula with multiple choices and values

Katherine Simpson

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.

Frank S.

@Katherine Simpson

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 S.

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.

Colleen Patterson

@Katherine Simpson

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))))

Katherine Simpson

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

Katherine Simpson

@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?

Katherine Simpson

@Frank S.

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")))))

Frank S.

@Katherine Simpson

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.

Katherine Simpson

@Frank S.

Thank you so much! That works perfectly!