Formula Help

[CodyBBall05]

Hello i am trying to create a spreadsheet that shows my bonus on my commission pay plan. but i am running into the issue of trying to create the formula for all of the specific numbers that are need

for example if l6 is greater than 90% and m6 is greater than 140% then n6 equals 4.50. this is the easy part, but it gets tricky when EX:
l6 is greater than 85% but less than 89.99% and m6 is greater than 135% but less than 139.99% then n6 equals 4.00
and it goes down the list in the percentage numbers both dropping in increments of 5 and the bonus number dropping in increments of .50

any help on this at all would be much appreciated!!!!

 

[jolivanes]

Are 90%, 140% and 4.5 the highest numbers and everything is down from there until the 4.5 ends up as 0 (zero)?
Is this just for one cell (I6)?

 

[maabadi]

Welcome to MrExcel Message Board.
Create One Index Table (example Column A to C) then
My formula Result only Related to Column A.

Book1
	A	B	C	D					I				M	N	O
1	P1	P2	Value
2	0%	50%	-4.5
3	5%	55%	-4
4	10%	60%	-3.5
5	15%	65%	-3						P1				P2	Formula
6	20%	70%	-2.5						90%				140%	4.5
7	25%	75%	-2						78%				127%	3
8	30%	80%	-1.5						64%				114%	1.5
9	35%	85%	-1						50%				100%	0.5
10	40%	90%	-0.5
11	45%	95%	0
12	50%	100%	0.5
13	55%	105%	1
14	60%	110%	1.5
15	65%	115%	2
16	70%	120%	2.5
17	75%	125%	3
18	80%	130%	3.5
19	85%	135%	4
20	90%	140%	4.5
21
Sheet1

Cell Formulas
Range	Formula
N6:N9	N6	=VLOOKUP(I6,$A$1:$C$20,3,TRUE)

 

[CodyBBall05]

yes it is all for one cell and it goes down from 4.50 to 1.00

Are 90%, 140% and 4.5 the highest numbers and everything is down from there until the 4.5 ends up as 0 (zero)?
Is this just for one cell (I6)

 

[CodyBBall05]

Welcome to MrExcel Message Board.
Create One Index Table (example Column A to C) then
My formula Result only Related to Column A.

Book1
	A	B	C	D					I				M	N	O
1	P1	P2	Value
2	0%	50%	-4.5
3	5%	55%	-4
4	10%	60%	-3.5
5	15%	65%	-3						P1				P2	Formula
6	20%	70%	-2.5						90%				140%	4.5
7	25%	75%	-2						78%				127%	3
8	30%	80%	-1.5						64%				114%	1.5
9	35%	85%	-1						50%				100%	0.5
10	40%	90%	-0.5
11	45%	95%	0
12	50%	100%	0.5
13	55%	105%	1
14	60%	110%	1.5
15	65%	115%	2
16	70%	120%	2.5
17	75%	125%	3
18	80%	130%	3.5
19	85%	135%	4
20	90%	140%	4.5
21
Sheet1

Cell Formulas
Range	Formula
N6:N9	N6	=VLOOKUP(I6,$A$1:$C$20,3,TRUE)

Welcome to MrExcel Message Board.
Create One Index Table (example Column A to C) then
My formula Result only Related to Column A.

Book1
	A	B	C	D					I				M	N	O
1	P1	P2	Value
2	0%	50%	-4.5
3	5%	55%	-4
4	10%	60%	-3.5
5	15%	65%	-3						P1				P2	Formula
6	20%	70%	-2.5						90%				140%	4.5
7	25%	75%	-2						78%				127%	3
8	30%	80%	-1.5						64%				114%	1.5
9	35%	85%	-1						50%				100%	0.5
10	40%	90%	-0.5
11	45%	95%	0
12	50%	100%	0.5
13	55%	105%	1
14	60%	110%	1.5
15	65%	115%	2
16	70%	120%	2.5
17	75%	125%	3
18	80%	130%	3.5
19	85%	135%	4
20	90%	140%	4.5
21
Sheet1

Cell Formulas
Range	Formula
N6:N9	N6	=VLOOKUP(I6,$A$1:$C$20,3,TRUE)

works great, thanks for the help man!!!!

 

[maabadi]

You're Welcome & thanks for feedback.