Help on the IF Function

[JimWM2000]

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.

 

[wilkisa]

Perhaps a VLookup would be better suited for this

 

[Yogi Anand]

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]

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]

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]

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]

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]

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
	A	B	C	D	E	F	G	H
1		J	M	T
2	#1	8%	0%	0%
3	#2	1%	0%	10%
4	#3	6%	2%	5%
5
6	UsingIntersectionOperator				8%	2%	2%	0%
7	Insert|Name|Create thencheckTopRowandLeftColumn
8
9	key-in='#1'JincellE6andread8%
10
11	UsingCustomList					J	M	T
12					#1	0.8	0	0
13	createaCustomListviaTools|Options				#2	0.01	0	0.1
14	key-in#1incellE10thendragtoH10				#3	.06	0.02	0.05
Sheet7

</SPAN>

Regards!

Yogi Anand