Need help with figuring out the algorithms for a problem.

[halfass]

Currently I'm working on a a project using Laravel php.

 

What I have is an invoice with items in it.

The item list goes quantity * price = subtotal

 

/*initial invoice*/
12 x 33.23 = 398.76‬
13 x 20.27 = 263.51‬
____________________
total      = 662.27

 

And then payment was made to the invoice.

 

/*payment was made*/

total      = 662.27
payment    = 300.00
--------------------
balance    = 362.27

 

now what i need to do is create a credit note that have all the item with the same quantity and the credit note total is adjusted to the balance. basically i need to adjust the item price

 

so what i did was price * balance/initial total

 

/*calculation for new price in credit note*/
33.23 x 362.27/662.27 = 18.18
20.27 x 362.27/662.27 = 11.09

 

but when i implement the new price into the credit note, it got close to the balance. but does not get exactly the balance.

 

/*implementation of new price in credit note*/
12 x 18.18 = 218.16
13 x 11.09 = 144.17
____________________
total      = 362.33

 

credit note total = 362.33

invoice balance = 362.27

 

And now I'm not sure how to proceed.

Is there any other way to do this or is there a continuation step that i can do to get the credit note total to match the invoice balance?

[Raytsou]

I don't know anything about Laravel php, but this is a rounding error (362.27/662.27 = 18.1772269618) != 18.18. Instead of doing 12*18.18, just calculate 12*33.23*362.27/662.27 = 218.126723542 != 218.16.
If you can't, I would try doing all the multiplying first then dividing at the last step to minimize error.

[halfass]

1 hour ago, Raytsou said:

I don't know anything about Laravel php, but this is a rounding error (362.27/662.27 = 18.1772269618) != 18.18. Instead of doing 12*18.18, just calculate 12*33.23*362.27/662.27 = 218.126723542 != 218.16.
If you can't, I would try doing all the multiplying first then dividing at the last step to minimize error.

 

Sorry. It totally escape my mind to put down the limitation.

 

3 hours ago, halfass said:

/*implementation of new price in credit note*/
12 x 18.18 = 218.16
13 x 11.09 = 144.17
____________________
total      = 362.33

 

This part will be shown to the user. Can't exactly show 18.1772269618. Plus, this calculation is made in JS. Vue js to be more precise. And I am not allowed to touch this part. Also, the price will be stored in database. The limitation of the database is the price will be stored only up to 2 decimal places.

[JacobFW]

11 hours ago, halfass said:

...

This is a well known problem with handling currency in coding.  Floating point arithmetic was designed to be approximate, not necessarily exact, which is part of why FP arithmetic can be done so quickly.  Currency however, must be precise.

 

One thing you can do is multiply everything by a fixed offset, so that the smallest decimal value is in the one's place

i.e.

362.27 * 100 = 36227

 

For addition and subtraction, it won't make any difference, but for multiplication and division, your result will have an extra factor of 100 you'll need to remove to have everything offset correctly again.

 

Google "currency in coding" and you'll find some tips for how to handle it.

You might also google "currency in php".  I imagine someone has encountered this problem and have some library to already handle it.

 

Best of luck.

 

[Trinopoty]

Maybe trying BCMath library for arbitrary precision handling or use a smaller unit like cents as @JacobFWsuggested.

Never use floating point for currency unless you want people to lose their money.

[halfass]

Hi. Sorry for the VERY late reply. Thanks Erik for the algo. I had to adjust the algo a little to fit my usecase. It helped a lot.

 

Turns out that the superior that gave me this task want me to learn that not everything that is requested can be achieved without any compromise. I had to convince people to make some compromise in the limitation given.

 

For example, the limitation is that only the unit price can be changed. I had them compromised on being able to add new item to tackle the problem of having balance that cannot fit into any of the items.

 

As for the currency in coding suggestion. Yes i am very aware of it. It was just that i had the problem of handling the leftover balance resulting from the limitation of 2 decimal places of currency that was in my algorithm. But thank you for the suggestion.

 

Thank you everyone for helping me out on this one.

[straight_stewie]

5 hours ago, halfass said:

Turns out that the superior that gave me this task want me to learn that not everything that is requested can be achieved without any compromise

@halfass

 

hmmmm.....

 

This is only true if one is unwilling to dedicate all the resources necessary to truly solve the problem. For all decideable problems, there exists a no compromise solution. Sometimes it just requires a lot to get there. Compromise can always be replaced by work.

 

Of course, I could be wrong. This is the belief that drove Steve Jobs crazy.

[Mijnay]

On 2/13/2020 at 4:11 AM, straight_stewie said:

On 2/12/2020 at 10:57 PM, halfass said:

Turns out that the superior that gave me this task want me to learn that not everything that is requested can be achieved without any compromise

 

This is only true if one is unwilling to dedicate all the resources necessary to truly solve the problem. For all decideable problems, there exists a no compromise solution. Sometimes it just requires a lot to get there. Compromise can always be replaced by work.

@straight_stewie

 

I believe in this example the original constraints made the problem impossible, so I am not sure why you bring up the idea of a no compromise solution.

 

If you are able to change an item's price in increments of 0.01 and are not allowed to change item counts, the problem is impossible in general. Proof by example:

count x price
1000 x 0.01 = 10.00
-------------------
      total = 10.00

Payment = 5.00

To account for the payment the item's price would have to be lowered. The price can be lowered to 0.00, however that price is to low to account for the payment. Since the price 0.01 is too high and 0.00 is too low and there is no possible price in between, the price cannot be adjusted to account for the payment.

[straight_stewie]

3 hours ago, Mijnay said:

If you are able to change an item's price in increments of 0.01

This is why I said "This is only true if one is unwilling to dedicate all the resources necessary to truly solve the problem".

Why can you only change an items price in increments of 0.01?

That's the problem that needs to be solved in order to reach a no compromise solution.

In this case the solution lies in a more thorough investigation into how to handle rounding errors in financial software.

Two general approaches are possible, calculate the actual solution, and then round towards the customers benefit, or calculate the actual solution and then round towards the businesses benefit. In some countries, or in some special cases in a given country, there may be law regulating how this is to be done.

But even then, that's a compromise in and of itself.

The real solution will require an extreme amount of financial resources to pull off: The system of money in question needs to be reworked to account for much more finely grained amounts of money.

So again, I conjecture: For all decideable problems, there exists a perfect solution with no compromises. Whether it's worth building that solution or not is not relevant to the conjecture.