Rounding Algorithm

[CC_Pet]

Hi, need some help trying to figure out a logic to do rounding. So what I would have would be 6 variables with whatever amount.

1. Divide each variable by 1000
2. Use math floor on all of them
3. Calculate the difference between the total before rounding and after
4. The remaining total (rounded to nearest whole) will then be distributed to the variables with the highest decimal

The main issue for me is after getting the decimals, sorting them by largest to smallest and then adding 1 value to each in descending order until the total difference reaches 0.

Visual Representation (No actual table to read, just showing how it would look like):

[Yoichi]

Hi,

Can you try the following?

arrOrignal = {1056468.79D,1425.71D,117573784.87D,33613280.06D,132123.78D,5123983.12D}
arrDivieded1000 = arrOriginal.Select(Function(d) d/1000).ToArray
arrDivieded1000Rounded = arrDivieded1000.Select(Function(d) Math.Floor(d)).ToArray
arrDiff = arrDivieded1000.Zip(arrDivieded1000Rounded,Function(d1,d2) d1-d2).ToArray()
arrAdd = arrDiff.Select(Function(d,i) Tuple.Create(d,i)).OrderBy(Function(t) t.Item1).Select(Function(t,i) if(i<arrDiff.Count/2,Tuple.Create(0D,t.item2),Tuple.Create(1D,t.item2))).OrderBy(Function(t) t.Item2).Select(Function(t) t.Item1).ToArray

arrReult = arrDivieded1000Rounded.Zip(arrAdd,Function(d1,d2) d1+d2).ToArray()

Note : variable type is Decimal or array of Decimal.
Number of items should be even.

Main.xaml (7.7 KB)

Regards,

[CC_Pet]

Hi Yoichi,

Are the “D” at the end of each number needed? What I am planning to do is to store decimal variables in arrOriginal instead of hardcoded values.

[Yoichi]

Hi,

If we handle these variables as Decimal, D is needed because it means Decimal type literal.
If we handle these variable as Double, it’s ok just numeric without any alphabet.

The following is FYI.

Constant and Literal Data Types - Visual Basic

Regards,

[CC_Pet]

Thanks for the help, is there a way to know which items were rounded? I want to write each element in the array using write cell and also highlight the items that had the value added to it.

[Yoichi]

Hi,

We can know it to check if “Original - Round down” (arrDiff) equals 0 or not. Is this same as your intent? (It seems they are mostly rounded.)

Regards,

[CC_Pet]

Yeah, I will try to think of a logic so that the bot can refer to arrDiff and maybe do a check on the indexes of arrResult vs arrDiff. If the element in arrDiff at that specific index = 1, then it can highlight the cell.

Also, I was looking through the algorithm in debug mode and realized its not very accurate.
Ex:
Bot:

Manual:

In the bot working above, you can see that it adds 3 numbers instead of 2. The remaining difference between the original - rounded = 2.37 which would then use standard round function to get 2.

[Yoichi]

Hi,

Sorry, I had misunderstanding your requirement. The following will work as you expect.

arrAdd = arrDiff.Select(Function(d,i) Tuple.Create(d,i)).OrderByDescending(Function(t) t.Item1).Select(Function(t,i) if(i<=arrDiff.Sum-1,Tuple.Create(1D,t.item2),Tuple.Create(0D,t.item2))).OrderBy(Function(t) t.Item2).Select(Function(t) t.Item1).ToArray

Main.xaml (9.5 KB)

And we can know which number should be highlighted checking value of arrDiff or arrAdd.
(Former is 0 or not , latter is 1 or 0)

Regards,