Log In
Register
Home
New Member
July 15, 2020
Offline
Hi everyone
So I'm trying to create a formula for my work so,
anything up to £150,000 I pay 2.5% and if its above that but below £500,000 then the difference is calculated at 1% but if its above £500,000 then its calculated at 0.5%
so just say I invested £800,000 the first £150,000 is calculated at 2.5 % making £3,750 then the remaining £350,000 to make up to £500,000 is calculated at 1% making £3500 then the remaining £300,000 to make up to the total of £800,000 is calculated at 0.5% making £1500 making a total of £8,750
hope that makes sense
thanks
Cally
See Answer
October 5, 2010
Online
Hi Cally,
If your value is in B2
=IF(B2<150000,B2*0.025,150000*0.025)+IF(B2>150000,IF(B2>500000,(350000*0.01)+(B2-500000)*0.005,(B2-150000)*0.01))
Regards
Phil
Answers Post
New Member
July 15, 2020
Offline
Hi
So if I wanted to show the breakdown of charges of the 3 bands what would be the formula in 3 separate cells.
for example as per the first example of £800,000 the total charge is £8,750, the first 150,000 is £3,750, second bracket is £3,250 and 3rd bracket is £1,750. I would like to show a breakdown of this in 3 separate cells.
thanks in advance.
October 5, 2010
Online
Hi Cally,
If you want the 3 separate calculations they will individually look a little different to the initial formula, because some of the logic encapsulated in the original formula has to be accounted for in a different way when using 3 separate formulae. But here they are
=IF($B$2<150000,$B$2*0.025,150000*0.025)
=IF($B$2>150000,IF($B$2>500000,(350000*0.01),($B$2-150000)*0.01),0)
=IF($B$2>500000,($B$2-500000)*0.005,0)
See attached,
Regards
Phil
1 Guest(s)
