Formula to add or subtract value based on item selected in drop down list

Jon Brown

Hi folks,

I would like to create a formula that can identify the item selceted in a drop down column and then automatically add a value based on the item selected in the drop down to the next cell.

Example if the item i selected from the dropdown list in column a was "deduct 10 points" I would like the formula to automatically deduct 10 points from the column b in the same row.

Any help is appreciated.

Thanks

Jon

J. Craig Williams

Jon,

 

Depending on the number of choices, you'll like want to build a formula using:

 

1. Nested IF's

2. VALUE() function

3. FIND() and/or various combinations of LEFT(), RIGHT()

 

Easist would probably be one formula to determine the value and then use the value in the column, but this is only for troubleshooting later.

 

Best practice would be to keep as consistent text in your dropdown as possible.

"deduct" instead of "Deduct", "deduct", and "subtract". More important for shorter formula would be to pad the numbers "deduct 05" instead of "deduct 5".

Lastly, you might take a short cut by changing the drop down to

a. deduct 10 points

b. deduct 5 points

c. add 5 points

d. add 10 points

or even

-10: deduct 10 points

-5: deduct 5 points

etc...

but that depends on how many items in your list and preference on how to display it.

The first applies to shorter lists.

 

Hope that helps

 

Craig