I'm trying to convert a set of tables, that I normally use dice to come up with a final result, to excel to save time.

so this is what the table looks like if rolling dice

Contents(roll d6)   -  treasure
1-2 = empty       -    1/6
3-4 = monster     -    3/6
5 = special     -      nil
6 = trap        -      2/6

I know to do the first column I can use the INDEX and RANDBETWEEN functions, but I'm not sure how to get the treasure column to work.

I'm pretty sure it would use a series of if statements(like: if monster roll d6 if 1-3 return treasure), but would I need a separate formula for monster, empty, trap, and special, or could it all be generated at once?

  • 2
    to do the first column I can use the INDEX and RANDBETWEEN functions <-- can u pls share the formula for this?
    – p._phidot_
    Jul 21 at 7:41

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:

      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.


Assuming the rolled number is in A1. In B2 :


Please share if it works/not. ( :

  • it does work, yet not exactly what I was looking for Jul 22 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.