Round several values to multiples of 100 and round the sum of all the values to 50000

[manojrf]

cash chest test new 10.1.16.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Currency	Pieces	Value	Result	Balance
2	100	1047	104700	104700	0	7			1000	1500	1047	1047	0
3	50	831	41550	41500	50	1	50		830	3000	2924	830	104700
4	20	124	2480	2400	80	4	80	120		7500	7430	120	146200
5	10	140	1400	1400	0	0	0	140	140	15000	15000	140	148600
6	Total		150130	150000	130								150000
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=A2*B2
D2:D5	D2	=A2*L2
E2:E5	E2	=C2-D2
F2:F5	F2	=RIGHT(B2,1)
J2	J2	=M6/100
K2:K5	K2	=M3/A2
L2	L2	=MIN(K2,IF(B2="",0,B2))
J3	J3	=M6/50
L3	L3	=MIN(K3,B3,I3)
J4	J4	=M6/20
L4:L5	L4	=IF($G$5+(RIGHT($G$4,2)*1)=(RIGHT($G$3,3)*1),B4,MIN((M5/A4),I4,H4))
G3:G5	G3	=A3*F3
I2	I2	=FLOOR(B2,100)
I3,I5	I3	=FLOOR(B3,2)
H4	H4	=FLOOR(B4,5)
H5	H5	=FLOOR(B5,10)
J5	J5	=M6/10
C6:E6	C6	=SUM(C2:C5)
M2:M5	M2	=M3-D2
M6	M6	=FLOOR(C6,50000)

Hello everyone,

I hope everybody is keeping fine.

As shown in the table above, the value in column "Pieces" changes always. Whatever the total value or pieces be, what I have tried to do is round the value in column "Value". But the value in "D6" should be rounded to 50000 always.
I have tried my best to use my brain and formulas to do this. Is there anyone who could find an easier way to do this, either a formula or a macro?
Please try. Thanks in advance

 

[KRice]

In D6, does this give the result you want:
=FLOOR(SUMPRODUCT($A$2:$A$5,$B$2:$B$5),50000)
This preserves the other values in the col C & D and relies only on the original values in cols A & B.

 

[manojrf]

Thanks KRice for spending your precious time for me.

cash chest test new 12.06.2020.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Currency	Pieces	Value		Pieces	Result		Pieces	Balance
2	100	1255	125500		1002	100200		253	25300	5			1200	1500	1002	1002	0
3	50	925	46250		924	46200		1	50	5	250		924	3000	2928	924	100200
4	20	124	2480		120	2400		4	80	4	80	120		7500	7440	120	146400
5	10	127	1270		120	1200		7	70	7	70	120	126	15000	15000	120	148800
6	Total		175500			150000			25500								150000
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=RIGHT(B2,1)
N2	N2	=Q6/100
O2:O5	O2	=Q3/A2
P2	P2	=MIN(O2,IF(B2="",0,B2))
N3	N3	=Q6/50
P3	P3	=MIN(O3,B3,M3)
N4	N4	=Q6/20
P4:P5	P4	=IF($K$5+(RIGHT($K$4,2)*1)=(RIGHT($K$3,3)*1),B4,MIN((Q5/A4),M4,L4))
K3:K5	K3	=A3*J3
M2	M2	=FLOOR(B2,100)
M3,M5	M3	=FLOOR(B3,2)
L4	L4	=FLOOR(B4,5)
E2:E5	E2	=F2/A2
H2:H5	H2	=I2/A2
L5	L5	=FLOOR(B5,10)
N5	N5	=Q6/10
C2:C5	C2	=A2*B2
C6,I6,F6	C6	=SUM(C2:C5)
F2:F5	F2	=A2*P2
I2:I5	I2	=C2-F2
Q2:Q5	Q2	=Q3-F2
Q6	Q6	=FLOOR(C6,50000)

My question would have been a bit confusing.
What I am trying to do is to take out the right number of pieces from column "B", keeping their values rounded to 100, put it in column "E" and all in all keeping the total sum rounded to 50000, in F6.
Here in the given table, 7 is taken out from B5, 4 from B4, 1 from B3 and 253 from B2. From column J to Q, I have some formulas to make this work automatically.
I was wondering whether there is an easy way to do this.

Thanks again.

 

[KRice]

Try something like this:

Book1
	A	B	C	D	E	F	G	H	I
1	Currency	Pieces	Value		Pieces	Result		Pieces	Balance
2	100	1255	125500		1002	100200		253	25300
3	50	925	46250		924	46200		1	50
4	20	124	2480		120	2400		4	80
5	10	127	1270		120	1200		7	70
6	Total		175500			150000			25500
Sheet2 (2)

Cell Formulas
Range	Formula
E2:E5	E2	=B2-H2
H2:H5	H2	=I2/A2
C2:C5	C2	=A2*B2
C6,I6	C6	=SUM(C2:C5)
F2	F2	=E2*A2
F3:F5	F3	=FLOOR(C3,100)
F6	F6	=FLOOR(SUMPRODUCT($A$2:$A$5,$B$2:$B$5),50000)
I2	I2	=C6-F6-SUM(I3:I5)
I3:I5	I3	=C3-F3

This rounds down to the nearest 100 in column F (except for the 100's currency) and then uses that value to establish the allocation of the total value for that row between columns E-F and H-I. SUMPRODUCT is used in F6 with FLOOR to round down the grand total to the nearest 50000. Then any residual is accounted for when making the allocation for the 100's currency.

 

[manojrf]

Once again thanks a lot. Your plan works out fine.
But what if I add three more currencies as shown in the table.

cash chest test new 12.06.2020.xls
	A	B	C	D	E	F	G	H	I
1	Currency	Pieces	Value		Pieces	Result		Pieces	Balance
2	2000	122	244000		113	226000		9	18000
3	500	4515	2257500		4500	2257500		15	7500
4	200	5589	1117800		5500	1100000		89	17800
5	100	1241	124100		1236	124100		5	500
6	50	927	46350		926	46300		1	50
7	20	126	2520		125	2500		1	20
8	10	162	1620		160	1600		2	20
9	Total		3793890			3750000			43890
Sheet2

Cell Formulas
Range	Formula
I3:I4	I3	=A3*H3
E2:E8	E2	=B2-H2
H6:H8	H6	=I6/A6
C2:C8	C2	=A2*B2
C9,I9	C9	=SUM(C2:C8)
F2,F4	F2	=E2*A2
F3	F3	=FLOOR(C3,500)
F5:F8	F5	=FLOOR(C5,100)
F9	F9	=FLOOR(SUMPRODUCT($A$2:$A$8,$B$2:$B$8),50000)
I6:I8	I6	=C6-F6

Please share your idea.

Sorry to bother you again

Thanks.

 

[KRice]

I don't think there is a unique way to do this, but if we apply the same rule set that I described earlier, then it can be extended to the higher value currencies as shown here. With this approach, since you want (I think) all totals of every currency rounded down to the nearest 100, we apply that rule everywhere, even for the larger currencies. Note that all higher level currencies that end in "00" do not actually need to be rounded since they are already some multiple of 100 (but this approach retains the rounding formula for them anyway to preserve the same approach on all rows except for the 100's currency). The only exception to the rounding rule is for the 100's currency, since the rounding errors that accumulate will have to be offset there. The two formulas that make up this offset are shaded in orange on the 100's row. And the same rule set that determines the allocation of pieces for all other currencies remains the same. This means that in the Balance for the 100's currency, the sum of the balances of all removed pieces for larger and smaller currencies is taken, and then subtracted from the difference between the un-rounded grand total value (in C9) and the rounded grand total value (downward to the nearest 50000) in F9. It is somewhat arbitrary how this correction can be made. You could, for example, choose to make up the total balance (the amount shown in I9) by applying a different rule set for rounding the higher level currencies to some other value. Please let me know if this is what you had in mind, or if you wanted to apply a different set of rules for higher currencies.

20200613_Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Currency	Pieces	Value		Pieces	Result		Pieces	Balance
2	2000	122	244000		122	244000		0	0
3	500	4515	2257500		4515	2257500		0	0
4	200	5589	1117800		5589	1117800		0	0
5	100	1241	124100		803	80300		438	43800
6	50	927	46350		926	46300		1	50
7	20	126	2520		125	2500		1	20
8	10	162	1620		160	1600		2	20
9	Total		3793890			3750000			43890
10					check -->	3750000
Sheet6

Cell Formulas
Range	Formula
E2:E8	E2	=B2-H2
H2:H8	H2	=I2/A2
C2:C8	C2	=A2*B2
C9,I9	C9	=SUM(C2:C8)
I6:I8,I2:I4	I2	=C2-F2
I5	I5	=C9-F9-SUM(I2:I4,I6:I8)
F6:F8,F2:F4	F2	=FLOOR(C2,100)
F5	F5	=E5*A5
F9	F9	=FLOOR(SUMPRODUCT($A$2:$A$8,$B$2:$B$8),50000)
F10	F10	=SUM(F2:F8)

 

[manojrf]

Once again, thanks a lot.

This works out fine. What I am trying to do is this.

Instead of removing 100's for the entire balance amount (in this case 43800), I am trying to distribute this amount to 200's,500's and 2000's, only if needed. In this case, we can remove 40 of 100, 289 of 200 and 4 of 500 to make this amount (43800). Because, if we rely the whole amount on 100's, in some cases the value in E5 may become below zero, with a minus sign. In the above table, if we put 302 in B5, E5 becomes -197.

When number of pieces in column B changes, the distribution will change.

I assume that you understand what I mean to do.

Thanks once again.

 

[KRice]

Yes, I see your point. Do you have some rule set in mind that would give the preferred solution? In most cases, as I mentioned earlier, there is not a unique solution. Let's take your example...

In this case, we can remove 40 of 100, 289 of 200 and 4 of 500 to make this amount (43800).

I assume you meant 189 rather than 289 of the 200's currency. But there are many other approaches too....we could also remove 38 of 100, 190 of 200 and 4 of 500 to make up the same difference of 43800 due to rounding. The point is that there are dozens of other possibilities. Obviously one constraint is that we cannot remove more than is available, but what else would guide your thinking?

Here is a revised version that illustrates an issue introduced by the higher value currencies and all of the other constraints or objectives: 1) round down so that each currency retains a total value of some multiple of 100, 2) the total value of all retained currencies should be rounded down to the nearest 50000, and 3) most of the difference should be made up with the 100's currency. This approach comes close to satisfying these objectives, but it doesn't rigorously satisfy all of them. For example, here the algorithm leaves 400 more than desired in the retained section and falls 400 short in the removed section. Even here, one could remove one more unit of 500 (since it is available) and then put 1 unit of 100 back, or instead remove 8 more units of 50, or remove 20 more units of 20, and many more combinations. A second step would be needed to address this small imbalance, but additional rules would be needed to describe the priorities. On the other hand, this may be close enough for your purposes?

20200613_Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Currency	Pieces	Value		Pieces	Result		Pieces	Balance
2	2000	22	44000		22	44000		0	0
3	500	15	7500		12	6000		3	1500
4	200	33	6600		0	0		33	6600
5	100	200	20000		0	0		200	20000
6	50	927	46350		926	46300		1	50
7	20	126	2520		125	2500		1	20
8	10	162	1620		160	1600		2	20
9	Total		128590			100400			28190
10					target -->	100000		target -->	28590
Sheet6

Cell Formulas
Range	Formula
E2:E8	E2	=B2-H2
H2:H5	H2	=MIN(INT(($I$10-SUMPRODUCT(H3:H$8,A3:A$8)) /A2),B2)
H6:H8	H6	=(C6-FLOOR(C6,100))/A6
C2:C8	C2	=A2*B2
C9,I9,F9	C9	=SUM(C2:C8)
F2:F8	F2	=E2*A2
F10	F10	=FLOOR(SUMPRODUCT($A$2:$A$8,$B$2:$B$8),50000)
I2:I8	I2	=H2*A2
I10	I10	=C9-F10