Rounding Error with Decimal Sum

Questions / Help
1

Hi.

Please I’m getting this unusual rounding error when summing numbers using the Decimal Library

My result is off by 0.01

  208,505,442,949.08 # Excel
  208,505,442,949.09 # sum function with Decimal

Any suggestions please?

Thanks.

def sum() do
    f = "C:/Downloads/MMR100.txt"
    {:ok, b} = :file.read_file(f)
    a = String.split(b,"\n")
    c = Enum.count(a)
    IO.puts c # 299,725

    _sum(a, "0.00") |> Decimal.to_string(:normal)

    # 208,505,442,949.08 - Excel
    # 208,505,442,949.09 - sum
  end

  defp _sum([], acc), do: acc
  defp _sum(["" | rest], acc), do: _sum(rest, Decimal.add("0", acc))
  defp _sum([v | rest], acc), do: _sum(rest, Decimal.add("#{v}", acc))
2

Afaik excel does use floats, so maybe it’s excel, which is wrong.

3

all my values are 2-decimal places
The Validator Program (some proprietor java program) also tells me the sum is wrong:

MMR100: Total is not equal to sum of all monthly balance for scheme…

When i manually adjust the sum to that excel value and feed it to the validator … it validates

4

Parsing and summing the list as float gives: 208505442949.07806, rounding this to 2 decimal places returns 208505442949.08, so obviously Excel and your verification program do use floats and therefore are inaccurate by design…

PS: I’ve heard there are still people using Excel to manage their money…

PPS: my iex session:

iex(3)> File.read!("MMR100.txt") |> String.split() |> Enum.map(&String.to_float/1) |> Enum.sum
208505442949.07806
iex(4)> File.read!("MMR100.txt") |> String.split() |> Enum.map(&String.to_float/1) |> Enum.sum |> Float.round(2)
208505442949.08
3 Likes
5

All the numbers in that list are 2-dp, how can you get 208505442949.07806 ???

6

The magic of floats.

http://0.30000000000000004.com/

4 Likes
8

This exactly is the reason, why you’d even need a library like Decimal. Because floats are not suited for calculating correctly.

2 Likes
10

Every single finance department i have dealt with keeps parallel records in excel.

For some some reason there is an innate distrust of records from IT systems

11

No, I do get 9:

iex()> File.read!("MMR100.txt") |> String.split() |> Enum.map(&String.graphemes/1) |> Enum.map(&List.last/1) |> Enum.map(&String.to_integer/1) |> Enum.sum
1349409
2 Likes
12
2 Likes
13

Bah. I only looked at the text in the post. Did not notice the “this file has been truncated”…

14

That’s horrifying… They really need to be taught how very very VERY bad that is…

If they want to use a Microsoft product then they should use Access instead of Excel, it can do everything Excel can except it has types, like a Decimal type, that does things right for such numbers…

1 Like