Help on the IF Function

JimWM2000

New Member
Joined
Oct 22, 2002
Messages
3
I'm trying to formulate a speadsheet that will configure commissions based on a commission code. The commission code represents the % commission distributed to each sales person.

For Instance: Code #1 means that Jeff will receive 8% commission, Thom will receive 0% and Mike will receive 0%.

I've 18 different codes for commissions. Any suggestions are greatly appreciated.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
Perhaps a VLookup would be better suited for this
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Veronica:

Welcome to the Board!

I believe use of the VLOOKUP function will work for you -- this will require you to set up a table of commission values corresponding to either names of SalesPersons/SalesVolume.

If you care to post some sample data, we can talk about it even some more.

Regards!

Yogi
 

bmacias

Board Regular
Joined
Sep 11, 2002
Messages
213
Veronica.

Also, look into the "choose" function. We use it to do pretty much what you are describing. except we only have 4 codes instead of 18.

Lets say:
a1 = 1000 (The salary or sale amount)
b1 = 1 (the code you want, to us 1 equals 2%)
c1 = =choose(b1,.02,.03,.04,05)*a1

Hope this helps.
 

JimWM2000

New Member
Joined
Oct 22, 2002
Messages
3

ADVERTISEMENT

The Choose function seems to work for one value but how do I get it to work when a code represents 3 seperate commissions.

Ex: Code #4

Tom 5% Jeff 6% Mike 2%
 

JimWM2000

New Member
Joined
Oct 22, 2002
Messages
3
On 2002-10-23 15:27, Yogi Anand wrote:
Hi Veronica:

Welcome to the Board!

I believe use of the VLOOKUP function will work for you -- this will require you to set up a table of commission values corresponding to either names of SalesPersons/SalesVolume.

If you care to post some sample data, we can talk about it even some more.

Regards!

Yogi

Thanks for your input...I'm not very savy at this sort of thing yet...However, here is some sample data for you to view...

Code
#1 Jeff 8%
#2 Thom 10% Jeff 1%
#3 Thom 5% Jeff 6%
#4 Thom 5% Jeff 6% Mike 2%

Contract Amount Code Thom Jeff Mike

I'm trying to save time by not entering a formula for each salesman everytime...I want excel to recognize the code. For ex. upon entering #1 Thom would receive a 0 Jeff would receive 8% of the contact amount and Mike would receive a 0.

Hope this helps!
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
Set up a table with your codes going down and Names accross the type and populate the correct percents. It mike look like this
blank Jim bob Joe
Code1 1 2 3
Code2 4 5 6

You can name this COM_TAB via the name box.

The formula
=INDEX(COM_TAB,MATCH(A2,INDEX(COM_TAB,0,1),0),MATCH(A1,INDEX(COM_TAB,1,0),0))

will return the right percent based on the persons name in A1, and the code in A2. This entries must exactly match how they appear on the table (maybe employee numbers would be a better key?)

Any time commission rates change you only need update the table. Give the amount of variables you have here, an if statement doesn't appear to be feasible.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
On 2002-10-24 13:37, JimWM2000 wrote:
On 2002-10-23 15:27, Yogi Anand wrote:
Hi Veronica:

Welcome to the Board!

I believe use of the VLOOKUP function will work for you -- this will require you to set up a table of commission values corresponding to either names of SalesPersons/SalesVolume.

If you care to post some sample data, we can talk about it even some more.

Regards!

Yogi

Thanks for your input...I'm not very savy at this sort of thing yet...However, here is some sample data for you to view...

Code
#1 Jeff 8%
#2 Thom 10% Jeff 1%
#3 Thom 5% Jeff 6%
#4 Thom 5% Jeff 6% Mike 2%

Contract Amount Code Thom Jeff Mike

I'm trying to save time by not entering a formula for each salesman everytime...I want excel to recognize the code. For ex. upon entering #1 Thom would receive a 0 Jeff would receive 8% of the contact amount and Mike would receive a 0.

Hope this helps!

Hi Veronica:

I am going to suggest two methods for your ease of input --
1. the first method involveds using named ranges and the INTERSECTION operator
2. the second method involves use of Custom Lists

These methods may or may not work for you -- bust these are viable options for just the type of thing you are trying to do.

In any event, here is the worksheet simulation showing the use of each of the two methods.

By the way, in my simulation, I abbreviated Jeff to J, Mike to M, and Thom to T
y021026h1.xls
ABCDEFGH
1JMT
2#18%0%0%
3#21%0%10%
4#36%2%5%
5
6UsingIntersectionOperator8%2%2%0%
7Insert|Name|Create thencheckTopRowandLeftColumn
8
9key-in='#1'JincellE6andread8%
10
11UsingCustomListJMT
12#10.800
13createaCustomListviaTools|Options#20.0100.1
14key-in#1incellE10thendragtoH10#3.060.020.05
Sheet7
</SPAN>

Regards!

Yogi Anand
 

Forum statistics

Threads
1,147,455
Messages
5,741,222
Members
423,649
Latest member
steel1968

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
Top