Can cell have multiple functions?

Cazzard

New Member
Joined
Mar 28, 2002
Messages
3
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sure, sounds like a lookup table or a nested if statement.....can you give be a more elaborate example please.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
In column B's cell, do this:

Data-Validation.
Choose List.
Type:

17%,18%,19%,20%

:)
 
Upvote 0
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
 
Upvote 0
Wow thanks all for the help. Thought I would have to wait days for someone to answer(not seconds) LOL. Thanks again problem solved.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top