Can someone help me with an excel formula please? My head is hurting and I am going round in circles!
I need to apply some different percentages to different parts of a number. The number may change, but the percentages and bands to which they apply won't change.
Ie, total value is £16750, but may vary. I need a formula that will work out 0-5000 @ 5%, 5001-10000 @ 10%, 10001-15000 @ 15% then anything over 15001 @ 20%.
I have a formula in use but it only works if the total value is over the 15001 figure, I need to make provision for the figure to drop below some of the % brackets.
I think I need to use 'IF' but I seem to have too many 'IF's and I am confusing excel, never mind myself! I am sure there is a blindingly obvious answer but I just can't get it this morning.
I'd be very grateful is anyone can guide me on this please.
for starters I would like to say that Joannes approach works but it's downfall is that in large spreadsheets Vlookups tend to be inefficient. The bigger they get, the slower they go.
From a performance perspective lets take a look at Index and Match.
1) create an array the same as Joannes.
2) for testing lets just set this up as a straight single cell lookup (the logic it the same whether its one cell or a million)
The yellow cell you can enter any value and the rate for that value is returned in the cell next to it.
In the rate cell enter this formula :
=INDEX($B$2:$C$5,MATCH($E$2,$B$2:$B$5,1),2)
So you ask, what does that actually mean?
=INDEX($B$2:$C$5, - we are setting up an array of the data area (locked down because the array will not change)
MATCH($E$2, - we are looking for the rate applicable to the value in cell e2 (locked down because the cell will not change)
$B$2:$B$5, - the array of the tier minimum values (locked down as it will not change)
1), - find the largest value less than or equal to the lookup value (note that the array must be in ascending order)
2) - Return the value in the second column of the Index.
In short, the index has set what is to be retrieved and the match defined the criteria.
That index and match can be used just as easily in an array. The only change to make would be changing the $E$2 (the value being sought) to the relevant cell that you want to find for any given row.
The two tables in there (B2:C5 and B2:B5) could both be replaced with named ranges which would divorce the formulae from positional processing (so adding more bands would mean changing the named range, not the formulae).
Lol, welcome to my world
all the best,
Shaun.
-- Edited by Shamus on Tuesday 26th of February 2019 06:25:32 PM
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Anyone else finding those damned intrusive ad's annoying?
I'm trying to explain how Index and Match would improve Excel performance over Vlookups and Google goes and takes a crap in the middle of the explanation.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Another variation on the same thing Joanne and Shaun have shared.
I like to lift all the figures required for the calculation using one "Match" operation. Column D contains the cumulative result at the point of the tier minimum and, to my mind, is the key to keeping the final calculation as simple as possible. I've split elements of the calculation out into individual cells so you can see better what is going on.
I hope this gives some more food for thought.
Regards,
Ian
Edit: Original example wrong. Doh!
-- Edited by Onion4Sage on Wednesday 27th of February 2019 09:37:05 AM
-- Edited by Onion4Sage on Wednesday 27th of February 2019 09:37:58 AM
