Forum Discussion
Help with a table
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)
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.
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
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)
Jamil, you're a star.
Thanks very much for your patience and help. That works a treat.
Thanks again.
John