# Formula Help

#### CodyBBall05

##### New Member
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!!!!

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### jolivanes

##### Well-known Member
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)?

##### Well-known Member
Welcome to MrExcel Message Board.
Create One Index Table (example Column A to C) then
My formula Result only Related to Column A.
Book1
ABCDIMNO
1P1P2Value
20%50%-4.5
35%55%-4
410%60%-3.5
515%65%-3P1P2Formula
620%70%-2.590%140%4.5
725%75%-278%127%3
830%80%-1.564%114%1.5
935%85%-150%100%0.5
1040%90%-0.5
1145%95%0
1250%100%0.5
1355%105%1
1460%110%1.5
1565%115%2
1670%120%2.5
1775%125%3
1880%130%3.5
1985%135%4
2090%140%4.5
21
Sheet1
Cell Formulas
RangeFormula
N6:N9N6=VLOOKUP(I6,\$A\$1:\$C\$20,3,TRUE)

#### CodyBBall05

##### New Member
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

##### New Member
Welcome to MrExcel Message Board.
Create One Index Table (example Column A to C) then
My formula Result only Related to Column A.
Book1
ABCDIMNO
1P1P2Value
20%50%-4.5
35%55%-4
410%60%-3.5
515%65%-3P1P2Formula
620%70%-2.590%140%4.5
725%75%-278%127%3
830%80%-1.564%114%1.5
935%85%-150%100%0.5
1040%90%-0.5
1145%95%0
1250%100%0.5
1355%105%1
1460%110%1.5
1565%115%2
1670%120%2.5
1775%125%3
1880%130%3.5
1985%135%4
2090%140%4.5
21
Sheet1
Cell Formulas
RangeFormula
N6:N9N6=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
ABCDIMNO
1P1P2Value
20%50%-4.5
35%55%-4
410%60%-3.5
515%65%-3P1P2Formula
620%70%-2.590%140%4.5
725%75%-278%127%3
830%80%-1.564%114%1.5
935%85%-150%100%0.5
1040%90%-0.5
1145%95%0
1250%100%0.5
1355%105%1
1460%110%1.5
1565%115%2
1670%120%2.5
1775%125%3
1880%130%3.5
1985%135%4
2090%140%4.5
21
Sheet1
Cell Formulas
RangeFormula
N6:N9N6=VLOOKUP(I6,\$A\$1:\$C\$20,3,TRUE)
works great, thanks for the help man!!!!

##### Well-known Member
You're Welcome & thanks for feedback.

Replies
0
Views
306
Replies
1
Views
381
Replies
1
Views
283
Replies
0
Views
360
Replies
2
Views
6K

1,147,564
Messages
5,741,856
Members
423,691
Latest member

### 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.

### Which adblocker are you using?

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

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