Jan 28 2020 12:38 PM
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 value to be add with the rest of the row. To give me the total sum. How do I do this?
Jan 28 2020 01:11 PM - edited Jan 28 2020 01:12 PM
Is your numerical value always a single digit?
Is your letter always a single letter?
Are they always sequenced NumberLetter, as in "6w" or are they sometimes "w6"?
How many columns of this kind of entry are in the row that you want to add up?
If it's always in the "6w" format (single digit, single letter) this formula =VALUE(LEFT(D3,1)) will return the value of the digit and could be used, in conjunction with others like it, =VALUE(LEFT(D3,1))+VALUE(LEFT(D4,1))+VALUE(LEFT(D5,1)).....
If it's less predictable or less consistent stuff you'll be dealing with, you'd probably be better off re-thinking the way you enter the data. Why not have separate cells for the numbers and the letters. Easy to add the numbers, and, frankly, easier to concatenate into the combinations, if that's really needed.
In other words, there's more to be defined here before we come up with a single "best solution"
Jan 28 2020 01:24 PM - edited Jan 28 2020 01:26 PM
Hello @wyoung1969,
I would have to agree with @mathetes. Perhaps you should rethink your method. Is there a reason that the numbers must be concatenated with the letters? Having numbers and text in separate columns would make this task much easier.
Maybe you could supply a sample file to help illustrate what exactly it is that you are trying to accomplish and why this is the method you are choosing.
Jan 28 2020 02:04 PM
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.
Jan 28 2020 10:44 PM
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 1 | Question 2 | Question 3 | Sub Total | |
Just guessing | 1 | 3 | 5W | 9 | |
Britganders | 5 | 3 | 1(1) | 8 | |
Scooby | 3 | 1w | 5 | 9 |
Jan 29 2020 12:02 AM
@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"
Jan 29 2020 12:24 AM
@Sergei Baklan The 1(1) you refer to is in when they bid the points but don't get the answer correct.
The only time that the W would be used is when they don't know the answer and they want free points for that question.
Jan 29 2020 12:54 AM
Jan 29 2020 01:21 AM
Yes, with new question it's better to start new conversation
Jan 29 2020 01:35 PM
Ok, now on the formula, is there a way to do a mast input it in columns G, from rows 4 - 50
Jan 29 2020 01:44 PM
Copy/paste the formula into G4 and drag this cell down till G50. Or, after the formula is pasted, select G4:G50 and Ctrl+D.