User talk:Betsumei/Character Picker: Difference between revisions

From FBSA Wiki
Jump to navigation Jump to search
(Suggestion)
 
m (adding color for clarity)
 
(One intermediate revision by one other user not shown)
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)
:: You only need a single zero, for example:
:: {| class="wikitable"
|- 
!   !! A !! B
|-
! 1
| '''Lookup''' || '''Odds'''
|-
! 2
|  =IF(<span style="color:#FF0000">'''ISERR(A1+B1)'''</span>,<span style="color:#2a4b8d">'''0'''</span>,<span style="color:#ac6600">'''A1+B1'''</span>) || =E2/$E$5
|-
! 3
|  =IF(<span style="color:#FF0000">'''ISERR(A2+B2)'''</span>,<span style="color:#2a4b8d">'''0'''</span>,<span style="color:#ac6600">'''A2+B2'''</span>)  || =E3/$E$5
|-
! 4
|  =IF(<span style="color:#FF0000">'''ISERR(A3+B3)'''</span>,<span style="color:#2a4b8d">'''0'''</span>,<span style="color:#ac6600">'''A3+B3'''</span>)  || =E4/$E$5
|}
:: Alternative (and simpler) version without IF and ISERR looks like this:
:: {| class="wikitable"
|- 
! &nbsp; !! A !! B
|-
! 1
| '''Lookup''' || '''Odds'''
|-
! 2
|  <span style="color:#2a4b8d">'''0'''</span> || =E2/$E$5
|-
! 3
|  =<span style="color:#ac6600">'''A2+B2'''</span>  || =E3/$E$5
|-
! 4
|  =<span style="color:#ac6600">'''A3+B3'''</span>  || =E4/$E$5
|}
:: [[User:Huang3721|Huang3721]] ([[User talk:Huang3721|talk]]) 06:09, 22 September 2021 (UTC)

Latest revision as of 06:09, 22 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)
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)