Forum Discussion
Solved
Help with a table
Hi everyone,
I'm new to the community and am looking for some help with a table I'm trying to put together.
I need a spread sheet to calculate the charges for investments at different levels. The charge is tiered depending on the amount involved.
The attached shows how the tiering works.
I have tried using IF and AND formulas, including MIN and MAX as well be seem to be going round in circles.
Can anybody point me in the right direction?
Thanks in advance.
John
Hello John,
I misunderstood your question. Now, I get it ;-)
What you need is a simple table with helper column and SUMPRODUCT Function.
Formula in F3 is =SUMPRODUCT(--($F$2>$A$2:$A$6),--($F$2-$A$2:$A$6),$D$2:$D$6)
7 Replies
you could set up a table like the one I have attached.
you can achieve your desired calculation using simply VLOOKUP formula and while many users may not be aware of VLOOKUP approximate match feature which is very handy for such calculations.
please see attached file.
Thanks for your speedy response Jamil, however, what I want if the facility to put an amount in F2 and for the spread sheet to calculate the overall charge.
So, for example, if the amount is £130,000 the calculation would be:
£25,000 @ 0.50% = £125
The next £75,000 @ 0.35% = £262.50
The next £30,000 @ 0.30% = £90.00
And then a total at the bottom i.e. £477.50.
Thanks
John
In that case then you can use the modified formula
=IF($F$2<>"",$F$2*VLOOKUP($F$2,$A$2:$B$6,2,TRUE),"")
please see it in the attached workbook.
