I need a formula to automatically distribute an amount across multiple cells where the max value of each cell and the order of distribution can be de

[Islam Gaber]

Im trying to upload a container with boxes , so I need to tell the sheet how many boxes do I have and all the rows will be like (the first row 6,6,6,6,6,3) and I need to know in which row the product will end. Thank you.

 

[lrobbo314]

Is this what you're looking for?

In the bottom section I turned the formula into a named range lambda.

DISTRIBUTION
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		1	2	3	4	5	6	7	8	9	10	Total
3	1	6	6	6	6	6	3					33
4	2	6	6	4								16
5	3	6	6	6	6	1						25
6	4	6	6	6	6	6	6	6	2			44
7	5	6	6	6	6	3						27
8
9
10
11		1	2	3	4	5	6	7	8	9	10	Total
12	1	6	6	6	6	6	3					33
13	2	6	6	4								16
14	3	6	6	6	6	1						25
15	4	6	6	6	6	6	6	6	2			44
16	5	6	6	6	6	3						27
Data

Cell Formulas
Range	Formula
B3:B7	B3	=LET(val,L3,div,6,s,SEQUENCE(,IF(MOD(val,div)=0,val/div,INT(val/div)+1)),MAP(s,LAMBDA(x,IF(x*div<val,div,val-((x-1)*div)))))
B12:B16	B12	=DISTRIBUTE(L12,6)
Dynamic array formulas.

 

[Dave Patton]

N.B. When I put the information into a sheet, the data L12:L16 was Text.

I had trouble getting the Lambda suggestion to work and I tried a variety of naming alternatives.
I prepared a simple solution and checked the totals. The numbers did not compute; consequently, I made the text numbers real numbers
and I was Ok. The Lambda also worked correctly.


lrobbo314 please advise what value (named formula) you used for the function Distribute.

The simple solution follows

LAMBDA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
18		1	2	3	4	5	6	7	8	9	10	Total	Num
19	1	6	6	6	6	6	3	0	0	0	0	33	6
20	2	6	6	4	0	0	0	0	0	0	0	16	3
21	3	6	6	6	6	1	0	0	0	0	0	25	5
22	4	6	6	6	6	6	6	6	2	0	0	44	8
23	5	6	6	6	6	3	0	0	0	0	0	27	5
3a

Cell Formulas
Range	Formula
B19:B23	B19	=MIN(6,L19)
C19:K23	C19	=MIN(6,$L19-SUM($B19:B19))
M19:M23	M19	=COUNTIF(B19:K19,">0")

 

[lrobbo314]

This is how I wrote the formula for the 'DISTRIBUTE' function.

=LAMBDA(
    total,divisor,
    LET(
        val,total,
        div,divisor,
        s,SEQUENCE(,IF(MOD(val,div)=0,val/div,INT(val/div)+1)),
        MAP(
            s,
                LAMBDA(x,
                    IF(x*div<val,
                        div,
                        val-((x-1)*div)
                    )
                )
        )
    )
)

 

[Dave Patton]

To Orobbo314, thank you. I was not sure of the Let part and the naming.


If column A is blank or contains text, the following will work.

LAMBDA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
18		1	2	3	4	5	6	7	8	9	10	Total	Num
19	Order9	6	6	6	6	6	3					33	6
20	Order10	6	6	4								16	3
21	Order11	6	6	6	6	1						25	5
22	Order12	6	6	6	6	6	6	6	2			44	8
23	Order13	6	6	6	6	3						27	5
24	Total	30	30	28	24	16	9	6	2			145
25
3a

Cell Formulas
Range	Formula
B19:K23	B19	=MIN(6,$L19-SUM($A19:A19))
M19:M23	M19	=COUNTIF(B19:K19,">0")
B24:L24	B24	=SUM(B19:B23)