User talk:Betsumei/Character Picker: Difference between revisions

From FBSA Wiki
Jump to navigation Jump to search
(Suggestion)
 
No edit summary
Line 138: Line 138:


[[User:Huang3721|Huang3721]] ([[User talk:Huang3721|talk]]) 12:51, 21 September 2021 (UTC)
[[User:Huang3721|Huang3721]] ([[User talk:Huang3721|talk]]) 12:51, 21 September 2021 (UTC)
: Thanks! I'll have to give that a try when I've got a minute. [[User:Betsumei|Betsumei]] ([[User talk:Betsumei|talk]]) 15:56, 21 September 2021 (UTC)

Revision as of 15:56, 21 September 2021

Lookup and Odds Formula

I tried your character picker, and the result is peculiar. Character 1 showed up more often than character 2, although I expected the opposite. Also, the formula returns #N/A whenever RAND() produces numbers below 0.0120.

  A B C D E
1 Lookup Odds Name Level Weight
2 0.01 0.01 Character 1 50 1
3 0.57 0.55 Character 2 5 46
4 1 0.43 Character 3 15 36
5   1     83
6          
7 Selection is: Character 1      

I made a new table to check your VLOOKUP formula, and this is what I got:

  A B
8 Random Input Vlookup result
9 RAND() VLOOKUP(A9, $A$2:$C$4, 3, TRUE)
10 0.00 #N/A
11 0.05 Character 1
12 0.1 Character 1
13 0.15 Character 1
14 0.2 Character 1
15 0.25 Character 1
16 0.3 Character 1
17 0.35 Character 1
18 0.4 Character 1
19 0.45 Character 1
20 0.5 Character 1
21 0.55 Character 1
22 0.6 Character 2
23 0.65 Character 2
24 0.7 Character 2
25 0.75 Character 2
26 0.8 Character 2
27 0.85 Character 2
28 0.9 Character 2
29 0.95 Character 2
30 1 Character 3

May I suggest changing A2 to 0 and changing A3 to A2 + B2? So, with x as a random number and no alteration to your VLOOKUP formula:

  • 0 < x < 0.0120, formula returns Character 1 (Probability 1%)
  • 0.012 <= x < 0.5663, formula returns Character 2 (Probability 55%)
  • 0.5663 <= x < 1, formula returns Characger 3. (Probability 43%)

By using IF and ISERR, each row is easier to copy:

  • IF(ISERR(Previous LOOKUP + Previous ODDS),0, Previous LOOKUP + Previous ODDS)

So, on the second row, the formula is =IF(ISERR(A1+B1),0,A1+B1). Since A1 and B1 are texts, adding them will generate error. Thus, ISERR returns TRUE and the whole formula returns 0.

The new table looks like this

  A B C D E
1 Lookup Odds Name Level Weight
2 0.00 0.01 Character 1 50 1
3 0.01 0.55 Character 2 5 46
4 0.57 0.43 Character 3 15 36
5   1     83

Huang3721 (talk) 12:51, 21 September 2021 (UTC)

Thanks! I'll have to give that a try when I've got a minute. Betsumei (talk) 15:56, 21 September 2021 (UTC)