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?

 

[Anthony47]

Not fully clear to me, but let me try...

Let's consider a list of "Totals" (in yellow in the attached XL2BB sheet)...
...that we want to evenly spead on X cells (X specified in B1; 8, in the example)

Then for each of the Total we calculate the average (orange, in the example) and the "delta" (blue)

We calculate in I2 the first value of the series for the first Total with the formula =F2
We calculate in J2 the second value with the formula =IF(J$1<=$B$1,I2+$F2,"")
Then copy J2 to the right for as many headers are set in Row1
Then copy down the first row of formulas for as many rows as Totals are listed

When you modify B1, the output will span to that number of columns

Example and formulas:

BR1 Sales_Ledger RECON(9.2.3) ott-2022
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Cells	8						Index	1	2	3	4	5	6	7	8	9	10	11
2	Sum	36	Average	4,5	Delta:	1		Value	1,00	2,00	3,00	4,00	5,00	6,00	7,00	8,00
3	Sum	44	Average	5,5	Delta:	1,222222		Value	1,22	2,44	3,67	4,89	6,11	7,33	8,56	9,78
4	Sum	55	Average	6,88	Delta:	1,527778		Value	1,53	3,06	4,58	6,11	7,64	9,17	10,69	12,22
5	Sum	62	Average	7,75	Delta:	1,722222		Value	1,72	3,44	5,17	6,89	8,61	10,33	12,06	13,78
6
7
8
Foglio14

Cell Formulas
Range	Formula
J1:S1	J1	=I1+1
I2:I5	I2	=F2
J2:S5	J2	=IF(J$1<=$B$1,I2+$F2,"")
D2:D5	D2	=B2/$B$1
F2:F5	F2	=D2/(($B$1+1)/2)

 

[rpg1966]

Thank you. I may not have explained the problem very well!

Your solution seems to split the sum of all input cells evenly over the output cells. However, my requirement is that the input cells are split over the output cells "chunk by chunk". In this simplified example, with 3 input values and 4 output values, the inputs are split into 3/4 chunks that are then moved into the output cells.

(In my actual requirement, there are 97 input cells and 100 output cells, so each chunk will be 97/100ths of the input cells.)

Here is a different diagram to try to explain it:

 

[johnnyL]

@rpg1966 your second deswcription of the second value calculated in the original post is contradictory, but see if the following helps:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Input Value 1	Input Value 2	Input Value 3			Sum			# of Input values	# of Output values
2	12	10	14			36			3	4
3	Output Value 1	Output Value 2	Output Value 3	Output Value 4
4	9	8	8.5	10.5		36
5
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(A2:C2)
A4	A4	=$I2/$J2*$A2
B4	B4	=(1-$I2/$J2)*$A2+($I2/$J2-(1-$I2/$J2))*$B2
C4	C4	=(1-($I2/$J2-(1-$I2/$J2)))*$B2+($I2/$J2-(1-(($I2/$J2-(1-$I2/$J2)))))*$C2
D4	D4	=$I2/$J2*$C2
F4	F4	=SUM(A4:D4)

 

[rpg1966]

You're right johnnyL, I accidentally typo'ed "1/4*9" instead of "1/4*12" in the comment box in the first image.

Thanks for your solution. Again, it seems I haven't been clear, apologies. I'm looking for a way to do this for expanding 97 values into 100 values, for which I'd need a generalised version of the formulae in your cells B4 and C4; the 3-values-to-4-values example was very simplified in order to get across the idea. It's the solution for that much larger set of values that I can't figure out a "simple" solution for.

 

[johnnyL]

Maybe if you provided a different example of what 4 input vales to 5 or 6 output values would look like might get some more people to respond.

 

[rpg1966]

CC

Maybe if you provided a different example of what 4 input vales to 5 or 6 output values would look like might get some more people to respond.

Cheers. I think the second image I attached says it all; it would just be 97 input and 100 output values, instead of 3 and 4 respectively. The first and last output values are always easy to calculate; it's the inner 2 (or 98!) output values for which I was wondering if there was a solution.

No worries, thanks for responding

 

[kevin9999]

The problem, as I see it, isn't those sequences where there's only one difference between the input table and the output table (see below). The problem arises when there are more than one difference between the input & the output apropos where do you point the last output formula? What I mean is, if you have 100 outputs, do you point the formula in output #100 at input #97 (using your actual issue figures) and if so, does output #99 point to input #96, and so on? If so, and you probably already see the problem here, where do you end up pointing output #2?

formula.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	Index	1	2	3			sum		Max Input	Max Output	Percentage	Increment
3	Value	12	10	14			36		3	4	0.75	0.25
4
5	Index	1	2	3	4		sum
6	Value	9	8	8.5	10.5		36
7
8
9
10
11	Index	1	2	3	4	5	6	7	8	9			sum		Max Input	Max Output	Percentage	Increment
12	Value	16	11	14	15	10	13	15	17	20			131		9	10	0.9	0.1
13
14	Index	1	2	3	4	5	6	7	8	9	10		sum
15	Value	14.4	10.4	12	13.2	11	10.2	12.3	13.9	15.6	18		131
16
Sheet1

Cell Formulas
Range	Formula
I3,I6	I3	=SUM(D3:G3)
K3,Q12	K3	=MAX(D2:G2)
L3,R12	L3	=MAX(D5:G5)
M3,S12	M3	=K3/L3
N3,T12	N3	=M3/K3
D6	D6	=D3*M3
E6	E6	=(D3*($N3*(COUNT($D3:E3)-1)))+(E3*($M3-((COUNT($D$3:E3)-1)*$N3)))
F6	F6	=(E3*($N$3*(COUNT($D$3:F3)-1)))+(F3*($M$3-((COUNT($D$3:F3)-1)*$N$3)))
G6,M15	G6	=F3*M3
O12,O15	O12	=SUM(D12:M12)
D15	D15	=D12*S12
E15:L15	E15	=(D12*($T12*(COUNT($D12:E12)-1)))+(E12*($S12-((COUNT($D$12:E12)-1)*$T12)))

 

[rpg1966]

Indeed. Output 1 will always be a fraction of input 1, and output 100 will always be a fraction of input 97 (since there are more output values than input values), but the in-between output values will be a sum of a fraction of each of two other cells.

There is presumably some way of using the ratio of the number of input to output values (your cell S12) to calculate which two input cell relates to each output cell, and what fractions of each to include, but I just can't wrap my brain around what might be a relatively "simple" way of doing that.

 

[kevin9999]

but the in-between output values will be a sum of a fraction of each of two other cells.

And that's the bit I can't wrap my head around - which ones? If you could supply a list of 1 to 100 indicating which of the two input cells (from the 1 to 97) they should refer to - you'll have (practically) solved your problem.