How to evenly spread 97 input values across 100 output values

[rpg1966]

I have 97 input values that I need to spread over 100 output values, such that the total remains the same. In other words, each output value will be a 97/100th chunk of the relevant input values.

The image below shows a simplified example with 3 input values and 4 output values, i.e. each output value is a 3/4 chunk of one or two of the input values:

- the 1st output value is just 3/4 of the first input value
- the 2nd output value is the remaining 1/4 of the first input value, plus 1/2 of the next (second) input value
- the 3rd output value is the remaining 1/2 of the second input value, plus 1/4 of the next (third) input value
- the 4th output value is the remaining 3/4 of the third input value, plus ... nothing, as we have now completed the table

I'm not sure if this should be trivially simple? But I can't think of a way to automate it; does anyone have any bright ideas?

 

[rpg1966]

I seem that we are trying to solve an unknown problem, and we know only how the OP thought "it" could be solved.

Could the objective of the work be described, forgetting how it could be done?

I'm not sure I understand your point. I have described the objective, i.e. splitting 97 input cells over 100 output cells.

I could do it manually, as described laboriously a few posts above, but next time it might be 96 input cells and 100 output cells. If there was an algorithmic way to do it, it would save a lot of time over manually typing in the splits into each output cell.

 

[rpg1966]

Can this work instead? It doesn't match your values, but it does keep the total sum.

Thanks, I'll have to have a play with it, and see how close it gets to the desired result.

 

[johnnyL]

@rpg1966 here is an XL2BB example I whipped up that has a few examples that seem to work. It also includes a partial portion of the 97/100.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW
1	Input Value 1	Input Value 2	Input Value 3				Sum			# of Input values	# of Output values
2	12	10	14				36			3	4	0.75	0.25
3	Output Value 1	Output Value 2	Output Value 3	Output Value 4
4	9	8	8.5	10.5			36
5
6	Input Value 1	Input Value 2	Input Value 3	Input Value 4
7	12	10	14	16			52			4	5	0.8	0.2
8	Output Value 1	Output Value 2	Output Value 3	Output Value 4	Output Value 5
9	9.6	8.4	9.6	11.6	12.8		52
10
11	Input Value 1	Input Value 2	Input Value 3	Input Value 4	Input Value 5
12	12	10	14	16	18		70			5	6	0.833333333	0.166666667
13	Output Value 1	Output Value 2	Output Value 3	Output Value 4	Output Value 5	Output Value 6
14	10	8.666666667	10.33333333	12.33333333	13.66666667	15	70
15
16
17
18
19	# of Input values	# of Output values
20	97	100	0.97	0.03
21	Input Value 1	Input Value 2	Input Value 3	Input Value 4	Input Value 5	Input Value 6	Input Value 7	Input Value 8	Input Value 9	Input Value 10	Input Value 11	Input Value 12	Input Value 13	Input Value 14	Input Value 15	Input Value 16	Input Value 17	Input Value 18	Input Value 19	Input Value 20	Input Value 21	Input Value 22	Input Value 23	Input Value 24	Input Value 25	Input Value 26	Input Value 27	Input Value 28	Input Value 29	Input Value 30	Input Value 31	Input Value 32	Input Value 33	Input Value 34	Input Value 35	Input Value 36	Input Value 37	Input Value 38	Input Value 39	Input Value 40	Input Value 41	Input Value 42	Input Value 43	Input Value 44	Input Value 45	Input Value 46	Input Value 47	Input Value 48	Input Value 49	Input Value 50	Input Value 51	Input Value 52	Input Value 53	Input Value 54	Input Value 55	Input Value 56	Input Value 57	Input Value 58	Input Value 59	Input Value 60	Input Value 61	Input Value 62	Input Value 63	Input Value 64	Input Value 65	Input Value 66	Input Value 67	Input Value 68	Input Value 69	Input Value 70	Input Value 71	Input Value 72	Input Value 73	Input Value 74	Input Value 75	Input Value 76	Input Value 77	Input Value 78	Input Value 79	Input Value 80	Input Value 81	Input Value 82	Input Value 83	Input Value 84	Input Value 85	Input Value 86	Input Value 87	Input Value 88	Input Value 89	Input Value 90	Input Value 91	Input Value 92	Input Value 93	Input Value 94	Input Value 95	Input Value 96	Input Value 97
22	2	4	6	8	10	12	14	16	18	20	22	24	26	28	30	32	34	36	38	40	42	44	46	48	50	52	54	56	58	60	62	64	66	68	70	72	74	76	78	80	82	84	86	88	90	92	94	96	98	100	102	104	106	108	110	112	114	116	118	120	122	124	126	128	130	132	134	136	138	140	142	144	146	148	150	152	154	156	158	160	162	164	166	168	170	172	174	176	178	180	182	184	186	188	190	192	194
23	Output Value 1	Output Value 2	Output Value 3	Output Value 4	Output Value 5	Output Value 6	Output Value 7	Output Value 8	Output Value 9	Output Value 10	Output Value 11	Output Value 12	Output Value 13	Output Value 14	Output Value 15	Output Value 16	Output Value 17	Output Value 18	Output Value 19	Output Value 20	Output Value 21	Output Value 22	Output Value 23	Output Value 24	Output Value 25	Output Value 26	Output Value 27	Output Value 28	Output Value 29	Output Value 30	Output Value 31	Output Value 32	Output Value 33	Output Value 34	Output Value 35	Output Value 36	Output Value 37	Output Value 38	Output Value 39	Output Value 40	Output Value 41	Output Value 42	Output Value 43	Output Value 44	Output Value 45	Output Value 46	Output Value 47	Output Value 48	Output Value 49	Output Value 50	Output Value 51	Output Value 52	Output Value 53	Output Value 54	Output Value 55	Output Value 56	Output Value 57	Output Value 58	Output Value 59	Output Value 60	Output Value 61	Output Value 62	Output Value 63	Output Value 64	Output Value 65	Output Value 66	Output Value 67	Output Value 68	Output Value 69	Output Value 70	Output Value 71	Output Value 72	Output Value 73	Output Value 74	Output Value 75	Output Value 76	Output Value 77	Output Value 78	Output Value 79	Output Value 80	Output Value 81	Output Value 82	Output Value 83	Output Value 84	Output Value 85	Output Value 86	Output Value 87	Output Value 88	Output Value 89	Output Value 90	Output Value 91	Output Value 92	Output Value 93	Output Value 94	Output Value 95	Output Value 96	Output Value 97	Output Value 98	Output Value 99	Output Value 100
24	1.94	3.82	5.7	7.58	9.46	11.34	13.22	15.1	16.98	18.86	20.74	22.62	24.5	26.38	28.26	30.14	32.02	33.9	35.78	37.66	39.54	41.42	43.3	45.18	47.06	48.94	50.82	52.7	54.58	56.46	58.34	60.22	62.1
25
Sheet1

Cell Formulas
Range	Formula
G2	G2	=SUM($A2:$C2)
J2,J12	J2	=LOOKUP(2, 1 / (A2:F2 <> ""), COLUMN(A2:F2))
L2,C20,L12	L2	= J2 / K2
M2,D20,M12,M7	M2	= 1 - J2 / K2
A4,A14,A9	A4	=A2 * L2
B4:C4	B4	=A2 * $M2 * (COUNT($A2:B2) - 1) + B2 * ($L2 - (COUNT($A$2:B2) - 1) * $M2)
D4	D4	=C2 * L2
G4	G4	=SUM(A4:D4)
G7	G7	=SUM($A7:$D7)
J7	J7	=LOOKUP(2, 1 / (A7:F7<>""), COLUMN(A7:F7))
L7	L7	= J7 / K7
B9:D9	B9	=A7 * $M7 * (COUNT($A7:B7) - 1) + B7 * ($L7 - (COUNT($A$7:B7) - 1) * $M7)
E9	E9	=D7 * L7
G9	G9	=SUM(A9:E9)
G12	G12	=SUM($A12:$E12)
B14:E14	B14	=A12 * $M12 * (COUNT($A12:B12) - 1) + B12 * ($L12 - (COUNT($A$12:B12) - 1) * $M12)
F14	F14	= E12 * L12
G14	G14	=SUM(A14:F14)
A20	A20	=LOOKUP(2, 1 / (A22:FF22 <> ""), COLUMN(A22:FF22))
B20	B20	=LOOKUP(2, 1 / (A23:FF23 <> ""), COLUMN(A23:FF23))
A24	A24	=A22 * C20
B24:AG24	B24	=A22 * $D20 * (COUNT($A22:B22) - 1) + B22 * ($C20 - (COUNT($A$22:B22) - 1) * $D20)

The 97/100 part is filled in up to the 33rd output value of the sample data used. Perhaps you can fill in the next few output values so we can figure out what you are trying to accomplish.

I used the formula that @kevin9999 supplied, cleaned up a bit, because it was much simpler than what I came up with.

 

[rpg1966]

Thank you johnny.

That doesn't quite do what I'm looking for, as described in posts #1, #3, and especially #14. Post #14 shows how the nature of only taking a 97/100th chunk of each input cell means that eventually (in this case, at the 34th and 35th outputs) the output gets calculated from not the input cell with the same number and the one before, but the previous input cell and the one before that. For example, you can see that (say) the 20th output is calculated from the 19th and 20th input cells, but the 35th output is calculated from the 33rd and 34th input cells. The same shift will happen again at the ~66th/67th and ~98th/99th/100th cells, which is how the 100th output can take it's value from the 97th input (97/100ths of the 97th input, to be precise). However, the formulae you've provided for B24:AG24 only ever references the same input cell and the previous input cell. i.e. it doesn't handle the shift at the ~34th/35th outputs, and it can't handle the extra shift at the 66/67 and 98/99/100th outputs.

You (and Anthony47) ask if I can help you "figure out what you are trying to accomplish" / "describe the objective", but I can't see how I can explain it any better. Post #1 describes it in words, post #3 gives a diagrammatic explanation, and post #14 actually works through the first 35 outputs one-by-one; in particular, showing the shift that kevin999 can't seem to wrap his head around. It seems that I am not able to explain myself very well, sadly! Therefore, let's call this question solved, as I don't want to waste people's time with my inadequate explanation.

 

[Anthony47]

I shall read again the messages you pinpoint, maybe (maybe) I'll get a better idea of the question (probably Johnny is much ahead of me)

However I still think that taking "the first 97/100th of the first cell", then taking "the remaining 3/100th from the 1st cell, and therefore 94/100th of the 2nd input cell" is HOW you are trying to solve the puzzle; should it be possible to describe the rules that govern this redistribution maybe (maybe) we could think of a different approach

By the way: which Office version do you use?

 

[rpg1966]

I shall read again the messages you pinpoint, maybe (maybe) I'll get a better idea of the question (probably Johnny is much ahead of me)

However I still think that taking "the first 97/100th of the first cell", then taking "the remaining 3/100th from the 1st cell, and therefore 94/100th of the 2nd input cell" is HOW you are trying to solve the puzzle; should it be possible to describe the rules that govern this redistribution maybe (maybe) we could think of a different approach

By the way: which Office version do you use?

I have an Office 365 subscription; not sure what that means in terms of version numbers.
Edit: it says Version 2209 (Build 15629.20208)

I could explain why I am seeking this split of 97 inputs into 100 outputs (or maybe 96>100, or whatever combination in future), but it wouldn't change the requirement to split the cells as described. So I left the explanation out, as it's not relevant.

Thanks for checking in, but I think I'll just type in the calculations manually as per post #14, and hope that I don't often need to do this for other combinations of #inputs and #outputs!

 

[Anthony47]

Ok the version.
Is this puzzle based on a 97 value to be splitted over 100 output cells, or generally on N values to M cells?

In the second case, how 3 values should be splitted over 5 output cells?

 

[johnnyL]

I have 97 input values that I need to spread over 100 output values, such that the total remains the same. In other words, each output value will be a 97/100th chunk of the relevant input values.

If you just want to turn 97 input values into 100 output values, how about using the following:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT
1				Percentage of each		# of Output values that	Each Output value that
2	# of Input Values	# of Output Values		Input value to keep		need to be added	needs to be added
3	97	100		0.97		3	95.06
4
5	Input Values
6	2	4	6	8	10	12	14	16	18	20	22	24	26	28	30	32	34	36	38	40	42	44	46	48	50	52	54	56	58	60	62	64	66	68	70	72	74	76	78	80	82	84	86	88	90	92	94	96	98	100	102	104	106	108	110	112	114	116	118	120	122	124	126	128	130	132	134	136	138	140	142	144	146	148	150	152	154	156	158	160	162	164	166	168	170	172	174	176	178	180	182	184	186	188	190	192	194
7	Output Values
8	1.94	3.88	5.82	7.76	9.7	11.64	13.58	15.52	17.46	19.4	21.34	23.28	25.22	27.16	29.1	31.04	32.98	34.92	36.86	38.8	40.74	42.68	44.62	46.56	48.5	50.44	52.38	54.32	56.26	58.2	60.14	62.08	64.02	65.96	67.9	69.84	71.78	73.72	75.66	77.6	79.54	81.48	83.42	85.36	87.3	89.24	91.18	93.12	95.06	97	98.94	100.88	102.82	104.76	106.7	108.64	110.58	112.52	114.46	116.4	118.34	120.28	122.22	124.16	126.1	128.04	129.98	131.92	133.86	135.8	137.74	139.68	141.62	143.56	145.5	147.44	149.38	151.32	153.26	155.2	157.14	159.08	161.02	162.96	164.9	166.84	168.78	170.72	172.66	174.6	176.54	178.48	180.42	182.36	184.3	186.24	188.18
9
10	Sum of Input Values	Sum of Output Values
11	9506	9506
12
Sheet2

Cell Formulas
Range	Formula
A3	A3	=MATCH(TRUE, ISBLANK(A6:FF6), 0) - 1
D3	D3	= A3 / B3
F3	F3	= B3 - A3
G3	G3	=(A11 - SUM($A8:OFFSET(A8,0,MATCH(TRUE, ISBLANK(A6:FF6), 0) - 1))) / ($B3 - $A3)
A8:CS8	A8	= $D3 * A6
A11	A11	=SUM(A6:OFFSET(A6,0,MATCH(TRUE, ISBLANK(A6:FF6), 0) - 1))
B11	B11	=SUM(A8:OFFSET(A8,0,MATCH(TRUE, ISBLANK(A8:FF8), 0) - 1)) + F3 * G3
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sulprobil]

Hello,

For a general approach (N values to M cells) an interpolation approach is to be preferred, I think.

For this approach you can use any interpolation function which can spill:

MrExcel_How to evenly spread 97 input values across 100 output values.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX
1	Input	Sum	1	2	3
2		36	12	10	14
3	Output		1	2	3	4
4		36	9	8	8,5	10,5
5
6	Input		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75	76	77	78	79	80	81	82	83	84	85	86	87	88	89	90	91	92	93	94	95	96	97
7		9506	2	4	6	8	10	12	14	16	18	20	22	24	26	28	30	32	34	36	38	40	42	44	46	48	50	52	54	56	58	60	62	64	66	68	70	72	74	76	78	80	82	84	86	88	90	92	94	96	98	100	102	104	106	108	110	112	114	116	118	120	122	124	126	128	130	132	134	136	138	140	142	144	146	148	150	152	154	156	158	160	162	164	166	168	170	172	174	176	178	180	182	184	186	188	190	192	194
8	Output		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75	76	77	78	79	80	81	82	83	84	85	86	87	88	89	90	91	92	93	94	95	96	97	98	99	100
9		9506	1,94	3,82121212	5,70242424	7,58363636	9,46484848	11,3460606	13,2272727	15,1084848	16,989697	18,8709091	20,7521212	22,6333333	24,5145455	26,3957576	28,2769697	30,1581818	32,0393939	33,9206061	35,8018182	37,6830303	39,5642424	41,4454545	43,3266667	45,2078788	47,0890909	48,970303	50,8515152	52,7327273	54,6139394	56,4951515	58,3763636	60,2575758	62,1387879	64,02	65,9012121	67,7824242	69,6636364	71,5448485	73,4260606	75,3072727	77,1884848	79,069697	80,9509091	82,8321212	84,7133333	86,5945455	88,4757576	90,3569697	92,2381818	94,1193939	96,0006061	97,8818182	99,7630303	101,644242	103,525455	105,406667	107,287879	109,169091	111,050303	112,931515	114,812727	116,693939	118,575152	120,456364	122,337576	124,218788	126,1	127,981212	129,862424	131,743636	133,624848	135,506061	137,387273	139,268485	141,149697	143,030909	144,912121	146,793333	148,674545	150,555758	152,43697	154,318182	156,199394	158,080606	159,961818	161,84303	163,724242	165,605455	167,486667	169,367879	171,249091	173,130303	175,011515	176,892727	178,773939	180,655152	182,536364	184,417576	186,298788	188,18
Sheet1

Cell Formulas
Range	Formula
C1:E1	C1	=SEQUENCE(1,3,1,1)
B2	B2	=SUM(C2:E2)
C3:F3	C3	=SEQUENCE(1,4,1,1)
B4	B4	=SUM(C4:F4)
C4:F4	C4	=SUM(C2:E2)*sbInterp(C1:E1,C2:E2,SEQUENCE(1,4,C1,(E1-C1)/(4-1)))/SUM(sbInterp(C1:E1,C2:E2,SEQUENCE(1,4,C1,(E1-C1)/(4-1))))
C6:CU6	C6	=SEQUENCE(1,97,1,1)
B7	B7	=SUM(C7:CU7)
C8:CX8	C8	=SEQUENCE(1,100,1,1)
B9	B9	=SUM(C9:CX9)
C9:CX9	C9	=SUM(C7:CU7)*sbInterp(C6:CU6,C7:CU7,SEQUENCE(1,100,C6,(CU6-C6)/(100-1)))/SUM(sbInterp(C6:CU6,C7:CU7,SEQUENCE(1,100,C6,(CU6-C6)/(100-1))))
Dynamic array formulas.

A sample file (download and use at your own risk, but I use an up-to-date virus scanning program):

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Regards,
Bernd

 

[Anthony47]

Just for fun I went to this macro, that should split N values in a row to M cells according the OP algorithm; N cound be <, > or = to M

Sub SplitN2M()
Dim sStart As Range, dSPos As Range, oStart As Range
Dim sArr, qCnt() As Long, oArr()
Dim sSize As Long, tFrom As Long, dSize As Long, I As Long, J As Long
'
Set sStart = Range("B4")        '<<< Where the source range starts from
Set oStart = Range("B7")        '<<< The output starting position
Set dSPos = Range("B10")        '<<< The output size
'
sSize = Application.WorksheetFunction.CountA(sStart.Resize(1, 110))
dSize = dSPos.Value
sArr = sStart.Resize(1, sSize + 1).Value
ReDim qCnt(1 To UBound(sArr, 2))
ReDim oArr(1 To dSize + 3)
oStart.Resize(1, 115).ClearContents
'
tFrom = 1
For J = 1 To dSize
    For I = 1 To sSize
        If qCnt(tFrom) < dSize Then
            oArr(J) = oArr(J) + sArr(1, tFrom) / dSize
            qCnt(tFrom) = qCnt(tFrom) + 1
        Else
            tFrom = tFrom + 1
            oArr(J) = oArr(J) + sArr(1, tFrom) / dSize
            qCnt(tFrom) = qCnt(tFrom) + 1
            If qCnt(tFrom) > dSize Then Stop    'Never?
        End If
    Next I
Next J
oStart.Resize(1, UBound(oArr)).Value = oArr
'Range("H20").Resize(1, UBound(qCnt) + 1) = qCnt
Beep
End Sub

The lines marked <<< need to be customized according the comment

I used it on the attached BB2XL sheet:

BR1 Sales_Ledger RECON(9.2 (version 1).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2
3
4	Source Serie	587	51	692	769	912
5
6		1	2	3	4	5	6	7	8	9	10	11
7	Dest Serie	266,818	266,818	71,909	23,182	198,000	314,545	328,545	349,545	362,545	414,545	414,545
8
9	Source Vals	5
10	Dest Cells	11
11
12
13
Foglio8

Cell Formulas
Range	Formula
C6:N6	C6	=IF(B6<>"",IF(B6+1<=$B$10,B6+1,""),"")
B9	B9	=COUNTA(B4:DZ4)

Then I used Worksheet_Change to start automatically the macro when the parametres changes (Source serie, Destination cells):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" Or Target.Row = 4 Then
    Application.EnableEvents = False
    Call SplitN2M
    Application.EnableEvents = True
End If
End Sub