# More Than 7 Nested

#### TommyKay44

##### New Member
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: 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
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
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
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: Are IFS formulas best used for when text is involved?

#### TommyKay44

##### New Member
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: =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
Did you try either of the suggestions?

Replies
0
Views
91
Replies
3
Views
223
Replies
1
Views
128
Replies
7
Views
178
Replies
5
Views
357

### Forum statistics

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?    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