Optimization with horizontal and vertical checks

[daryllj]

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:

		NetValue	BookValue	GainLoss
	Original	4250.00	3000.00	1250.00
	Ratio				Check
A	11.80%	502.00	354.00	148.00	0.00
B	21.00%	893.00	630.00	263.00	0.00
C	0.00%	0.00	0.00	0.00	0.00
D	26.20%	1114.00	786.00	328.00	0.00
E	41.00%	1743.00	1230.00	513.00	0.00
	100.00%	4252.00	3000.00	1252.00
	Check	-2.00	0.00	-2.00

Desired output:

Ratio	NetValue	BookValue	GainLoss
11.80%	501.00	354.00	147.00
21.00%	892.00	630.00	262.00
0.00%	0.00	0.00	0.00
26.20%	1114.00	786.00	328.00
41.00%	1743.00	1230.00	513.00
100.00%	4250.00	3000.00	1250.00

[Yoshiro_Fujimori]

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.

Name	Ratio	NetValue	BookValue	GainLoss
A	11.80%	501.50	354.00	147.50
B	21.00%	892.50	630.00	262.50
C	0.00%	0.00	0.00	0.00
D	26.20%	1113.50	786.00	327.50
E	41.00%	1742.50	1230.00	512.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...

[daryllj]

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]

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

Name	Ratio	NetValue	BookValue	GainLoss
A	0.118	501	354	147
B	0.21	892	630	262
C	0	0	0	0
D	0.262	1114	786	328
E	0.41	1743	1230	513
Total		4250	3000	1250