SR4 Party Tracker: Excel help requested

SR4 Party Tracker: Excel help requested

phasmaphobic View Member Profile	Feb 11 2006, 11:04 PM Post #1
Moving Target Group: Members Posts: 169 Joined: 13-December 05 From: Portland, OR Member No.: 8,070	I'm working on creating a simple GM sheet for my weekly game. The sheet contains the attributes and skill and misc modifiers for all the characters so I can easily reference them. I want to put in a simple dice roller tool at the top of the sheet. The tool would use the following drop-down menus, most likely activated by Combo Boxes A: Character Name - this is pulled from the character info lists B: Type of Test (EX: "Perception: Hearing") - this is from a list of predefined common tests, and determines Attribute and Skill necessary C: Misc Mods - this is a drop-down list of numbers, ranging from -10 to 10, maybe more, applied as a dice pool mod. Then a button which, when pressed, rolls the dice, checks for hits and glitches, and gives the result. I'm fairly adept at excel, so working out the formulas is not proving to be much of a problem so far. The problem is mainly one of aesthetics: using the standard random functions in excel, the calculations occur EVERY TIME new data is entered. I want this one specific calculation to run ONLY when the button is pressed. Thoughts? Suggestions? Any and all help is appreciated.

TinkerGnome View Member Profile	Feb 11 2006, 11:24 PM Post #2
Dragon Group: Members Posts: 4,138 Joined: 10-June 03 From: Tennessee Member No.: 4,706	Explore this line of thought. It may or may not work. Hide the box where the random numbers are generated. Have the button press cause the randomization to occur and copy the numeric content of the box into a display box. This way, you get a random result only when you hit the button. Not sure how well it'll work, though.

phasmaphobic

Feb 11 2006, 11:29 PM

Post #3

Moving Target

Group: Members
Posts: 169
Joined: 13-December 05
From: Portland, OR
Member No.: 8,070

QUOTE (TinkerGnome)

Explore this line of thought. It may or may not work.

Hide the box where the random numbers are generated. Have the button press cause the randomization to occur and copy the numeric content of the box into a display box. This way, you get a random result only when you hit the button.

Not sure how well it'll work, though.

Do you know a formula for copying the contents of a cell into a display box?

TinkerGnome

Feb 12 2006, 12:06 AM

Post #4

Dragon

Group: Members
Posts: 4,138
Joined: 10-June 03
From: Tennessee
Member No.: 4,706

It's not a formula. You'll need to use a macro. This worked for me:

CODE

Sub display_random()
'
' display_random Macro
'
Range("A2").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

A2 holds the random number function and C2 is where I want it displayed. You should be able to freely change them.

phasmaphobic

Feb 12 2006, 12:10 AM

Post #5

Moving Target

Group: Members
Posts: 169
Joined: 13-December 05
From: Portland, OR
Member No.: 8,070

QUOTE (TinkerGnome)

It's not a formula. You'll need to use a macro. This worked for me:

CODE

Sub display_random()
'
' display_random Macro
'
Range("A2").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

A2 holds the random number function and C2 is where I want it displayed. You should be able to freely change them.

Looks good. Might be a way of doing it.

Now that I have my data decided in the boxes, I need a way to calculate "random number from 1 to 6, X number of times, count the 5s and 6s"

phasmaphobic View Member Profile	Feb 12 2006, 12:33 AM Post #6
Moving Target Group: Members Posts: 169 Joined: 13-December 05 From: Portland, OR Member No.: 8,070	I've got all the variables in place and harvesting accordingly, now all I need is the formula. I need a formula that finds a random number X times, and then counts the number of times Y and Z occur. Hmmmm...

TinkerGnome View Member Profile	Feb 12 2006, 12:40 AM Post #7
Dragon Group: Members Posts: 4,138 Joined: 10-June 03 From: Tennessee Member No.: 4,706	Here's the hard way to do it: Clicky

phasmaphobic

Feb 12 2006, 12:50 AM

Post #8

Moving Target

Group: Members
Posts: 169
Joined: 13-December 05
From: Portland, OR
Member No.: 8,070

QUOTE (TinkerGnome @ Feb 11 2006, 04:40 PM)

Here's the hard way to do it:

Clicky

Wow, that's intense! Hahah.

On the "hidden dice sheet" there's a whole buttload of "#NAME?" errors all starting in column B.

Which leads me to believe that for some reason my randbetween function is not working. WTF...

EDIT: Doh. New install of excel, didn't have the add-ins enabled.

TinkerGnome View Member Profile	Feb 12 2006, 12:51 AM Post #9
Dragon Group: Members Posts: 4,138 Joined: 10-June 03 From: Tennessee Member No.: 4,706	Click tools->add-ins and enable the Analysis ToolPak. That'll fix the "#NAME?" errors. You need it because I'm lazy and was running RANDBETWEEN functions. EDIT: Yeah. That. I found an addin that is supposed to roll dice, but it only returns a solid result. Ie, you can get it to roll 3d6, but it'd just give you the sum.

phasmaphobic

Feb 12 2006, 12:54 AM

Post #10

Moving Target

Group: Members
Posts: 169
Joined: 13-December 05
From: Portland, OR
Member No.: 8,070

QUOTE (TinkerGnome)

Click tools->add-ins and enable the Analysis ToolPak. That'll fix the "#NAME?" errors. You need it because I'm lazy and was running RANDBETWEEN functions.

Beat ya to it =)

Now, to add Glitches. This should be fun...

You know, there HAS to be a simpler way to do this.

TinkerGnome

Feb 12 2006, 12:56 AM

Post #11

Dragon

Group: Members
Posts: 4,138
Joined: 10-June 03
From: Tennessee
Member No.: 4,706

QUOTE (phasmaphobic)

Now, to add Glitches.

Glitches aren't bad, actually. Just do a countif 1 and then do a comparison between hits and the result of that.

Edge is going to be the killer.

TinkerGnome

Feb 12 2006, 11:21 AM

Post #12

Dragon

Group: Members
Posts: 4,138
Joined: 10-June 03
From: Tennessee
Member No.: 4,706

The A+ answer is to do it all in a macro. This one works:

CODE

Sub roll_dice()
'
' roll_dice Macro
'
' You can change the following cells:
' F1 - Where you want the results to go
' C1 - The cell containing the number of edge dice to be rolled
' D1 - The cell containing the total number of dice to roll
'
[F1:F1].Value = ""
ones = 0
hits = 0
edge = 0

If [C1:C1].Value <> 0 Then
edge = 1
End If

For r = 0 To ([D1:D1].Value - 1)
newdie = Int(6 * Rnd) + 1
If newdie < 2 Then
ones = ones + 1
End If
If newdie > 4 Then
hits = hits + 1
[F1:F1].Value = [F1:F1].Value & " [" & newdie & "]"
Else: [F1:F1].Value = [F1:F1].Value & " " & newdie
End If
If edge <> 0 Then
If newdie > 5 Then
r = r - 1
End If
End If
Next r

[F1:F1].Value = [F1:F1].Value & " Hits:" & hits

gThreshold = [D1:D1].Value / 2

If ones >= gThreshold Then
If hits < 1 Then
[F1:F1].Value = [F1:F1].Value & " Critical Glitch!"
Else
[F1:F1].Value = [F1:F1].Value & " Glitch!"
End If
End If
End Sub

I had a little trouble using cell names as references, so you'll need to do a find and replace to change the cell values to what you need them to be.

phasmaphobic View Member Profile	Feb 14 2006, 09:01 PM Post #13
Moving Target Group: Members Posts: 169 Joined: 13-December 05 From: Portland, OR Member No.: 8,070	After a bit of work, I've got it mostly worked out, including Edge, Glitches, Specializations, and more, with only one (unnecessary) macro that hits F9 (recalc sheet) when the "roll" button is pressed.. The problem with the original macro that copied the value, is that it copied the value before the recalculation, and the glitch report I had wasn't up to par with that. Anyway, if you wanna see the excel sheet, I'll email it to you. Be warned, I use a different set of rules options: 1. Hits on 4, 5, and 6 2. Edge adds itself as direct hits, and additionally opens the rest of the dice to the rule of 6 3. Edge dice do not glitch 4. We're using a fantasy setting =) EDIT: Here's a link to the file: CLIKY CLICKY

phasmaphobic

Feb 14 2006, 09:10 PM

Post #14

Moving Target

Group: Members
Posts: 169
Joined: 13-December 05
From: Portland, OR
Member No.: 8,070

QUOTE (TinkerGnome)

The A+ answer is to do it all in a macro. This one works:

CODE

Sub roll_dice()
'
' roll_dice Macro
'
' You can change the following cells:
' F1 - Where you want the results to go
' C1 - The cell containing the number of edge dice to be rolled
' D1 - The cell containing the total number of dice to roll
'
[F1:F1].Value = ""
ones = 0
hits = 0
edge = 0

If [C1:C1].Value <> 0 Then
edge = 1
End If

For r = 0 To ([D1:D1].Value - 1)
newdie = Int(6 * Rnd) + 1
If newdie < 2 Then
ones = ones + 1
End If
If newdie > 4 Then
hits = hits + 1
[F1:F1].Value = [F1:F1].Value & " [" & newdie & "]"
Else: [F1:F1].Value = [F1:F1].Value & " " & newdie
End If
If edge <> 0 Then
If newdie > 5 Then
r = r - 1
End If
End If
Next r

[F1:F1].Value = [F1:F1].Value & " Hits:" & hits

gThreshold = [D1:D1].Value / 2

If ones >= gThreshold Then
If hits < 1 Then
[F1:F1].Value = [F1:F1].Value & " Critical Glitch!"
Else
[F1:F1].Value = [F1:F1].Value & " Glitch!"
End If
End If
End Sub

I had a little trouble using cell names as references, so you'll need to do a find and replace to change the cell values to what you need them to be.

Can you whip up a file with this as an example?

TinkerGnome View Member Profile	Feb 15 2006, 12:54 AM Post #15
Dragon Group: Members Posts: 4,138 Joined: 10-June 03 From: Tennessee Member No.: 4,706	example

phasmaphobic

Feb 15 2006, 11:48 PM

Post #16

Moving Target

Group: Members
Posts: 169
Joined: 13-December 05
From: Portland, OR
Member No.: 8,070

QUOTE (TinkerGnome)

example

Thanks!

This helps immensely

« Next Oldest · Community Projects · Next Newest »