Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula Help please
Hi,
I need a formula that will do the following please...
In the field "How Many" they will select from a drop down list
1200
201400
401600
>600
I will run the formula in the Cleaning Fee column.
Depending on which one they select, I need values to go into the Cleaning Fee column
So for 1200 the value would be 150.00
201400 would be 300.00, 401600 would be 450.00 and >600 would be 600.00.
Any help?
Thanks,
Comments

Heidi,
There are a few different ways to solve this. I'm not an expert, so others might have another solution. But one solution is to use the LOOKUP() function. The cavet to this solution is that, to the best of my knowledge, you have to include the Lookup table in the same sheet where the lookup function is executed. My suggestion would be to add your lookup table to some new columns you add to the far left of your sheet. You can even hide these columns if you need to. I've included a screen shot which shows how I laid out the table and the forumula.
Online Help for LOOKUP function can be found HERE
Hope this helps.
 Kent

Hi Heidi,
an alternative would be nested IF statements:
=IF([HowMany]1 < 201;150; IF([HowMany]1 < 401;300;IF([HowMany]1 < 601;450;600)))
Advantage: No separate or hidden columns.
Disadvantage: Not so user friendly to update the fee (if necessary).
Good luck

Heidi,
I like Michael's idea of using nested If statements. However if your "How Many" column is going to be a drop down list, you may need to write your formula like this.
=IF([How Many]1 = "1200", 150, IF([How Many]1 = "201400", 300, IF([How Many]1 = "401600", 450, IF([How Many]1 = ">600", 600, " "))))
Hope this helps,
Jason