Math Mad Minute

[SGP]

I want to create a subtraction math mad minute sheet (about 60 questions) for my students. The first version has to have no negative numbers. Easy enough to do with RANDBETWEEN if the target definition cells contain a min (eg 0) and a max (eg 9). The problem is that RANDBETWEEN is a uniform distribution so I'm going to see as many 1s (for example) as I will 9s. 1s can only have 2 possible outcomes ( 1-1=0 and 1-0=1) but a 9 can have 10 outcomes. The result is a "boring" sheet with way too many low numbers and not enough high numbers. So in 55 "random" rolls, how can I get 10 9s, 9 8s, .......2 1s, and 1 0? I'd prefer to just specify a max value in a cell as opposed to something like using macros.

SGP

 

[6StringJazzer]

This will approximate the proportions you want but you cannot get the exact proportions with a formula that generates random numbers.

=LOOKUP(RANDBETWEEN(0,54),{0,10,19,27,34,40,45,49,52,54},{9,8,7,6,5,4,3,2,1,0})

To get the exact proportions you can use a shuffle approach. Create a list of 55 numbers 0-9 using the proportions you want, then have a formula in an adjacent column that assigns a random number to each, then use the LARGE function to select numbers in that random order. Click on the cell to see the formula.

scratch.xlsm
	S	T	U
1	Number	Random	Chosen number for this position
2	9	0.094685	2
3	9	0.183711	7
4	9	0.86073	7
5	9	0.235186	1
6	9	0.435596	5
7	9	0.239282	3
8	9	0.861847	3
9	9	0.642851	6
10	9	0.056007	5
11	9	0.287127	9
12	8	0.461243	9
13	8	0.489198	8
14	8	0.853766	8
15	8	0.804564	7
16	8	0.443394	8
17	8	0.756823	4
18	8	0.597703	7
19	8	0.625583	4
20	8	0.096425	4
21	7	0.226809	7
22	7	0.977093	6
23	7	0.01268	9
24	7	0.976892	6
25	7	0.689569	8
26	7	0.757342	3
27	7	0.271562	8
28	7	0.746704	5
29	6	0.227135	6
30	6	0.642761	8
31	6	0.897234	4
32	6	0.342119	3
33	6	0.670183	8
34	6	0.329954	4
35	6	0.492151	8
36	5	0.152085	9
37	5	0.04668	1
38	5	0.926517	6
39	5	0.568147	0
40	5	0.06386	6
41	5	0.886506	9
42	4	0.753881	7
43	4	0.736147	9
44	4	0.723276	9
45	4	0.458285	6
46	4	0.479395	7
47	3	0.918865	2
48	3	0.601238	9
49	3	0.909619	5
50	3	0.471403	8
51	2	0.989369	9
52	2	0.195034	5
53	2	0.026962	9
54	1	0.34265	5
55	1	0.946475	2
56	0	0.340561	7
SGP random

Cell Formulas
Range	Formula
T2:T56	T2	=RAND()
U2:U56	U2	=INDEX($S$2:$S$56,MATCH(LARGE($T$2:$T$56,ROW()-1),$T$2:$T$56,0))

 

[lrobbo314]

Something like this? The left side generates the numbers and the right side makes sure that the higher numbers are on top to avoid negative answers.

TCOs working ELC Zones 2021 JJ.xlsx
	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
1
2		3			9			9			9			7			9			8			8			7			1			7			9			9			9			7			9			9			9			8			5
3	-	7		-	6		-	6		-	9		-	1		-	5		-	9		-	9		-	8		-	5		-	3		-	6		-	6		-	9		-	1		-	5		-	8		-	8		-	7		-	1
4
5
6		8			8			4			9			7			4			5			5			9			7			8			8			4			9			9			9			7			6			9			8
7	-	7		-	6		-	3		-	9		-	9		-	9		-	7		-	6		-	9		-	8		-	7		-	6		-	3		-	9		-	7		-	4		-	5		-	5		-	9		-	7
8
9
10		4			7			6			9			5			8			6			5			4			1			9			9			7			9			7			8			9			6			8			4
11	-	9		-	9		-	7		-	9		-	7		-	6		-	9		-	6		-	8		-	4		-	4		-	7		-	6		-	9		-	5		-	6		-	6		-	5		-	4		-	1
12
13
14		8			9			5			4			8			8			5			7			7			8			8			9			9			6			8			8			8			7			7			9
15	-	2		-	5		-	9		-	6		-	6		-	2		-	8		-	7		-	4		-	9		-	2		-	5		-	5		-	4		-	6		-	2		-	5		-	7		-	4		-	8
16
17
18		9			7			7			8			8			2			2			6			9			9			9			7			7			9			9			7			9			7			9			9
19	-	6		-	7		-	6		-	9		-	9		-	7		-	9		-	7		-	3		-	7		-	6		-	7		-	6		-	8		-	8		-	2		-	2		-	6		-	3		-	7
20
21
22		6			7			4			5			9			8			7			6			6			9			7			8			4			6			9			8			9			7			6			9
23	-	7		-	8		-	1		-	6		-	6		-	7		-	9		-	7		-	5		-	9		-	6		-	7		-	1		-	5		-	6		-	7		-	7		-	6		-	5		-	9
24
25
26		4			7			5			9			6			9			6			7			9			7			4			7			5			9			6			9			7			7			9			7
27	-	2		-	5		-	3		-	8		-	1		-	7		-	7		-	4		-	2		-	6		-	2		-	5		-	3		-	8		-	1		-	7		-	6		-	4		-	2		-	6
28
29
30		6			7			8			9			5			6			7			8			9			5			6			8			8			9			9			6			9			8			9			7
31	-	6		-	8		-	8		-	8		-	9		-	6		-	9		-	7		-	5		-	7		-	6		-	7		-	8		-	8		-	5		-	6		-	7		-	7		-	5		-	5
32
33
34		8			9			7			8			6			9			8			4			3			2			8			9			9			9			6			9			9			7			5			6
35	-	5		-	7		-	9		-	9		-	5		-	6		-	9		-	7		-	5		-	6		-	5		-	7		-	7		-	8		-	5		-	6		-	8		-	4		-	3		-	2
36
37
38		5			7			3			9			8			7			6			8			8			3			5			8			5			9			9			9			8			8			8			8
39	-	4		-	8		-	5		-	8		-	9		-	9		-	8		-	8		-	6		-	8		-	4		-	7		-	3		-	8		-	8		-	7		-	6		-	8		-	6		-	3
Sheet5

Cell Formulas
Range	Formula
B2:B3,AC38:AC39,Z38:Z39,W38:W39,T38:T39,Q38:Q39,N38:N39,K38:K39,H38:H39,E38:E39,B38:B39,AC34:AC35,Z34:Z35,W34:W35,T34:T35,Q34:Q35,N34:N35,K34:K35,H34:H35,E34:E35,B34:B35,AC30:AC31,Z30:Z31,W30:W31,T30:T31,Q30:Q31,N30:N31,K30:K31,H30:H31,E30:E31,B30:B31	B2	=MID(CONCAT(REPT({9;8;7;6;5;4;3;2;1},{8;7;6;5;4;3;2;1;1})),RANDBETWEEN(1,37),1)
AF2,BG38,BD38,BA38,AX38,AU38,AR38,AO38,AL38,AI38,AF38,BG34,BD34,BA34,AX34,AU34,AR34,AO34,AL34,AI34,AF34,BG30,BD30,BA30,AX30,AU30,AR30,AO30,AL30,AI30,AF30,BG26,BD26,BA26,AX26,AU26,AR26,AO26,AL26,AI26,AF26,BG22,BD22,BA22,AX22,AU22,AR22,AO22,AL22,AI22,AF22	AF2	=IF(B2<>"",MAX(INT(B2:B3)),"")
AF3,BG39,BD39,BA39,AX39,AU39,AR39,AO39,AL39,AI39,AF39,BG35,BD35,BA35,AX35,AU35,AR35,AO35,AL35,AI35,AF35,BG31,BD31,BA31,AX31,AU31,AR31,AO31,AL31,AI31,AF31,BG27,BD27,BA27,AX27,AU27,AR27,AO27,AL27,AI27,AF27,BG23,BD23,BA23,AX23,AU23,AR23,AO23,AL23,AI23,AF23	AF3	=IF(B2<>"",MIN(INT(B2:B3)),"")

 

[lrobbo314]

Here is a distribution of the numbers generated by that formula. It's not exact, but you get a definite bigger proportion of larger numbers.

TCOs working ELC Zones 2021 JJ.xlsx
	A	B
1	Value	Count
2	9	46
3	8	34
4	7	38
5	6	30
6	5	21
7	4	12
8	3	7
9	2	7
10	1	5
Table4

 

[SGP]

Thanks Irobbo3114 for directing me to the Lookup function.
Here's the result.
Row 1 is the target max
Row 2 contains the cumulative requirement for the sum of the prior entries (e.g. k2=k1+j2).
Column AI is 1500 input points
Column AJ is the lookup for those inputs.
Since max is actually 1 less than the cumulative (e.g. 54 vs 55) for the target (i.e. 9) the Lookup function doesn't get an mistake at the top end.

So input 9 as a target, the max is found from Row2, AJ is the result. So in the first case, a RANDBETWEEN of 27 results in a "6".
The left graph (1500 points in AI) was just to prove to myself that I was getting a roughly uniform distribution on 0-max
Row 8 are the bins from 0-target
The right graph was to prove to myself that each number between 0-target was chosen with increasing frequency.

After that it was trivial to implement a worksheet where the higher numbers were seen more often in the minuend.

I'm happy!

Thanks