Can cell have multiple functions?

Cazzard · Mar 29th, 2002, 07:49 AM

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.

wi_guy · Mar 29th, 2002, 07:51 AM

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

lenze · Mar 29th, 2002, 07:53 AM

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.

Tom Schreiner · Mar 29th, 2002, 07:55 AM

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 ]

Cazzard · Mar 29th, 2002, 07:56 AM

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.

Anne Troy · Mar 29th, 2002, 08:01 AM

In column B's cell, do this:

Data-Validation.
Choose List.
Type:

17%,18%,19%,20%

lenze · Mar 29th, 2002, 08:04 AM

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

Cazzard · Mar 29th, 2002, 08:13 AM

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

Mar 29th, 2002, 08:01 AM	#6
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	In column B's cell, do this: Data-Validation. Choose List. Type: 17%,18%,19%,20% __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 07:49 AM	#1
Cazzard New Member Join Date: Mar 2002 Posts: 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.

Mar 29th, 2002, 07:51 AM	#2
wi_guy Board Regular Join Date: Mar 2002 Posts: 50	Sure, sounds like a lookup table or a nested if statement.....can you give be a more elaborate example please.

Mar 29th, 2002, 07:53 AM	#3
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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.

Mar 29th, 2002, 07:55 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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 ]

Mar 29th, 2002, 07:56 AM	#5
Cazzard New Member Join Date: Mar 2002 Posts: 3	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.

Mar 29th, 2002, 08:04 AM	#7
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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

Mar 29th, 2002, 08:13 AM	#8
Cazzard New Member Join Date: Mar 2002 Posts: 3	Wow thanks all for the help. Thought I would have to wait days for someone to answer(not seconds) LOL. Thanks again problem solved.