Spreading a value across an array

[narnian_uk]

I have an array which looks like this (but any of the elements could contain any positive value or zero, and there could be fewer or more rows; this is just an example):

I'm trying to write a formula which spreads the values in the above array evenly across three columns so that it ends up looking like this (I'll only show the final seven columns in order to make the screenshot large enough to be visible):

I had initially assumed that I could perhaps use something like =IF(array>0, CHOOSE({1,2,3},array/3,array/3,array/3),) - but that just produces an #N/A (I assume it's the equivalent of some kind of spill error).

I'd very much appreciate any thoughts you might have (including 'can't be done, stop wasting your time')!

 

[lrobbo314]

Will you always have padding on either side? That is to say, are you ever going to have a situation where the positive value is on the edge of the range? Or, will there be enough zeros on either side to be able to spread the value to the left and to the right of the positive value?

 

[narnian_uk]

Will you always have padding on either side? That is to say, are you ever going to have a situation where the positive value is on the edge of the range? Or, will there be enough zeros on either side to be able to spread the value to the left and to the right of the positive value?

Good question. The value will always need to be spread to the right, so there'll never need to be padding on the left. Let's assume that there'll always be enough padding to the right - I think that's likely anyway.

 

[lrobbo314]

Maybe this...

narnian
	A	B	C	D	E	F	G	H
1	0	0	0	65.8	0	0	0	0
2	0	0	0	65.8	0	0	0	0
3	0	0	47	0	0	0	0	0
4
5	0	0	21.93333	21.93333	21.93333	0	0	0
6	0	0	21.93333	21.93333	21.93333	0	0	0
7	0	15.66667	15.66667	15.66667	0	0	0	0
Sheet8

Cell Formulas
Range	Formula
A5:H7	A5	=LET(tc,TOCOL(A1:H3),rc,ROWS(tc),sp,EXPAND(MAP(SEQUENCE(rc-2),LAMBDA(x,SUM(INDEX(tc,SEQUENCE(3,,x)))/3)),rc,,0),rt,INDEX(sp,MOD(SEQUENCE(ROWS(sp),,0)-1,rc)+1),WRAPROWS(rt,COLUMNS(A1:H3)))
Dynamic array formulas.