Formula Help please

Heidi Decker1

Hi,

I need a formula that will do the following please...

In the field "How Many" they will select from a drop down list

1-200

201-400

401-600

>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 1-200 the value would be 150.00

201-400 would be 300.00, 401-600 would be 450.00 and >600 would be 600.00.

Any help?

Thanks,

Kent Vaughn

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 

Michael G.

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

Jason Rowe

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 = "1-200", 150, IF([How Many]1 = "201-400", 300, IF([How Many]1 = "401-600", 450, IF([How Many]1 = ">600", 600, " "))))

 

Hope this helps,

 

Jason