The Book-keepers Forum (BKF)

Post Info TOPIC: Excel Formula Help Please


Senior Member

Status: Offline
Posts: 100
Date:
Excel Formula Help Please


Good Morning All - Happy Monday to you!

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. 

Thanks in advance.

Victoria

 



__________________

Victoria

"It's not what you earn, it's what you spend"



Master Book-keeper

Status: Offline
Posts: 7535
Date:

Hi Victoria You need to bung in a Vlookup, I reckon.



__________________

Joanne  

Fallows Hall Ltd 

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

 



Master Book-keeper

Status: Offline
Posts: 7535
Date:

Eg - page 1 &2

 

Edited - page one proves the calc

page 2 not showing formula for some reason once I posted it, so here it is

Table of xyz  
TiersTier minimumRate
0-500000.05
5001-1000050010.1
10001-15000100010.15
15001+150010.2
   
   
£ Rate
400 =VLOOKUP(B11,$C$4:$C$7:$D$4:$D$7,2,TRUE)
12500 =VLOOKUP(B12,$C$4:$C$7:$D$4:$D$7,2,TRUE)
16000 =VLOOKUP(B13,$C$4:$C$7:$D$4:$D$7,2,TRUE)
25000 =VLOOKUP(B14,$C$4:$C$7:$D$4:$D$7,2,TRUE)
1 =VLOOKUP(B15,$C$4:$C$7:$D$4:$D$7,2,TRUE)
5001 =VLOOKUP(B16,$C$4:$C$7:$D$4:$D$7,2,TRUE)
10000 =VLOOKUP(B17,$C$4:$C$7:$D$4:$D$7,2,TRUE)
10001 =VLOOKUP(B18,$C$4:$C$7:$D$4:$D$7,2,TRUE)


-- Edited by Cheshire on Monday 25th of February 2019 04:09:13 PM

Attachments
VLookup eg.xls (19.5 kb)
__________________

Joanne  

Fallows Hall Ltd 

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

 



Senior Member

Status: Offline
Posts: 100
Date:

Joanne - thanks for your help - I am pleased that it does look as difficult as I thought it was!!!
I'll try your suggestion
Regards

__________________

Victoria

"It's not what you earn, it's what you spend"



Forum Moderator & Expert

Status: Offline
Posts: 11622
Date:

Hi Victoria,

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.

Excel T1.jpg

 

 

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)

Excel T2.jpg

 

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 biggrin

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.



Forum Moderator & Expert

Status: Offline
Posts: 11622
Date:

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.



Senior Member

Status: Offline
Posts: 198
Date:

Hi Victoria,

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.

Capture.PNG

Capture2.PNG

 

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

__________________

Ian

Ian Brown FCA
Onion Reporting Software Ltd

www.onionrs.co.uk

Sage accounts in Excel. No set-up necessary. Free trial.



Master Book-keeper

Status: Offline
Posts: 7535
Date:

oops. Shouldve known to leave it to the masters. Slapped legs

 

OP not been back on from what I can see though.



__________________

Joanne  

Fallows Hall Ltd 

Winner - Bookkeeper of the Year 2015, 2016 and 2017

Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.

 

Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  

©2007-2019 The Book-keepers Forum (BKF). All Rights Reserved. The Book-keepers Forum (BKF) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Book-keepers Forum and BKF are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Book-keepers Forum. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Book-keepers Forum. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: info@bookcert.co.uk.

Privacy & Cookie Policy  About