Tariff formula not adding properly (Excel 2003 SP3)

[steveh]

Good morning

I have a formula for calculating rates on a tariff, the formula is

=IF(AND($D$10=D109,$D$14>1,$D$14<100),E109+$K$11+($D$14-1)*F109,0)

The result of this calculation should give me £560 in total but it is giving me £570, I suspect I know where the problem is but I cannot seem to correct it. I have a base rate (E109) , which in this instance is £215 and includes the first kilo, F109 is a per kilo rate in this instance £10 the weight of this shipment is 25 kilos so the calculation should be £215+24*£10+K11(which is £105)= £560. The weight is recorded in D14 and my suspicion is the part of the formula which I thought subtracted 1 kilo ($D$14-1) does not so the calculation is £10 too high (25*£10)

[HansV]

If you want us to check the calculations we'll have to see (a stripped down copy of) the workbook.

[Guessed]

Have you checked that this is not due to a rounding error? Check the exact values of each of your cells to verify that each displayed number is exact.

Break out each component of the formula to check where it might be going wrong eg try =($D$14-1)*F109 formatted to 5 significant figures

[steveh]

If you want us to check the calculations we'll have to see (a stripped down copy of) the workbook.

Thanks Hans

This is a very stripped down version with all macros and web queeries removed (this means the mileage calculator will not work unless entered manually but that will not affect the calculation)

Guessed: Thanks for the reponse but this are currencies and formatted to 2 places so there should be no rounding

[rory]

Seems to be working to me. Can you give a specific example from the workbook you posted where it's wrong?

[HansV]

Same as Rory: I don't see the discrepancy that you mention.

[steveh]

Seems to be working to me. Can you give a specific example from the workbook you posted where it's wrong?

Mm

Thats really strange, in the original workbook I was, and I now realise for only some calculations, getting a result that was £10.00 too much. After your response I managed to narrow this down to a tariff band that had a flat rate for the first kilo of £215 and then £10 per kilo thereafter no matter what weight I used over 1 kilo (also it was isolated to rows from 109). I have now removed all of the formulas and closed the workbook, copied it, and then reinstated the formulas and it seems to be working OK so I guess there must have been some corruption.

Thanks all for looking