Randomly fill cells with numbers to sum up to a given total

[Lux Aeterna]

Hello again!
I've got a total score here that I'd like to split to several smaller random numbers. For example, N3=22. I'd like a formula preferably that would add numbers to cells O3:X3 that sum up to 22. Numbers I can use are only even ones, from 0 to 6 (0, 2, 4, 6).

I'd rather have a formula because sometimes I might need to use odd numbers as well and I'd like to be adjust it myself.

PS. If what I am asking for is difficult, we could do it the other way. Meaning, add numbers to O3:X3 that sum up to a given range (range is in cell M3).

Thanks a lot!

 

[JamesCanale]

I was tinkering with some of the constants to get a more even distribution. I'm not crazy about it.

Book1
	N	O	P	Q	R	S	T	U	V	W	X
2	Final Value	q1	q2	q3	q4	q5	q6	q7	q8	q9	q10
3	22	2	2	0	6	0	8	4	0	0	0
4	14	4	2	2	0	0	0	2	0	0	4
5	26	0	8	0	2	4	6	4	2	0	0
6	10	0	2	0	0	0	8	0	0	0	0
7	44	0	10	6	4	4	8	10	2	0	0
Sheet1

Cell Formulas
Range	Formula
O3:O7	O3	=RANDBETWEEN(0,N3/6)*2
P3:S7	P3	=IFERROR(INT(RANDBETWEEN(0,($N3-SUM($O3:O3))/3)/2)*2,0)
T3:W7	T3	=IFERROR(INT(RANDBETWEEN(0,$N3-SUM($O3:S3))/2)*2,0)
X3:X7	X3	=N3-SUM(O3:W3)

 

[lrobbo314]

here is my attempt.

ExpandRange.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2	Final Value	q1	q2	q3	q4	q5	q6	q7	q8	q9	q10	q11	q12	q13	q14	q15
3	22	2	6	4	4	6
4	14	6	4	4
5	26	4	6	6	6	4
6	10	2	6	2
7	44	2	4	2	2	2	4	6	4	6	4	6	2
Sheet6

Cell Formulas
Range	Formula
O2:AC2	O2	="q"&SEQUENCE(,15)
O3:AC7	O3	=LET(v,$N3,er,$N3:N3,rv,SUM(er)-v,IF(rv<v,IF(v-rv>6,CHOOSE(RANDBETWEEN(1,3),2,4,6),v-rv),""))
Dynamic array formulas.

 

[Lux Aeterna]

here is my attempt.

Unfortunately I've got office 2019 and it doesn't support let function.

I was tinkering with some of the constants to get a more even distribution. I'm not crazy about it.

This seemed to work, but the only possible cell values are 0, 2, 4 and 6. Don't worry about how the values get distributed.

 

[Fluff]

Unfortunately I've got office 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Lux Aeterna]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks, just did it!