User talk:Betsumei/Character Picker
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