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$141)*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$141) does not so the calculation is Â£10 too high (25*Â£10)
Tariff formula not adding properly (Excel 2003 SP3)

 SilverLounger
 Posts: 1952
 Joined: 26 Jan 2010, 12:46
 Location: Nr. Heathrow Airport
Tariff formula not adding properly (Excel 2003 SP3)
Steve
http://www.freightprouk.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
http://www.freightprouk.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

 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)
If you want us to check the calculations we'll have to see (a stripped down copy of) the workbook.
Best wishes,
Hans
Hans

 2StarLounger
 Posts: 102
 Joined: 04 Feb 2010, 22:44
 Location: Melbourne Australia
Re: Tariff formula not adding properly (Excel 2003 SP3)
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$141)*F109 formatted to 5 significant figures
Break out each component of the formula to check where it might be going wrong eg try =($D$141)*F109 formatted to 5 significant figures
Andrew Lockton
Melbourne Australia
Melbourne Australia

 SilverLounger
 Posts: 1952
 Joined: 26 Jan 2010, 12:46
 Location: Nr. Heathrow Airport
Re: Tariff formula not adding properly (Excel 2003 SP3)
Thanks HansHansV wrote:If you want us to check the calculations we'll have to see (a stripped down copy of) the workbook.
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.freightprouk.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
http://www.freightprouk.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

 5StarLounger
 Posts: 817
 Joined: 24 Jan 2010, 15:56
Re: Tariff formula not adding properly (Excel 2003 SP3)
Seems to be working to me. Can you give a specific example from the workbook you posted where it's wrong?
Regards,
Rory
Rory

 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)
Same as Rory: I don't see the discrepancy that you mention.
Best wishes,
Hans
Hans

 SilverLounger
 Posts: 1952
 Joined: 26 Jan 2010, 12:46
 Location: Nr. Heathrow Airport
Re: Tariff formula not adding properly (Excel 2003 SP3)
Mmrory wrote:Seems to be working to me. Can you give a specific example from the workbook you posted where it's wrong?
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.freightprouk.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
http://www.freightprouk.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