Thanks:  0
Likes:  0

# Thread: Can cell have multiple functions?

1. What I am trying to do is calculate comissions based on different % rates. What I would like to ideally do is have a menu in the cell that I can choose the different %rates to do the calculations with.

2. Sure, sounds like a lookup table or a nested if statement.....can you give be a more elaborate example please.

3. Try using a named ranges in your formula. Something like "=Sales* Comm". Name a cell on your sheet Comm and change that cell to reflect the current commision rate. You might even add a drop down box or spin control to change the rate.

4. Hi
If you have a list of rates that remain static, atleast for a period of time, you can type these rates in one column in consecutive rows, and refer to this list with validation.
Put the list way out of site somewhere, pick your cell in which you want the dropdown, click on Data, Validation, Allow, List, Select(Highlight) the list you formerly created, Make sure "In Cell Dropdown" is checked. If you want this in more than one cell, Copy current cell, choose the cells to paste, right click on these, choose paste special, choose validation.
This will provide a dropdown in a cell or cells with the values you predetermined.

There may be other ways, but this should work for you.
Have a nice day!
Tom

[ This Message was edited by: TsTom on 2002-03-29 06:58 ]

5. Ok lets say column A is the sales total what I would like column B to do is be able to choose a rate from 17%,18%,19% or 20% from drop box. Column C would display the calculated % total of the percentage chosen.

6. In column B's cell, do this:

Data-Validation.
Choose List.
Type:

17%,18%,19%,20%

7. First, you must have something that controls the choice. How is the Commision rate determined? Does it depend on who the Salesperson is, or on the amount of the sale, or something else? Once that's decided, you can easily automate the calculation based on input into one or two cells. Either a VLOOKUP, a Named Range, Data Validation Dropdown, or perhaps the CHOOSE function

8. Wow thanks all for the help. Thought I would have to wait days for someone to answer(not seconds) LOL. Thanks again problem solved.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•