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

Discuss the spreadsheet application

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

Postby Galarandir » Fri Apr 30, 2021 11:58 pm

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 1164 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

Postby RusselB » Sat May 01, 2021 6:20 am

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
RusselB
Moderator
 
Posts: 6445
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Trying to roll X number of Y sided dice

Postby Zizi64 » Sat May 01, 2021 9:30 am

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 42 times


 Edit: Edited: I just fixed some glitches: 


DiceSum_fixed.ods
(13.86 KiB) Downloaded 42 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.
User avatar
Zizi64
Volunteer
 
Posts: 9941
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Trying to roll X number of Y sided dice

Postby F3K Total » Sat May 01, 2021 10:05 am

Hello,
found a solution without helper-cells, using array formulas.
Attachments
DICE_SUM_ARRAY.ods
(9.41 KiB) Downloaded 50 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

Postby Zizi64 » Sat May 01, 2021 10:37 am

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

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

Postby Galarandir » Mon May 03, 2021 8:44 am

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 15 guests