Jonto

Hi Folks

I'm trying to put a formula into an excel speadsheet that works out a some fees on a sliding scale based on a sub total cell, but, i'll be honest, i'm struggling.

Is anyone able to provide me with the formula to work out the following?

First £2,000 - 0% fee
Next £1,700 - 100% fee
Next £1,500 - 75% fee
Next £396,000 - 15%
On the balance above £401,200 - fee is 1% untill the fee reaches a maxium of £80,000.

As an example, if the sub total cell totals:-

£1,800 there is no fee
£3,700 the fee is £1,700
£4,700 the fee is £2,450
£10,000 the fee is £3,545.

Thanks in advance!

Regards
Jonathan

fast bloke

You can't do that as a simple formula. Do a side table with the band limits and then do a nested if then referring to the side table to work out the fee

fast bloke

btw - strange looking fee structure. At 3700, someone is spanking cash.

Jonto

Ok, thanks for the help.

Never done of those before but i will have a go and if i have any problems i will let you know!

jods

I'll send you the solution.


























For £1700

Jonto

Excellent, Cheers Jods, will you send me a pm







































NOT

DJ_Jon

iTrader: (7)

You might be able to do it with a nested IF statement. Personally I would use VBA to solve this problem & have a button on the screen. If I get a chance I'll try & knock something up for you.

Puff The Magic Wagon!

iTrader: (2)

I'm bored...

=IF(A4<2000.001,A4-A4,IF(A4<3700.001,(((A4-2000)*2)+2000)-A4,IF(A4<5200.001,(((A4-3700)*1.75)+5200)-A4,IF(A4<396000.001,(((A4-5200)*1.15)+8025)-A4,IF(A4<2251500.001,(((A4-396000)*1.01)+457445)-A4,80000)))))

A4 is where I had the gross figure. Your result for £4700 is not correct I believe?

dunx

iTrader: (3)

I believe it is...

LOL

dunx