Formula Help

CodyBBall05

New Member
Joined
May 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 5, 2004
Messages
1,841
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
2,680
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
May 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!!!!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,680
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & thanks for feedback.
 

Forum statistics

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

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