# Rounding Error with Decimal Sum

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
``````

Thanks.

``````def sum() do
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))
``````

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

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

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

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

The magic of floats.

http://0.30000000000000004.com/

4 Likes

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

2 Likes

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

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
2 Likes

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

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