SOLVED

Highlighted
New Contributor

Excel formula to apply variable percentages

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

5 Replies
Highlighted

Re: Excel formula to apply variable percentages

If put above in helper range

in D8

``=B8*LOOKUP(B8,\$E\$2:\$E\$4,\$F\$2:\$F\$4)``

and drag it down

Highlighted

Re: Excel formula to apply variable percentages

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

Highlighted
Best Response confirmed by Nicola_Ed (New Contributor)
Solution

Re: Excel formula to apply variable percentages

When 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))``
Highlighted

Re: Excel formula to apply variable percentages

Great - thank you. Worked a treat!
Highlighted

Re: Excel formula to apply variable percentages

@Nicola_Ed , you are welcome