Preserving pre-rounding sum

1

I am trying to find an optimal way of changing a column. For strange finance requirements, I am meant to perform a calculation that rounds values from a sum that originally is 1 from 9 to 5 digits. However, when rounding, I lose precision so the final sum ends at 0.9997.

I am thinking on using the the difference after and before rounding and distributing it across all. However, this might be misleading

I know it is a long shot but maybe someone has done something similar in the past and knows of a better way?

2

Can you provide an example of a correct calculation? Input data and the result

3

Example Pre-rounding sum.xlsx (10.7 KB)

This worksheet has the basics of the calculation. Calculating the share of a total, rounding the results and adjusting the share so it makes up to 1 again. This is a way of going about it but not the most efficient. As you will see from my calculation I just attribute the missing precision to the highest difference between actuals and rounded

4

An example of an R implementation: Round values while preserve their rounded sum in R — round_preserve_sum • JLutils

5

Here is a slightly improved implementation of your algorithm.

Improvement: since it's using rounding that works both ways, up and down, the max difference should be defined by max absolute difference, not just by max value. For instance, in your sample data, the absolute difference for product "K" is larger than the absolute difference for product "O". So it would be more correct to adjust the share of product "K", not of product "O" as it was in your algorithm.

precision-rounding.morph (5.8 KB)

I didn't understand how it works.

6

Great! Thank you for this Dmitry. Sometimes I find it hard to translate excel into EasyMorph but this is far more efficient