Nest multiple IFs if you want different results based on different values in the same cell

[cindygo]

I can’t seem to figure this out.

There are only 4 different contracts offered. Depending on the contract signed the commissions is based on if there is a balance owing. For three of the contracts this is true, for the fourth the commission is always the same regardless of PIF. Example:

Contract Commission
$ 5,995.00 If PIF $525, If any balance owing $225
$ 7,990.00 If PIF $700, If any balance owing $300
$ 9,985.00 If PIF $875, If any balance owing $375
$ 1,195.00 Always $125

The spreadsheet has a row for each sale. The commission (D) should calculate from what is entered in (B) and (C).

A B C D
1 Name Contract Balance Owing Commission
2 Client 1 $ 5,995.00 $ - $ 525.00
3 Client 2 $ 5,995.00 $ 4,000.00 $ 225.00
4 Client 3 $ 7,990.00 $ - $ 700.00
5 Client 4 $ 7,990.00 $ 6,950.00 $ 300.00
6 Client 5 $ 9,985.00 $ - $ 875.00
7 Client 6 $ 9,985.00 $ 3,000.00 $ 375.00
8 Client 7 $ 1,195.00 $ - $ 125.00
9 Client 8 $ 1,195.00 $ 1,195.00 $ 125.00

As always, thanks for your help!
Cindy

 

[njimack]

A bit unwieldy but it does the trick...

Excel Workbook
	A	B	C	D
1	Name	Contract	Balance Owing	Commission
2	1	5995	0	525
3	2	5995	4000	225
4	3	7990	0	700
5	4	7990	6950	300
6	5	9985	0	775
7	6	9985	3000	375
8	7	1195	0	125
9	8	1195	1195	125
Sheet1

EDIT - a more efficient solution would be to have separate tables which hold details of contract size and commission payable, and then use INDEX and MATCH to retrieve the correct result.

 

[cindygo]

Not what I thought it would look like but it works perfectly! I understand the idea of separate tables but this will work for this project.
Thanks for your help Neil!
Cindy