Tariff formula not adding properly (Excel 2003 SP3)

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Tariff formula not adding properly (Excel 2003 SP3)

Post by 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)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Tariff formula not adding properly (Excel 2003 SP3)

Post by HansV »

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

User avatar
Guessed
2StarLounger
Posts: 102
Joined: 04 Feb 2010, 22:44
Location: Melbourne Australia

Re: Tariff formula not adding properly (Excel 2003 SP3)

Post by 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
Andrew Lockton
Melbourne Australia

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Tariff formula not adding properly (Excel 2003 SP3)

Post by steveh »

HansV wrote: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
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Tariff formula not adding properly (Excel 2003 SP3)

Post by rory »

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

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Tariff formula not adding properly (Excel 2003 SP3)

Post by HansV »

Same as Rory: I don't see the discrepancy that you mention.
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Tariff formula not adding properly (Excel 2003 SP3)

Post by steveh »

rory wrote: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
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin