Commission Percent based on Gross Margin Percent Formula

45% is greater than 30%, so the nested IF() returns the "value_if_true" argument for the >30% test. In order to get your nested IF() to work, you need to make sure all of your tests are the same direction =IF(<30%,...,IF(<35%,...,IF(<40%,...,etc.)))

If you are interested, I would be inclined to use a lookup table and lookup function with the approximate match option (4th argument of VLOOKUP() is TRUE). With a small change to your "lookup table" (put 0% in where you have <=29% and enter 55% where you have >=55%), then a simple =VLOOKUP(margin,lookup_table,2,TRUE) will return the corresponding commission.

If the commmission rate isn't interpolated between tiers,

	A	B	C
1	GM	Comm
2	0%	0%
3	30%	6%
4	25%	7%
5	40%	8%
6	45%	9%
7	50%	10%
8	55%	11%
9	100%	11%
10
11	GM	Comm
12	29%	0%	B12: =VLOOKUP(A12, $A$2:$B$9, 2)
13	30%	6%
14	34%	7%
15	35%	7%
16	39%	7%
17	40%	8%
18	44%	8%
19	45%	9%
20	49%	9%
21	50%	10%
22	54%	10%
23	55%	11%
24	59%	11%

Try

=LOOKUP(E14,{0,0.29,0.30,0.35,0.40,0.45,0.50,0.55},{0,0,0.6,0.7,0.8,0.9,0.10,0.11})

Hi

One way

Formula:

Please Login or Register  to view this content.