+4Would appreciate some help on IF statements:
I have a field for “WEIGHT” and another for “WEIGHT RANGE”. I would like user to enter his/her weight and need a formula to yield the weight range. eg
42 >> 40-45
48>> 46-50
52>> 51-55
56>> 56-60
63>> 61-65
69>> 66-70
Would there be better alternatives using AND or SWITCH or anything else?
Thank you.
Best answer by James_Quek
A SWITCH statement won’t work well because you would have to enter every single possible number. Nested IF statements are the way to go in this situation.
IF( {weight} < 40, "under 40",
IF( {weight} <= 45, "40-45",
IF( {weight} <= 50, "46-50",
IF( {weight} <= 55, "50-55",
IF( {weight} <= 60, "56-60",
IF( {weight} <= 65, "61-65",
IF( {weight} <= 70, "66-70",
"71+"
)))))))
[SOLVED]
Thanks so much for this elegant solution. Works beautifully.
+4try this and see if this will get through the syntax filter.
IF({FIELD NAME} = “42”, “40-45”, IF({FIELD NAME} = “48”, “46-50”, IF({FIELD NAME} = “52”, “51-55”, IF({FIELD NAME} = “56”, “56-60”, IF({FIELD NAME} = “63”, “61-65”, IF({FIELD NAME} = “69”, “66-70”))))))
You dont need your weight range field with this formula
+29A SWITCH statement won’t work well because you would have to enter every single possible number. Nested IF statements are the way to go in this situation.
IF( {weight} < 40, "under 40",
IF( {weight} <= 45, "40-45",
IF( {weight} <= 50, "46-50",
IF( {weight} <= 55, "50-55",
IF( {weight} <= 60, "56-60",
IF( {weight} <= 65, "61-65",
IF( {weight} <= 70, "66-70",
"71+"
)))))))
A SWITCH statement won’t work well because you would have to enter every single possible number. Nested IF statements are the way to go in this situation.
IF( {weight} < 40, "under 40",
IF( {weight} <= 45, "40-45",
IF( {weight} <= 50, "46-50",
IF( {weight} <= 55, "50-55",
IF( {weight} <= 60, "56-60",
IF( {weight} <= 65, "61-65",
IF( {weight} <= 70, "66-70",
"71+"
)))))))
[SOLVED]
Thanks so much for this elegant solution. Works beautifully.
+4try this and see if this will get through the syntax filter.
IF({FIELD NAME} = “42”, “40-45”, IF({FIELD NAME} = “48”, “46-50”, IF({FIELD NAME} = “52”, “51-55”, IF({FIELD NAME} = “56”, “56-60”, IF({FIELD NAME} = “63”, “61-65”, IF({FIELD NAME} = “69”, “66-70”))))))
You dont need your weight range field with this formula
Thanks for this. Will try it out. Much appreciated!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.