User:Betsumei/Character Picker: Difference between revisions

From FBSA Wiki
Jump to navigation Jump to search
(Adding (what I hope) is what Huang3721 suggested. Unless I misunderstood, which I do tend to do.)
(Simplifying per Huang3721)
 
Line 13: Line 13:
|-
|-
! 2  
! 2  
|  =if(iferror(A1+B1,0),A1+B1,0) || =E2/$E$5<ref name="Sum">Replace <kbd>$E$5</kbd> with the cell reference to the sum of the weights.</ref> || Character 1 || 50 || =MAX((51-D2),1)
|  =if(iserror(A1+B1),0,A1+B1) || =E2/$E$5<ref name="Sum">Replace <kbd>$E$5</kbd> with the cell reference to the sum of the weights.</ref> || Character 1 || 50 || =MAX((51-D2),1)
|-
|-
! 3  
! 3  
|  =if(iferror(A2+B2,0),A2+B2,0) || =E3/$E$5<ref name="Sum" /> || Character 2 || 5 || =MAX((51-D3),1)
|  =if(iserror(A2+B2),0,A2+B2) || =E3/$E$5<ref name="Sum" /> || Character 2 || 5 || =MAX((51-D3),1)
|-
|-
! 4
! 4
|  =if(iferror(A3+B3,0),A3+B3,0) || =E4/$E$5<ref name="Sum" /> || Character 3 || 15 || =MAX((51-D4),1)
|  =if(iserror(A3+B3),0,A3+B3) || =E4/$E$5<ref name="Sum" /> || Character 3 || 15 || =MAX((51-D4),1)
|-
|-
! 5  
! 5  

Latest revision as of 15:16, 22 September 2021

If you're like me, you've got a whole lot of alts and not a whole lot of decision making ability. Sure, you could roll dice to see whose turn it is to get played today, but that's too random; maybe you want to make it a weighted chance?

Good news! It's possible with just your spreadsheet application of choice and a minimum of data entry.

Now with improvements suggested by User:Huang3721 that make it less broken!

  A B C D E
1 Lookup Odds Name Level Weight
2 =if(iserror(A1+B1),0,A1+B1) =E2/$E$5[1] Character 1 50 =MAX((51-D2),1)
3 =if(iserror(A2+B2),0,A2+B2) =E3/$E$5[1] Character 2 5 =MAX((51-D3),1)
4 =if(iserror(A3+B3),0,A3+B3) =E4/$E$5[1] Character 3 15 =MAX((51-D4),1)
5   =SUM(B2:B4)[2]     =SUM(E2:E4)[3]
6          
7 Selection is: =VLOOKUP(rand(),$A$2:$C$16,3,true)[4]      

Just add as many lines fashioned after rows 3-4 as you need (row 2 is a bit unique because there's no data above it), and the VLOOKUP function will pick a character for you. Use whatever formula you want in column E to determine the relative weights of characters; I chose to make the weight based on how many levels they have to gain.

  1. 1.0 1.1 1.2 Replace $E$5 with the cell reference to the sum of the weights.
  2. This isn't strictly necessary, but if it comes up with an answer other than 100% something is wrong.
  3. Edit this formula's range to include all the weights.
  4. Replace $C$16 with the cell reference to the last entry in the name column.