Forum Discussion
Help with a table
- Mar 27, 2018
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)
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
- JamilMar 27, 2018Bronze Contributor
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.
- John CottonMar 27, 2018Copper Contributor
Thanks again Jamil, but can the spreadsheet not populate the F column i.e. in my previous example, can it not see that the overall amount is £130,000 and populate F3 with £75,000 then F4 with £30,000 etc.?
If you look at my initial attachment it shows that the charge is applied in bands, up to £25k, then £25,001 up to £100k, then £100,001 to £500k etc.
John
- JamilMar 27, 2018Bronze Contributor
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)