Calculation assistance please

Hi

I have a workbook with 2 list boxes linked to B7 and D7 which looks up a flat service rate in the range C16 to D204 and this work fine and returns the basic charge for a weekday or weekend service for the first kilo

=INDEX(C16:D204,B7,D7)

The spanner in the works is that I have now been asked to make it calculate for a second and then subsequent kilos so I now have a cell H7 to enter the weight and I have added a column E16:E204 which contains a second and subsequent kilo charge.

When a Country is selected from B7 and a service from D7 I would like a calculation in H11 to take the weight from H7 deduct it by 1 (because this is already included in the base charge in C16:D204) and then take the remaining kilos and multiply by the additional kilo rate in H7.

I tried it like this but it says I have to few arguements

=INDEX(C16:D204,B7,D7)+H7-1*INDEX(E16:E204)

Can somebody point me in the right direction please?

Thanks

Danny

How do you determine which value to take from E16:E204? If the 10th cell is for 10 kg etc then try

=INDEX(C16:D204,B7,D7)+INDEX(E16:E204,H7-1)

Hi

Thanks for the prompt response, that did not seem to work, maybe the attached stripped down example will give you a better idea of what I am trying to do

Thanks

Danny

Hi Oldman

Thanks for the input, this however miscalculates, if for example I enter 10 (kilos) in H11 and have selected UK - Aberdeen I would expect the following

base charge C16 (£87.75) + 9 * E16 (2.45*9) so the result should be £109.80but using the suggested formula it returns £95.30?

Perhaps I am implementing it incorrectly

Danny

Wow

Well done that seems spot on, I will stress test it to death now

Thanks

Danny