User talk:Betsumei/Character Picker

From FBSA Wiki
Revision as of 06:09, 22 September 2021 by Huang3721 (talk | contribs) (adding color for clarity)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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)
You only need a single zero, for example:
  A B
1 Lookup Odds
2 =IF(ISERR(A1+B1),0,A1+B1) =E2/$E$5
3 =IF(ISERR(A2+B2),0,A2+B2) =E3/$E$5
4 =IF(ISERR(A3+B3),0,A3+B3) =E4/$E$5
Alternative (and simpler) version without IF and ISERR looks like this:
  A B
1 Lookup Odds
2 0 =E2/$E$5
3 =A2+B2 =E3/$E$5
4 =A3+B3 =E4/$E$5
Huang3721 (talk) 06:09, 22 September 2021 (UTC)