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

Discuss the spreadsheet application
Post Reply
Galarandir
Posts: 2
Joined: Fri Apr 30, 2021 11:40 pm

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

Post by Galarandir »

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
dicesample.png (4.27 KiB) Viewed 1499 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
User avatar
RusselB
Moderator
Posts: 6597
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Trying to roll X number of Y sided dice

Post by RusselB »

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.
User avatar
Zizi64
Volunteer
Posts: 10660
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Trying to roll X number of Y sided dice

Post by Zizi64 »

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 83 times
 Edit: Edited: I just fixed some glitches: 
DiceSum_fixed.ods
(13.86 KiB) Downloaded 81 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.3.5;AOO4.1.13
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.
F3K Total
Volunteer
Posts: 1012
Joined: Fri Dec 16, 2011 8:20 pm

Re: Trying to roll X number of Y sided dice

Post by F3K Total »

Hello,
found a solution without helper-cells, using array formulas.
Attachments
DICE_SUM_ARRAY.ods
(9.41 KiB) Downloaded 85 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
User avatar
Zizi64
Volunteer
Posts: 10660
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Trying to roll X number of Y sided dice

Post by Zizi64 »

@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.3.5;AOO4.1.13
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.
Galarandir
Posts: 2
Joined: Fri Apr 30, 2021 11:40 pm

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

Post by Galarandir »

Thank you for your help
Win10 OpenOffice 4.1.9
Post Reply