Optimization with horizontal and vertical checks

Question

Hi, I'm currently working on a table that includes NetValue, BookValue, and GainLoss (where NV - BV = GL). I need to allocate these values according to a specific ratio, rounding to the nearest whole number.

The problem arises due to the total rounding of values, which will be manually adjusted by a maximum increment of +/- 1 to negate the variance from the original totals. Ideally, I will first check the rows for any variance, then adjust the columns. If there is variance in both, it's a good indicator that the intersected cell should be adjusted.

I'm exploring the Optimization tool but I'm struggling to determine the correct input values for each anchor to make this work. I will be using the Optimization tool to determine the appropriate adjustments.

Input:

NetValueBookValueGainLoss  Original4250.003000.001250.00        Ratio   CheckA11.80%502.00354.00148.000.00B21.00%893.00630.00263.000.00C0.00%0.000.000.000.00D26.20%1114.00786.00328.000.00E41.00%1743.001230.00513.000.00 100.00%4252.003000.001252.00  Check-2.000.00-2.00

Desired output:

RatioNetValueBookValueGainLoss11.80%501.00354.00147.0021.00%892.00630.00262.000.00%0.000.000.0026.20%1114.00786.00328.0041.00%1743.001230.00513.00100.00%4250.003000.001250.00

Yoshiro_Fujimori · Answer

Though the decision logic to select the rows to round up is not clear,

the attached workflow output the data mostly the same as you expect.

I hope this helps.

Workflow

Formula #1

[Value2] = [Ratio] * [Value]

[RoundDown] = FLOOR([Value2])

[Diff] = [Value2] - [RoundDown]

Formula #2

[Diff] = IF [Count] <= [Sum_Diff] THEN 1 ELSE 0 ENDIF

[Value2] = [RoundDown] + [Diff]

Output

NameRatioNetValueBookValueGainLossA0.118501354147B0.21892630262C0000D0.2621114786328E0.4117431230513Total 425030001250

workflow.yxmd

daryllj · Answer

Hi @Yoshiro_Fujimori ,

Yes, I think I presented my sample incorrectly. The significant values should be rounded to the nearest whole number only. You could eliminate the decimals instead. However, strictly adjust the allocation after checking for variance in the rounding totals.

Unfortunately, I need this optimization because multiple adjustments will need to be made on a daily basis.

Yoshiro_Fujimori · Answer

Hi @daryllj ,

When you show 2 decimal places, it normally means it is rounded to the nearest number to th real value with the precision of 0.01.

The calculated result of your sample data is as below.

If you want to show it with 2 decimal places, you can simply use ".50" with no problem.

NameRatioNetValueBookValueGainLossA11.80%501.50354.00147.50B21.00%892.50630.00262.50C0.00%0.000.000.00D26.20%1113.50786.00327.50E41.00%1742.501230.00512.50

Otherwise if you really want to show the number in Integer and still want to adjust the sum,

you would need to define conditions of when to round-up and when to round-down.

But it does not seem to be an essential problem to take a time...