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

P

phillexcel

New Member
Joined
Aug 5, 2014
Messages
31
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? :eeek:
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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?

ABCDE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]value[/TD]
[TD="bgcolor: #FCE4D6"]fee[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]queries[/TD]
[TD="bgcolor: #FFF2CC"]total fee[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]£1.00[/TD]
[TD="align: right"]£1,250.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£0.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]£50,001.00[/TD]
[TD="align: right"]£1,500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£1.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,250[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]£100,001.00[/TD]
[TD="align: right"]1.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£50,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,250[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]£125,001.00[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£50,001.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,500[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]£150,001.00[/TD]
[TD="align: right"]1.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£125,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£2,188[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]£200,001.00[/TD]
[TD="align: right"]0.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£125,001.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,875[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]£250,001.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£500,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£500[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]£300,001.00[/TD]
[TD="align: right"]0.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]£400,001.00[/TD]
[TD="align: right"]0.10%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet18​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(VLOOKUP(D2,$A$2:$B$10,2)>1,VLOOKUP(D2,$A$2:$B$10,2),VLOOKUP(D2,$A$2:$B$10,2)*D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
DRSteele said:
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?

ABCDE

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]value[/TD]
[TD="bgcolor: #FCE4D6"]fee[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]queries[/TD]
[TD="bgcolor: #FFF2CC"]total fee[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]£1.00[/TD]
[TD="align: right"]£1,250.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£0.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]£50,001.00[/TD]
[TD="align: right"]£1,500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£1.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,250[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]£100,001.00[/TD]
[TD="align: right"]1.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£50,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,250[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]£125,001.00[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£50,001.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,500[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]£150,001.00[/TD]
[TD="align: right"]1.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£125,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£2,188[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]£200,001.00[/TD]
[TD="align: right"]0.75%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£125,001.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£1,875[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]£250,001.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£500,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]£500[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]£300,001.00[/TD]
[TD="align: right"]0.25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]£400,001.00[/TD]
[TD="align: right"]0.10%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet18​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(VLOOKUP(D2,$A$2:$B$10,2)>1,VLOOKUP(D2,$A$2:$B$10,2),VLOOKUP(D2,$A$2:$B$10,2)*D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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%))))))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
NEED AN EQUATION FOR FINDING THE VALUE
Replies
5
Views
413
Peter_SSs
Peter_SSs
T
  • Question Question
Forumla for a sliding scale
Replies
3
Views
1K
ssingh75
S

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

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?

Disable AdBlock

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
Back
Top