Tiered Charges Calculation

Hi all,

I am new to the forum, joined about 10 mins ago, so forgive me I am do anything wrong!
I am starting to pull my hair out over a spreadsheet I am trying to build and could really do with some help! Disclosure; I am not Excel expert by any means but I am very good at following instructions!

I have attached the spreadsheet I am working on (I hope!) and a colleague has attempted a try at this but I KNOW the formulae is just not right somewhere a long the lines but I cannot work out where.

Basically, the company charges a tiered rate, decreasing, according to the amount of money invested with them.

The rates are as follows; £0 - £30,000 is 0.35%, the next £30,001 to £250,000 is 0.30%, the next £250,001 - £400,000 is 0.20% and the remaining amount (£400,001 +) is 0.10%

My colleague and I have used an IF formulae but it does not work for amounts under £250,000 (I cannot work out why) and it also doesn't split the investment amount correctly between the different tiers.
The total does not equal the actual amount invested either.

Please let me know if I am have made zero sense - I am happy to expand more on the issue, I am just rage typing as this has been going on for roughly a week now

I appreciate all the help in anticipation and thank you for taking your time to help us!

Hi ExcelNovice1991,

If it helps to understand the answers provided, this is the article I always reference whenever I have a similar query. Link

Hope it helps.

Regards,

Snook

Special Ks formulas are better and more succinct, so i would use them but your error was c6
=IF(ISBLANK($B$1),"",IF(AND($B$1<=250000,$B$1>250000),$B$1-30000,IF($B$1>250000,220000,"")))
it was the same as c7

I don't know if you'll see that I edited my answer so I thought I would just post it here again in case - sorry if I am being ultra annoying!

Thank you so much! I thought it was solved (hence my edit on this answer!) but it is still not working for amounts of, say, in my example, £58,362.12
It just puts all the money into the first tier but technically there will circa £20K left over that will fall into the second tier
Does any one know why??

You are all heroes in your own right! Thank you ever so much for taking the time out of your days to help me and my colleague - we will never be able to thank you enough for saving our Friday afternoons!
Thank you! Thank you! Thank you!

Hi again,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M