Calculating Mixed Percentage and Flat Figures within one Cell - Please can someone help?

[phillexcel]

Value in Column N

If the value in column N equals the below I need the calculated fee to appear in Column O

These are tiered rates, the first 2 bandings are flat rates, the rest are percentage based.

Value and Fee
£1-£50,000 = £1250
£50,001 - £100,000 = £1500
£100,001 - £125,000 = 1.75%
£125,001 - £150,000 = 1.5%
£150,001 - £200,000 = 1.25%
£200,001 - £250,000 = 0.75%
£250,001 - £300,000 = 0.50%
£300,001 - £400,000 = 0.25%
£400,001 + = 1%

I’m struggling to get this formula, the formula I’ve got in Column O2 at the moment is:

=N2*LOOKUP(N2,{100001,125001,150001,200001,250001,300001,400001},{0.0175,0.015,0.0125,0.0075,0.005,0.0025,0.01})

This sorts the problem with percentage fees but I can’t figure out how to get those flat fees for the lower values into the formula, please can someone help?

 

[DRSteele]

Would this satisfy your requirements? It's easier than LOOKUP, I think. Just copy E2 down as far as necessary.

Note that I think you meant 0.10% for the 400,000+ category. Also, notice what happens with a zero - is that acceptable?

	A	B	C	D	E
1	value	fee		queries	total fee
2	£1.00	£1,250.00		£0.00	#N/A
3	£50,001.00	£1,500.00		£1.00	£1,250
4	£100,001.00	1.75%		£50,000.00	£1,250
5	£125,001.00	1.50%		£50,001.00	£1,500
6	£150,001.00	1.25%		£125,000.00	£2,188
7	£200,001.00	0.75%		£125,001.00	£1,875
8	£250,001.00	0.50%		£500,000.00	£500
9	£300,001.00	0.25%
10	£400,001.00	0.10%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet18​

Worksheet Formulas Cell Formula E2 =IF(VLOOKUP(D2,$A$2:$B$10,2)>1,VLOOKUP(D2,$A$2:$B$10,2),VLOOKUP(D2,$A$2:$B$10,2)*D2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[phillexcel]

Would this satisfy your requirements? It's easier than LOOKUP, I think. Just copy E2 down as far as necessary.

Note that I think you meant 0.10% for the 400,000+ category. Also, notice what happens with a zero - is that acceptable?

	A	B	C	D	E
1	value	fee		queries	total fee
2	£1.00	£1,250.00		£0.00	#N/A
3	£50,001.00	£1,500.00		£1.00	£1,250
4	£100,001.00	1.75%		£50,000.00	£1,250
5	£125,001.00	1.50%		£50,001.00	£1,500
6	£150,001.00	1.25%		£125,000.00	£2,188
7	£200,001.00	0.75%		£125,001.00	£1,875
8	£250,001.00	0.50%		£500,000.00	£500
9	£300,001.00	0.25%
10	£400,001.00	0.10%

<tbody>
</tbody>

Sheet18​

Worksheet Formulas Cell Formula E2 =IF(VLOOKUP(D2,$A$2:$B$10,2)>1,VLOOKUP(D2,$A$2:$B$10,2),VLOOKUP(D2,$A$2:$B$10,2)*D2) <tbody> </tbody>

<tbody>
</tbody>

Thanks so much for the help, the £400,001 + section is 1% not 0.10%. I've managed to figure out the following formula that solves the problem for me, I'd prefer to have a table though as if any of the commission rates change I wouldn't need to amend the formula.

The formula that works for me so far is.....

=IF(N3<50001,1250,IF(N3<100001,1500,IF(N3<125001,N3*1.75%,IF(N3<150001,N3*1.5%,IF(N3<200001,N3*1.25%,IF(N3<250001,N3*0.75%,IF(N3<300001,N3*0.5%,IF(N3<400001,N3*0.25%,N3*1%))))))))