Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I Need help with an excel formula. I made a list with the data validation option. Three options in the drop down list. does not except blank. For each option, I want to change the formula in another cell.

The list is in b23. based on which of the three options are selected I want d23 to multiply the sum of (d18-d22) by .02 or .01 or by .39 and display it. What formula do I write in d23?

This seems like a pretty basic question. Any basic guides on the topic would also be greatly appreciated.

share|improve this question

1 Answer 1

Here's two possible options: an easy way that doesn't look as nice, and a harder way that's a bit friendlier to users.

Use the multiplier values as the entries in the Validation list

Put your three multiplier values somewhere in your spreadsheet, perhaps G17:G20.

| VALUES |
| 0.02   |
| 0.01   | 
| 0.39   |

In cell B23, set the data validation Source to be =$G$18:$G$20. Finally, in cell D23, your formula should be: =SUM(D18:D22) * B23.

Use VLOOKUP() and a table to display labels, but select values

Here, you need a separate table that has the multipliers, and names you want displayed for each one. Here's some example values for cells F17:G20

| LABEL | VALUE |
| Tax A | 0.02  |
| Tax B | 0.01  | 
| Tax C | 0.39  |

In cell B23, set the data validation Source to be =$F$18:$F$20. Then, set the formula in D23 to this:

=SUM(D18:D22) * VLOOKUP(B23, F18:G20, 2)

As you can see, the second option really isn't that much harder, and can make it easier for users (and/or you). Your spreadsheet is a littler easier to understand, too, since the values are clearly labeled. They can even live on a separate worksheet, along with some documentation about what they're for.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.