Automating a Random Table in Excel
You can use either of the following:
=IF(RANDBETWEEN(1,6)<=2, "Empty with "& IF(RANDBETWEEN(1,6)=1, "treasure", "no treasure"),
IF(RANDBETWEEN(1,6)<=4, "Monster with "& IF(RANDBETWEEN(1,6)<=3, "tresure","no treasure"),
IF(RANDBETWEEN(1,6)<=5, "Special",
"Trap with "& IF(RANDBETWEEN(1,6)<=2, "tresure","no treasure")
)))
It's structured in the formula editor to look like the above so that each piece of the IF()
has a clear layout.
Many would prefer using IFS()
though, as the layout can be even cleaner and with the individual lines being complete unto themselves, it is very easy to understand, edit, and add to or subtract from:
=IFS(
RANDBETWEEN(1,6)<=2, "Empty with "& IF(RANDBETWEEN(1,6)=1, "treasure", "no treasure"),
RANDBETWEEN(1,6)<=4, "Monster with "& IF(RANDBETWEEN(1,6)<=3, "tresure","no treasure"),
RANDBETWEEN(1,6)<=5, "Special",
TRUE, "Trap with "& IF(RANDBETWEEN(1,6)<=2, "tresure","no treasure")
)
You could add in the complications of more than one die, or more than one type of die, for each of the six "rolls" that are shown (six are shown, but only two would ever "occur" for each result). The structuring with IFS()
is especially amenable to that though it could, of course, be done with the standard nested IF()
as well.
You do not show an earlier version tag, so you presumably have LET()
available to you. If you use LET()
, the above can be done with just something like "RoomRoll" and "TreasureRoll" BUT if you do, you cannot take the shortcut I did with the way the roll results are evaluated. You'd have to actually test the range "3 or 4" for example, not be able to use the test "<=4" (which works because if it had been a 1 or a 2, there'd be no need to test the 3-4 range). To avoid complications in the working part of the formula, you might prefer using "Roll1", "Roll2", etc. The first would be more extensible for future needs, but the latter would be easier NOW. So... choices...
Naturally, you could choose to split up the two results, Room and Treasure, rolling for them in separate cells. But that's thinking just slightly off from just rolling the dice. Realizing you can get both in one step, as you ask for, is making much better use of the tool.