Progressive Commission / IF Help

try changing this... =IF(D12>20999,D12*70%,IF(D12<36001,D12*75%,IF(D12>44001,D12*80%,)*IF(D12>56749,D12*85%,)))

to this... =IF(D12>20999,D12*70%,IF(D12<36001,D12*75%,IF(D12>44001,D12*80%,IF(D12>56749,D12*85%)))

A different approach:

Please Login or Register  to view this content.

Cheers,

Trying what you wrote I see why it doesn't work even with my "tweek". You have amounts that look like they are conflicting. For example you don't address what happens if the value is less than 21000, then you have it both greater than 20999 AND less than 36001 which, what happens if it is less than 36001?
Bottom line, I think your best bet is to use what ConneXL gave you, I think it will solve your problem.

I find your description somewhat confusing. Can you mock up a table with values and expected commissions including the transition points from one commission to another?

I have used your IF formula but reversed it so that it goes from highest value to lowest value with 3% being at the last of it for values under 20999. It is not clear if there is a base commission and the other commission levels are added or what the situation is.

This example is the most basic of the possible computations.

Formula:

Please Login or Register  to view this content.

This is the result for various values of D12 using the above formula. Is this what you expect? If different, what do you expect?

	D	E
11	Amount	Commission
12	20000	600
13	21000	15750
14	22000	16500
15	23000	17250
16	24000	18000
17	25000	18750
18	26000	19500
19	27000	20250
20	28000	21000
21	29000	21750
22	30000	22500
23	31000	23250
24	32000	24000
25	33000	24750
26	34000	25500
27	35000	26250
28	36000	27000
29	37000	27750
30	38000	28500
31	39000	29250
32	40000	30000
33	41000	30750
34	42000	31500
35	43000	32250
36	44000	33000
37	45000	36000
38	46000	36800
39	47000	37600
40	48000	38400
41	49000	39200
42	50000	40000
43	51000	40800
44	52000	41600
45	53000	42400
46	54000	43200
47	55000	44000
48	56000	44800
49	57000	48450
50	58000	49300
51	59000	50150
52	60000	51000
53	61000	51850

Thanks so much for everyone's help! I had to get clarification from the person who sent me on this journey to find out exactly what the "bleep" she was looking for. Here is my understanding. I am positive that I have incorrect number ranges since I wasn't sure 100% what the exact ranges were to based on the limited info give. Thanks Again! Y'all are amazingly quick & informative!

Excel1.png

Date Sold Property Address Sales Price (SP) 3 % Commission ( on SP)
1/28/2015 1234 That Street $200,000.00 $6,000.00
1/28/2015 Property 1 $200,000.00 $6,000.00
1/28/2015 Property 2 $200,000.00 $6,000.00
1/28/2015 Property 3 $200,000.00 $6,000.00
1/28/2015 Property 4 $200,000.00 $6,000.00
1/28/2015 Property 5 $200,000.00 $6,000.00

Gross Commission $36,000.00


Percentage of Gross Commission:
70% Gross of commission until Associates share equals (0 - 21000) 21,000
75% Gross of commission until Associates share equals (21001 - 36000) 36,000
80% Gross of commission until Associates share equals (36001 - 44000) 44,000
85% Gross of commission until Associates share equals (44001 - 56570) 56,570
90% Gross of commission until Associates share equals (56751 & up) 56,750

please attach sample workbook with desired result

what is your desired result in this case

sorry,
can you please tell me in your attached sheet what is the progressive commission?

=lookup(d12,{0,21001,36001,44001,56751},{70,75,80,85,90}%)

in this case, it is a matter of what you are comfortable with and understand. Lookup has a greater capacity for levels than nested IFs that is limited to 7 levels. If however, the levels change, then in both cases the values being hard coded will have to be changed in the formula.

Another way to approach this is to use VLOOKUP with a table of values. When a value changes, just change it in the table and you don't have to mess with the formula at all.

Here is a file to experiment with.