Table-top game mechanic / getting a result from a table

[briarlynn_daisy]

Hi, I am trying to use Excel to simulate die rolls and generating stats and modifiers.

I have a table of stat roll outcomes, and the associated percentile modifier:

Book1
	G	H
2	Stat Rolls	Modifier
3	-5	-0.15
4	-4	-0.15
5	-3	-0.1
6	-2	-0.1
7	-1	-0.05
8	0	-0.05
9	1	0
10	2	0
11	3	0.05
12	4	0.05
13	5	0.1
14	6	0.1
15	7	0.15
16	8	0.15
17	9	0.2
18	10	0.2
19	11	0.25
20	12	0.25
21	13	0.3
22	14	0.3
23	15	0.35
24	16	0.35
Sheet1

And on my other table for a random stat roll and modifiers, I want a to use the randbetween fx to generate a roll, and then display the associated modifier based on the the result of that roll. How can I do this?

Book1
	J	K	L
2	Stat:	Value (-5 through 16)	Modifier
3	Luck
4	Intellect
5	Resilience
6	Agility
7	Strength
8	Magik
9	Awareness
10	Personality
11	Spirit
Sheet1

 

[Eric W]

Welcome to the MrExcel forum!

You can just use a lookup:

Book1
	G	H	I	J	K	L
1
2	Stat Rolls	Modifier		Stat:	Value (-5 through 16)	Modifier
3	-5	-0.15		Luck	9	0.2
4	-4	-0.15		Intellect	7	0.15
5	-3	-0.1		Resilience	-3	-0.1
6	-2	-0.1		Agility	-4	-0.15
7	-1	-0.05		Strength	-3	-0.1
8	0	-0.05		Magik	1	0
9	1	0		Awareness	11	0.25
10	2	0		Personality	14	0.3
11	3	0.05		Spirit	16	0.35
12	4	0.05
13	5	0.1
14	6	0.1
15	7	0.15
16	8	0.15
17	9	0.2
18	10	0.2
19	11	0.25
20	12	0.25
21	13	0.3
22	14	0.3
23	15	0.35
24	16	0.35
25
Sheet3

Cell Formulas
Range	Formula
K3:K11	K3	=RANDARRAY(9,,-5,16,TRUE)
L3:L11	L3	=VLOOKUP(K3#,G3:H24,2,0)
Dynamic array formulas.

Since the modifiers follow a simple pattern, you can also use

=INT((K3#-1)/2)*0.05

for the L3 formula, and you don't need to reference the G2 table at all.

 

[briarlynn_daisy]

Welcome to the MrExcel forum!

You can just use a lookup:

Book1
	G	H	I	J	K	L
1
2	Stat Rolls	Modifier		Stat:	Value (-5 through 16)	Modifier
3	-5	-0.15		Luck	9	0.2
4	-4	-0.15		Intellect	7	0.15
5	-3	-0.1		Resilience	-3	-0.1
6	-2	-0.1		Agility	-4	-0.15
7	-1	-0.05		Strength	-3	-0.1
8	0	-0.05		Magik	1	0
9	1	0		Awareness	11	0.25
10	2	0		Personality	14	0.3
11	3	0.05		Spirit	16	0.35
12	4	0.05
13	5	0.1
14	6	0.1
15	7	0.15
16	8	0.15
17	9	0.2
18	10	0.2
19	11	0.25
20	12	0.25
21	13	0.3
22	14	0.3
23	15	0.35
24	16	0.35
25
Sheet3

Cell Formulas
Range	Formula
K3:K11	K3	=RANDARRAY(9,,-5,16,TRUE)
L3:L11	L3	=VLOOKUP(K3#,G3:H24,2,0)
Dynamic array formulas.

Since the modifiers follow a simple pattern, you can also use

=INT((K3#-1)/2)*0.05

for the L3 formula, and you don't need to reference the G2 table at all.

Thank you soo much!

 

[briarlynn_daisy]

Welcome to the MrExcel forum!

You can just use a lookup:

Book1
	G	H	I	J	K	L
1
2	Stat Rolls	Modifier		Stat:	Value (-5 through 16)	Modifier
3	-5	-0.15		Luck	9	0.2
4	-4	-0.15		Intellect	7	0.15
5	-3	-0.1		Resilience	-3	-0.1
6	-2	-0.1		Agility	-4	-0.15
7	-1	-0.05		Strength	-3	-0.1
8	0	-0.05		Magik	1	0
9	1	0		Awareness	11	0.25
10	2	0		Personality	14	0.3
11	3	0.05		Spirit	16	0.35
12	4	0.05
13	5	0.1
14	6	0.1
15	7	0.15
16	8	0.15
17	9	0.2
18	10	0.2
19	11	0.25
20	12	0.25
21	13	0.3
22	14	0.3
23	15	0.35
24	16	0.35
25
Sheet3

Cell Formulas
Range	Formula
K3:K11	K3	=RANDARRAY(9,,-5,16,TRUE)
L3:L11	L3	=VLOOKUP(K3#,G3:H24,2,0)
Dynamic array formulas.

Since the modifiers follow a simple pattern, you can also use

=INT((K3#-1)/2)*0.05

for the L3 formula, and you don't need to reference the G2 table at all.

I'm getting a Spill error when I use =RANDARRAY(9,,-5,16, TRUE).

Book1
	I	J	K
2	Stat:	Value (-5 through 16)	Modifier
3	Luck	#SPILL!	20%
4	Intellect	#SPILL!	-5%
5	Resilience	#SPILL!	5%
6	Agility	#SPILL!	-5%
7	Strength	#SPILL!	15%
8	Magik	#SPILL!	15%
9	Awareness	#SPILL!	10%
10	Personality	#SPILL!	0%
11	Spirit	#SPILL!	5%
Sheet1

Cell Formulas
Range	Formula
J3:J11	J3	=RANDARRAY(9,,-5,16,TRUE)

 

[briarlynn_daisy]

GOT IT!

PoD Roll Mechanics.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Stat Rolls	Modifier		STAT AVG	BONUS		Stat:	Value (-5 through 16)	Modifier
2	-5	-15%		-5	1		Luck	12	25%
3	-4	-15%		-4	1		Intellect	-1	-5%
4	-3	-10%		-3	1		Resilience	16	35%
5	-2	-10%		-2	1		Agility	11	25%
6	-1	-5%		-1	2		Strength	-1	-5%
7	0	-5%		0	2		Magik	-1	-5%
8	1	0%		1	2		Awareness	3	5%
9	2	0%		2	2		Personality	1	0%
10	3	5%		3	3		Spirit	-4	-15%
11	4	5%		4	3		Stat AVG	4
12	5	10%		5	3		STAT BONUS	3.0
13	6	10%		6	3
14	7	15%		7	4
15	8	15%		8	4		Luck	INT	RES	AGIL	STR	MAG	AWARE	PERS	SPR
16	9	20%		9	4		25%	-5%	35%	25%	-5%	-5%	5%	0%	-15%
17	10	20%		10	4
18	11	25%		11	5		2.5%	19.5%	3.5%	-27.5%	-0.5%	49.5%	40.5%	-10.0%	68.5%
19	12	25%		12	5
20	13	30%		13	5
21	14	30%		14	5		WILL	%TILE	CNTR RNG		SAVES	%TILE
22	15	35%		15	6		MAG WILL	13%	25%		FORTITUDE	55%
23	16	35%		16	6		DIV WILL	3%	5%		PSYCH	0%
24							MYST WILL	23%	45%		ANIMUS	45%
25											DEATH	30%
Sheet1

Cell Formulas
Range	Formula
N2:N10	N2	=VLOOKUP(M2#,$F$2:$G$23,2,0)
M2:M10	M2	=RANDARRAY(,1,-5,16,TRUE)
M11	M11	=AVERAGE([Value (-5 through 16)])
M12	M12	=LOOKUP(Table4[[#Totals],[Value (-5 through 16)]],I2:J23)
L16	L16	=N2
M16	M16	=N3
N16	N16	=N4
O16	O16	=N5
P16	P16	=N6
Q16	Q16	=N7
R16	R16	=N8
S16	S16	=N9
T16	T16	=N10
L18	L18	=($B2+L$16)/10
M18	M18	=($B3+M$16)/10
N18	N18	=($B4+N$16)/10
O18	O18	=($B5+O$16)/10
P18	P18	=($B6+P$16)/10
Q18	Q18	=($B7+Q$16)/10
R18	R18	=($B8+R$16)/10
S18	S18	=($B9+S$16)/10
T18	T18	=($B10+T$16)/10
M22	M22	=N7+N4/2
N22:N24	N22	=[@[%TILE]]*2
M23	M23	=N10+N4/2
M24	M24	=N8+N4/2
Q22	Q22	=(($N$2+$N$4+$N$6)/3)*M12
Q23	Q23	=(($N$2+$N$3+$N$4)/3)*M13
Q24	Q24	=(($N$2+$N$4+$N$10)/3)*M12
Q25	Q25	=(($N$2+$N$4+$N$6+$N$10)/4)*M12

 

[Eric W]

It looks like you're new to the world of spill formulas. Just for future reference, a spill formula is a formula you put in a single cell, but the formula generates multiple results, which then "spill" to other cells. So for example, my original RANDARRAY formula was designed to put in a single cell, then it would create 9 random numbers which would fill the cells below that cell. But it requires that the cells below it are empty. So when you got the SPILL errors, it looks like you were trying to put the formula in all of the cells. There's nothing wrong with the way you ended up doing it, it's just a different approach.

Anyway, I'm glad you got it working!