## [SOLVED] Trying to roll X number of Y sided dice

Discuss the spreadsheet application

### [SOLVED] Trying to roll X number of Y sided dice

I'm new to coding and stuck on a problem I can't find an answer for.

I'm trying to put together something that will allow me to input into two cells a number of dice to be rolled and the number of sides each die has that will give me the sum of the dice rolled in a third cell.
dicesample.png (4.27 KiB) Viewed 1117 times
https：//i.ibb.co/0XMfqq9/dicesample.png
Last edited by robleyd on Mon May 03, 2021 8:59 am, edited 2 times in total.
Reason: Add green tick
Win10 OpenOffice 4.1.9
Galarandir

Posts: 2
Joined: Fri Apr 30, 2021 11:40 pm

### Re: Trying to roll X number of Y sided dice

Welcome to the Forums.
There is no way to calculate the sum of the rolled dice, as there is no way of knowing which die is going to land on which side.
It is possible to calculate the range of possible results. Your minimum would be simply the number of dice. Your maximum would be the number of sides times the number of dice.
Please note that the above calculations are based on the standard die sequencing. ie: starting at 1 and increasing by 1 per side.

P.S.: You could get a total by calculating a random roll for each die and then summing those up. I can think of two ways of doing that, though both have the limitation of setting a maximum number of die for the calculations. Since you have an entry for the number of die, you would have to either limit that or find another way to calculate an unknown number of die.
This might be possible with a macro, as it would have to contain a loop, but (in Calc) I can think of no other options.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6419
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Trying to roll X number of Y sided dice

You must generate random integer numbers between the minimum and maximum value of one dice (it is possible by usage the Cell Formulas), but you must do it many times cyclically by number of the dices.
Therefore the easiest way to solve this task the macro function. Write your macro function - if you need it really.

Otherwise you must use helper cells what handle all of the dices (the possible maximum count of the dices), and then you must sum the those dice values (only) what are inside the actual dice count. A Ctrl-Shift-F9 will "roll the dices" again.

Here is a sample file. Check the applied Named Ranges, the applied Formulas, the applied Data Validity in the input cells, the applied Styles and the Conditional Format feature.

Note: my sample file was created in the LibreOffice 6.1.6 version. Some features work differently than they work in the Apache OpenOffice. I just tried it in the AOO 4.1.3 portable version: it works.

DiceSum.ods
(14.73 KiB) Downloaded 41 times

 Edit: Edited: I just fixed some glitches:

DiceSum_fixed.ods
(13.86 KiB) Downloaded 39 times
Last edited by Zizi64 on Sat May 01, 2021 10:22 am, edited 2 times in total.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 9887
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Trying to roll X number of Y sided dice

Hello,
found a solution without helper-cells, using array formulas.
Attachments
DICE_SUM_ARRAY.ods
(9.41 KiB) Downloaded 46 times
MMove 1.0.6
• Extension for easy, exact positioning of shapes, pictures, controls, frames ...
my current system
• Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer

Posts: 989
Joined: Fri Dec 16, 2011 8:20 pm

### Re: Trying to roll X number of Y sided dice

@F3K Total :
Very nice solution.

Just a small note.
It requires some "helper" cells: A1:A30 (or less/more). (But you can use them for storing other things.)
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 9887
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: [SOLVED]Trying to roll X number of Y sided dice

Thank you for your help
Win10 OpenOffice 4.1.9
Galarandir

Posts: 2
Joined: Fri Apr 30, 2021 11:40 pm

Return to Calc

### Who is online

Users browsing this forum: No registered users and 23 guests