More Than 7 Nested

TommyKay44

New Member
Joined
Aug 30, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to automate adding amounts to an employee's hourly rate of pay depending on the amount of business (sales) they did that week. Here's a pic of the 9 different levels:
1630346691295.png


I have it working fine with a nested IF formula for digits 1-7 with the formula below:
=IF(H10=1,(F10*0.5)+((G10*0.5)*1.5),IF(H10=2,(F10*2)+((G10*2)*1.5),IF(H10=3,(F10*5)+((G10*5)*1.5),IF(H10=4,(F10*7)+((G10*7)*1.5),IF(H10=5,(F10*10)+((G10*10)*1.5),IF(H10=6,(F10*12)+((G10*12)*1.5),IF(H10=7,(F10*15)+((G10*15)*1.5),0)))))))

My clients using it and, fortunately, hasn't needed levels 8 and 9 yet. An IFS formula seems ideal to use but when trying to change it to an IFS for levels 1-7, I can't get it to work. What am I doing wrong in the IFS formula below?
=IFS(H10=1,(F10*0.5)+((G10*0.5)*1.5),H10=2,(F10*2)+((G10*2)*1.5),H10=3,(F10*5)+((G10*5)*1.5),H10=4,(F10*7)+((G10*7)*1.5),H10=5,(F10*10)+((G10*10)*1.5),H10=6,(F10*12)+((G10*12)*1.5),H10=7,(F10*15)+((G10*15)*1.5),0)

Thank you in advance for any help I can get on this
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
695
Office Version
  1. 365
Platform
  1. Windows
How about leave the table and use a range lookup:

Mr Excel Playground 3.xlsm
GHIJKL
1Level 1$ -$ 0.50$ 8,200.00sales
2Level 2$ 3,500.00$ 2.00$ 7.00bonus
3Level 3$ 5,000.00$ 5.00
4Level 4$ 7,500.00$ 7.00
5Level 5$ 10,000.00$ 10.00
6Level 6$ 13,500.00$ 12.00
7Level 7$ 15,000.00$ 15.00
8Level 8$ 17,000.00$ 17.00
9Level 9$ 20,000.00$ 20.00
Sheet18
Cell Formulas
RangeFormula
K2K2=VLOOKUP(K1,H1:I9,2,TRUE)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,811
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
A lookup table would be the best option, but another way is like
Excel Formula:
=(F10*CHOOSE(H10,0.5,2,5,7,10,12,15))+(G10*CHOOSE(H10,0.5,2,5,7,10,12,15)*1.5)
 

TommyKay44

New Member
Joined
Aug 30, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
How about leave the table and use a range lookup:

Mr Excel Playground 3.xlsm
GHIJKL
1Level 1$ -$ 0.50$ 8,200.00sales
2Level 2$ 3,500.00$ 2.00$ 7.00bonus
3Level 3$ 5,000.00$ 5.00
4Level 4$ 7,500.00$ 7.00
5Level 5$ 10,000.00$ 10.00
6Level 6$ 13,500.00$ 12.00
7Level 7$ 15,000.00$ 15.00
8Level 8$ 17,000.00$ 17.00
9Level 9$ 20,000.00$ 20.00
Sheet18
Cell Formulas
RangeFormula
K2K2=VLOOKUP(K1,H1:I9,2,TRUE)
The actual sales ranges are basicaly just a a key diagram to lookup what level # to put in the cell in the H column. I probbably shouldn't have put it on my question. Here's a pic of it working with the nested formula addressing only levels 1-7:
1630353316663.png


Are IFS formulas best used for when text is involved?
 

TommyKay44

New Member
Joined
Aug 30, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Sorry. I'm new at this forum. I meant to reply the same thing to Fluff as I did to James Canale. The sales range table wasn't really relevant. Here's the formula working with the 7 levels nested:
1630354585957.png

=IF(H10=1,(F10*0.5)+((G10*0.5)*1.5),IF(H10=2,(F10*2)+((G10*2)*1.5),IF(H10=3,(F10*5)+((G10*5)*1.5),IF(H10=4,(F10*7)+((G10*7)*1.5),IF(H10=5,(F10*10)+((G10*10)*1.5),IF(H10=6,(F10*12)+((G10*12)*1.5),IF(H10=7,(F10*15)+((G10*15)*1.5),0)))))))

The only two levels left are 8 (+$17.00) and 9 (+$20.00)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,811
Office Version
  1. 365
Platform
  1. Windows
Did you try either of the suggestions?
 

Forum statistics

Threads
1,176,393
Messages
5,902,806
Members
434,997
Latest member
bigolbearking

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