Apr 11 2020 10:44 AM
Hi I need a formula to apply to a cumulative income total which calculate percentages of the total based on its value as follows:-
0 to £5,814 - 0%
£5,815 to £23,225 - 100%
£23,226 and over - 80%
So in cells D8 to D19.
How do i do this ? Thanks
Apr 11 2020 10:53 AM
Apr 11 2020 11:29 AM
Thanks - sorry i may not have explained. The percentages are in bands - so if the income is £5,000 i want it to show zero. Once it gets past, £5,814 its 100% of the amount over £5,814. So when its £7,200 it should only show £1,386. I think its a few ifs as :-
If less than £5,814 then zero.
100% of everything between £5,815 and £23,225
80% of everything over £23,226
So the result will be all three added together ( I know the first result will always be nil)
Thanks
Apr 11 2020 01:20 PM
SolutionWhen I'd use the column with % change compare to previous range
with formula
=SUMPRODUCT((B8-$E$2:$E$4)*$G$2:$G$4*(B8>$E$2:$E$4))
Apr 12 2020 03:45 AM
Apr 11 2020 01:20 PM
SolutionWhen I'd use the column with % change compare to previous range
with formula
=SUMPRODUCT((B8-$E$2:$E$4)*$G$2:$G$4*(B8>$E$2:$E$4))