Forum Discussion

wyoung1969's avatar
wyoung1969
Copper Contributor
Jan 28, 2020

Trivia Score Sheet

What I'm trying to accomplish is having the letter  w   to represent a word, but also having a numerical value with it like   6w      But here is the tricky part of it, I need to have that numerical ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 28, 2020

wyoung1969 

Never deal with Trivia. Yes, it's better to separate but if combined value is the rule of the game it's important to know what is the logic behind such combination. If always only one letter after the number like here

we may sum numbers with

=SUMPRODUCT((RIGHT($B$2:$G$2)=$A5)*LEFT($B$2:$G$2,LEN($B$2:$G$2)-1))

For other logic it could be another approach.

  • wyoung1969's avatar
    wyoung1969
    Copper Contributor
    Jan 28, 2020

    The Idea behind this:  Is there is a Word for the day, that if the team goes to the website and get the Word for the day.   Then they can use this word for a Mulligan for a question that they don't know the answer to and get the points that they bet.  It can be used only 1 time for the entire of the Game.  So each team has 1 Free pass or Mulligan for game night.  

     

    There can be up to 50 teams, and there are 6 rounds with 3 questions per round, not including Halftime and Wager Question and sometimes Bonus Question.

     

    I am trying to label the points numerical value with a   w    to let me know that they used their word for the day.  Also, having the that Cell Turn Yellow, after I hit Tab or Enter.  

     

    Now having said that, I need the points (numbers)  for each to team to be added up in the sub total cell

     

    Please see the table below

     

    Team Names  Question 1Question 2Question 3 Sub Total
          
    Just guessing135W 9
    Britganders531(1) 8
    Scooby31w5 9
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 29, 2020

      wyoung1969 , thank you.

       

      It's unclear what to do if you have any other text after the number except w. Like in case of 1(1). If ignore as in the sample

      formula could be

      =SUMPRODUCT(1*IFERROR(SUBSTITUTE(LOWER(C4:E4),"w","")+0,0))

      You may also highlight cells with w by conditional formatting rule with formula

      =RIGHT(C4)="w"
      • wyoung1969's avatar
        wyoung1969
        Copper Contributor
        Jan 29, 2020

        Ok, now on the formula, is there a way to do a mast input it in columns G, from rows  4 - 50 

Resources