Yeah, I thought it was about time to try another phalla, and maybe even run one this time around. Plus I am a student now so no pesky work getting in the way which is convenient!
If anyone has some free time so I can bounce some ideas past them for a phalla I am hoping to run in the next month or so (if the mechanics work...) please send me a PM!
I have never actually run one before so I am slightly concerned it will be bad. :<
0
Options
38thDoelets never be stupid againwait lets always be stupid foreverRegistered Userregular
I'm super excited for this phalla! Thanks for volunteering to host, 38th Defender of Earth!
+1
Options
H3KnucklesBut we decide which is rightand which is an illusion.Registered Userregular
Excel question for people who know Excel:
I want a formula to check whether the text in a cell at A2 appears in range E$2:E$39, and for each match, check whether the cell in F at the same row as the match is empty [if true for all matches, return "no"] or not [then check whether the cell in K at the same row as the match is empty (return "yes") or not (return "?")].
I know nothing about how to implement VBA or macros in Excel, so please be patient with me if it's necessary to do what I'm trying to accomplish. It might take a while for me to be able to verify the solution.
So the basic command you want to use is Vlookup, which has the format:
VLOOKUP(
lookup value (eg $A$2),
range containing the lookup value (e.g. E$2:[b]F[/b]$39),
the column number in the range containing the return value (relative to the column you're looking at, so 2), and
optionally specify TRUE for approximate match or FALSE for an exact match (only for numbers, not text strings)
)
This will return the value in F, at which point you'd just do an if statement for the processing. Unfortunately, this only finds the first result, so I'd need to do some googling to help for more results
EDIT: wait, no, this is going about this the wrong way. What you'd instead want to do is create a dummy column or two and do a bunch of nested ifs. One to check if K-whatever matches $A$2, Another to see if it matches E-whatever, and then a combined if statement to do the logic from there.
I'm not sure, is your expected result a single value, or a column vector?
Spoit on
+1
Options
H3KnucklesBut we decide which is rightand which is an illusion.Registered Userregular
edited February 2017
@Spoit Thanks for helping, I wasn't expecting an answer until sometime during the North American workday.
Basically, I made a per-day voting spreadsheet. Column A is the names of the players. Column B is the current tally of votes for them. Column C (where I'm trying to come up with a formula) is whether they have voted or not that day ("yes", "no", or "?" for dodgy results). Column E is a chronological listing of voters, where column F is who they voted for. Column G is where I move cancelled votes (leaving F at that row as a blank "").
Here's where it gets tricky. Column i checks if a voter has voted more than once, and returns their name on each row they have a vote; any rows where I've cancelled their vote, it updates to return "cancelled". Since anything else leaves this column blank, this makes it quick and easy to spot where the tally may be getting fudged. Similarly, since several formulas on the sheet need names spelled a certain way, I made column J return any name entered into the voter's column (E) that doesn't match anything in the player list (A), so if I typo something it immediately grabs my attention. I did the same thing in column K for making sure entries in the votes column (F) are correct.
What I want, is to make that column C formula tell me if a player has a valid vote in, by first finding any matches in the voter column (E), then checking if that matched vote hasn't been cancelled (does the corresponding F cell have a value besides ""), and if it does, to make sure that vote is for another player (by checking that the vote error column K has an empty cell "" on the same row). This way, smart-aleck votes for the host, or mistaken votes for a player who is in reserve or has been eliminated don't give a false-positive "yes" in the 'Voted?' column (C).
Honestly? As a host I'd just give it a pass if they put in a joke vote. Innactivus really isn't that big a problem, and it's pretty rare when someone is posting but not voting. And odds are if they make a habit of it, the other players will call it out anyway. I'd just set up a conditional formatting to highlight blank cells in a muted red, and leave it at that. EDIT: Or, like you said, you already have it highlight invalid votes, so you should notice that too
In terms of actually vote counting, I can't find any of my old excel sheets at the moment, but the way I did it was just have a big trashy single cell that I updated the votes as they happened on the left, and then used a function (I don't remember which), to just extract the first word in the cell into a new column. And then had countif add up the number of instances that the value for that row in the A column (with the player list) was in the votecount list.
Spoit on
0
Options
H3KnucklesBut we decide which is rightand which is an illusion.Registered Userregular
edited February 2017
Hm, I guess you're right. Any false-positive situation will stick out enough I can just check them as I go. This isn't really for hosting purposes anyways, just me trying to keep track of as much as I can.
The vote counting itself works fine for me since the only real catch is ties, but I can just check if the "winner's" tally matches anyone else's. The sheet has a cell with a formula to tell me the winner (using a nested INDEX(MODE(MATCH())) formula) at the 'bottom' of the Votes column (I put it low enough I can insert rows for extra votes as needed without mucking anything else on the sheet up; just gotta remember to drag-fill the I/J/K formulas).
IIRC, you might be able to use a gradient-ed conditional formatting to highlight the votes relative to their counts, but It's been a while so I forgot how to do it. Really, being able to use multiple conditional formatting options is really the main reason to use excel over google docs for vote counting
If you wanted to get really fancy, what you'd do is create a way to make a database of votes, reading off the voter, votee, and post number, and automagically fetch only the highest post number one from each voter. But then at that point, you'd want to just figure out a way to grab data directly from the thread. And then you're a wizard, like @Phyphor
This way, smart-aleck votes for the host ... don't give a false-positive "yes" in the 'Voted?' column (C).
Hey! My wincon says to kill the refs!
Or at least have the most points once the refs are dead.
Which is the same thing!
I'm updating my day one vote reason for you now.
(in my head)
Geez, I was just trying to think of examples where whether a vote counted might be questionable. I didn't mention you specifically (I also didn't think anyone would take offense at 'smart-aleck' but you and Spoit both seemed to take it more seriously than I meant it).
IIRC, you might be able to use a gradient-ed conditional formatting to highlight the votes relative to their counts, but It's been a while so I forgot how to do it. Really, being able to use multiple conditional formatting options is really the main reason to use excel over google docs for vote counting
Wait, you can make it change things like text color or cell background color based on formulas?
...Oh my god, my borderlands gear spreadsheet is going to be so much simpler once I've figured this out.
This way, smart-aleck votes for the host ... don't give a false-positive "yes" in the 'Voted?' column (C).
Hey! My wincon says to kill the refs!
Or at least have the most points once the refs are dead.
Which is the same thing!
I'm updating my day one vote reason for you now.
(in my head)
Geez, I was just trying to think of examples where whether a vote counted might be questionable. I didn't mention you specifically (I also didn't think anyone would take offense at 'smart-aleck' but you and Spoit both seemed to take it more seriously than I meant it).
It's all good.
I had just voted for the host in the current game, and then managed to also vote for you prior?
So was just jesting.
I broke up the formula into separate lines in a way that should make parsing it and comparing the repeated parts easier.
It does everything correctly, except answers that should be a "yes" or "?" always give the same result. If I make the test "=0" I always get the false return "?", whereas when I leave it "<>0" I always get the true return "yes". As far as I can tell, the ISBLANK test in the second line of "IF(SUM..." conditions isn't working right and always gives a false result which is then flipped to true by the NOT statement and increases the SUM result to be a non-zero value. What I don't get is that the upper line of this formula operates correctly. If the problem was that ISBLANK is testing the entire range and not just the rows where the associated SEARCH is true, the formula wouldn't produce correct outcomes for the upper line.
This is a bit of a hail mary that one of you guys can see what's going wrong here.
Is the target cell actually blank, or does it contain a formula?
Yeah, I think that's the problem. The K cell the second test is checking has a formula that returns an empty "" (if the vote cell in column F matches a player) or the text from the same-row cell in F (if the vote cell in column F doesn't match any of the active players). Exceljet says that the ISBLANK function should still regard that empty return as blank, but I'm on an old machine that only has Office 2003, so it might not work the same way for me (it has already caused a problem because one suggestion on the subreddit involved COUNTIFS that don't exist in this version). Edit: just tested and this is the problem.
I'm wondering if there isn't a simpler way to do this by just running a reverse-lookup of the F column's vote data for each match the first test finds against the player list in column A instead.
This is all totally unnecessary of course, but once I've got my mind on a puzzle it's hard to let it go.
Edit 2: The person in the subreddit thread didn't want to let it go either, and made the suggestion to replace the second ISBLANK(...) with a different function LEN(...)=0, and when I did that and made the parent SUM(...)=0, everything works now! Yay!
Wait, I think that I've misread your whole methodology here, which would make it a lot more complicated (though really, at this point we've all probably spent more time and energy than checking manually would have taken for a dozen games). How are you doing your vote column? Which ones are filled arithmetically, and which are manual? Are you just dumping them willy nilly into an unorganized list, chronologically? If so, try instead to just do the 'active vote' column manually, matching it to the voter in your earlier column (A?), where yeah, you can just leave a "" for people who cancel, but don't revote.
Wait, I think that I've misread your whole methodology here, which would make it a lot more complicated (though really, at this point we've all probably spent more time and energy than checking manually would have taken for a dozen games). How are you doing your vote column? Which ones are filled arithmetically, and which are manual? Are you just dumping them willy nilly into an unorganized list, chronologically? If so, try instead to just do the 'active vote' column manually, matching it to the voter in your earlier column (A?), where yeah, you can just leave a "" for people who cancel, but don't revote.
Sorry I didn't see this earlier. As I said in the edit to my previous post, the subreddit guy came through for me, so it's all good to go. It's been working real well for following the game, even after I got killed.
Each Day gets its own sheet:
Column A is all players alive that day (I only filled that out once, now if I need more sheets I just copy the last day's blank sheet a few times, and at the start of the day I delete the rows for dead players; the bonus is in games with resurrection this saves me from having to edit players back in)
Column B is a tally of the votes for that player (it's a formula counting times they're mentioned in column F)
Column C is whether or not the player has made a valid vote that day (a function that looks at E, F, & L)
Column E is where I enter the name of a voter (in the order they happen in the thread)
Column F is where I enter the name of the player they voted for (text is red to help me keep them straight)
Column G is where I record the subtotal of votes for that player at the time the vote is made (I just type in what B's value is after filling out F)
Column H is an odd one: when a vote is withdrawn, I copy it over to here (for reference) and clear it from F
So that gives me a chronological listing of votes that's fast and easy to enter as I'm reading through the thread, and at the end of it is a formula that gives me the vote 'winner'. As it is, if there's a tie it'll only tell me the one who had the earliest vote, but I just check their number in the tally column (B) and look to see if anyone else matched it. The last couple columns are weird formulas that exist to catch my attention if there's an error in the entries for E or F;
Column J checks if the voter's name in E has occurred more than once in that column, and displays the name on each line so I can easily find the old redacted votes and move them to H. If the earlier entries have had their F cell cleared already, the J cell for that line just says "withdrawn".
Column K just checks the voter's name against the list of players in column A. If there's a match, it shows nothing, otherwise it displays what I entered in E. So anytime this column shows something I know I done goofed.
Column L is basically doing the same thing with the votes; it checks the name from column F against the player list in column A, and only displays what I entered when there isn't a match. Sometimes this is an error on the voter's part (like voting for someone who's already dead), but it draws my attention to check.
I can post the formulas if you really want, but the important thing is everything's working. Thanks for all the help, but I think I've got it pretty well set up.
H3KnucklesBut we decide which is rightand which is an illusion.Registered Userregular
edited February 2017
@ObiFett I understand what all those words mean by themselves, but I don't understand the sentence you've constructed. Sounds useful, so I guess I should look that up.
Seriously though, I don't use Excel for work, so I've never had much cause to study it. I can put in functions once I know what they do, use the chart wizard, and that's kinda my upper limit.
ObiFett I understand what all those words mean by themselves, but I don't understand the sentence you've constructed. Sounds useful, so I guess I should look that up.
Step-by-Step
Select the range you want to have data validation. In your case, that would be E and F:
Choose List for the "Allow" field and then click on the Source button:
Select the list of names that should be allowed to count as votes. In your case it would be column A
The result is that now when you type a name it will give you an error if you type something that isn't in the source column (your case would be column A). You also get a nifty little dropdown selector.
Posts
:winky:
Hello!
I feel as if we listen to the same Podcasts...
I still have time to make this happen.
I have never actually run one before so I am slightly concerned it will be bad. :<
Nether Ball Game II is now scouting talent.
I want a formula to check whether the text in a cell at A2 appears in range E$2:E$39, and for each match, check whether the cell in F at the same row as the match is empty [if true for all matches, return "no"] or not [then check whether the cell in K at the same row as the match is empty (return "yes") or not (return "?")].
I know nothing about how to implement VBA or macros in Excel, so please be patient with me if it's necessary to do what I'm trying to accomplish. It might take a while for me to be able to verify the solution.
This will return the value in F, at which point you'd just do an if statement for the processing. Unfortunately, this only finds the first result, so I'd need to do some googling to help for more results
EDIT: wait, no, this is going about this the wrong way. What you'd instead want to do is create a dummy column or two and do a bunch of nested ifs. One to check if K-whatever matches $A$2, Another to see if it matches E-whatever, and then a combined if statement to do the logic from there.
I'm not sure, is your expected result a single value, or a column vector?
Basically, I made a per-day voting spreadsheet. Column A is the names of the players. Column B is the current tally of votes for them. Column C (where I'm trying to come up with a formula) is whether they have voted or not that day ("yes", "no", or "?" for dodgy results). Column E is a chronological listing of voters, where column F is who they voted for. Column G is where I move cancelled votes (leaving F at that row as a blank "").
Here's where it gets tricky. Column i checks if a voter has voted more than once, and returns their name on each row they have a vote; any rows where I've cancelled their vote, it updates to return "cancelled". Since anything else leaves this column blank, this makes it quick and easy to spot where the tally may be getting fudged. Similarly, since several formulas on the sheet need names spelled a certain way, I made column J return any name entered into the voter's column (E) that doesn't match anything in the player list (A), so if I typo something it immediately grabs my attention. I did the same thing in column K for making sure entries in the votes column (F) are correct.
What I want, is to make that column C formula tell me if a player has a valid vote in, by first finding any matches in the voter column (E), then checking if that matched vote hasn't been cancelled (does the corresponding F cell have a value besides ""), and if it does, to make sure that vote is for another player (by checking that the vote error column K has an empty cell "" on the same row). This way, smart-aleck votes for the host, or mistaken votes for a player who is in reserve or has been eliminated don't give a false-positive "yes" in the 'Voted?' column (C).
In terms of actually vote counting, I can't find any of my old excel sheets at the moment, but the way I did it was just have a big trashy single cell that I updated the votes as they happened on the left, and then used a function (I don't remember which), to just extract the first word in the cell into a new column. And then had countif add up the number of instances that the value for that row in the A column (with the player list) was in the votecount list.
The vote counting itself works fine for me since the only real catch is ties, but I can just check if the "winner's" tally matches anyone else's. The sheet has a cell with a formula to tell me the winner (using a nested INDEX(MODE(MATCH())) formula) at the 'bottom' of the Votes column (I put it low enough I can insert rows for extra votes as needed without mucking anything else on the sheet up; just gotta remember to drag-fill the I/J/K formulas).
Edit: Anyway, thanks for the input.
Hey! My wincon says to kill the refs!
Or at least have the most points once the refs are dead.
Which is the same thing!
I'm updating my day one vote reason for you now.
(in my head)
Sounds like we need a sondheim phalla.
Geez, I was just trying to think of examples where whether a vote counted might be questionable. I didn't mention you specifically (I also didn't think anyone would take offense at 'smart-aleck' but you and Spoit both seemed to take it more seriously than I meant it).
Wait, you can make it change things like text color or cell background color based on formulas?
...Oh my god, my borderlands gear spreadsheet is going to be so much simpler once I've figured this out.
It's all good.
I had just voted for the host in the current game, and then managed to also vote for you prior?
So was just jesting.
IF(SUM(--NOT(IF(ISNUMBER(SEARCH(A20,$E$2:$E$39)),ISBLANK($F$2:$F$39),1)))=0,"no",
IF(SUM(--NOT(IF(ISNUMBER(SEARCH(A20,$E$2:$E$39)),ISBLANK($K$2:$K$39),1)))<>0,"yes","?")
)
)
I broke up the formula into separate lines in a way that should make parsing it and comparing the repeated parts easier.
It does everything correctly, except answers that should be a "yes" or "?" always give the same result. If I make the test "=0" I always get the false return "?", whereas when I leave it "<>0" I always get the true return "yes". As far as I can tell, the ISBLANK test in the second line of "IF(SUM..." conditions isn't working right and always gives a false result which is then flipped to true by the NOT statement and increases the SUM result to be a non-zero value. What I don't get is that the upper line of this formula operates correctly. If the problem was that ISBLANK is testing the entire range and not just the rows where the associated SEARCH is true, the formula wouldn't produce correct outcomes for the upper line.
This is a bit of a hail mary that one of you guys can see what's going wrong here.
Switch Friend Code: SW-1406-1275-7906
The Monster Baru Cormorant - Seth Dickinson
Steam: Korvalain
=SUMIF($E$2:$E$32,D32,$F$2:$F$32)
D is vote name, E is who the person is voting for.
Yeah, I think that's the problem. The K cell the second test is checking has a formula that returns an empty "" (if the vote cell in column F matches a player) or the text from the same-row cell in F (if the vote cell in column F doesn't match any of the active players). Exceljet says that the ISBLANK function should still regard that empty return as blank, but I'm on an old machine that only has Office 2003, so it might not work the same way for me (it has already caused a problem because one suggestion on the subreddit involved COUNTIFS that don't exist in this version). Edit: just tested and this is the problem.
I'm wondering if there isn't a simpler way to do this by just running a reverse-lookup of the F column's vote data for each match the first test finds against the player list in column A instead.
This is all totally unnecessary of course, but once I've got my mind on a puzzle it's hard to let it go.
Edit 2: The person in the subreddit thread didn't want to let it go either, and made the suggestion to replace the second ISBLANK(...) with a different function LEN(...)=0, and when I did that and made the parent SUM(...)=0, everything works now! Yay!
hey, just bumping this request from last year
still applicable
Sorry I didn't see this earlier. As I said in the edit to my previous post, the subreddit guy came through for me, so it's all good to go. It's been working real well for following the game, even after I got killed.
Each Day gets its own sheet:
- Column A is all players alive that day (I only filled that out once, now if I need more sheets I just copy the last day's blank sheet a few times, and at the start of the day I delete the rows for dead players; the bonus is in games with resurrection this saves me from having to edit players back in)
- Column B is a tally of the votes for that player (it's a formula counting times they're mentioned in column F)
- Column C is whether or not the player has made a valid vote that day (a function that looks at E, F, & L)
- Column E is where I enter the name of a voter (in the order they happen in the thread)
- Column F is where I enter the name of the player they voted for (text is red to help me keep them straight)
- Column G is where I record the subtotal of votes for that player at the time the vote is made (I just type in what B's value is after filling out F)
- Column H is an odd one: when a vote is withdrawn, I copy it over to here (for reference) and clear it from F
So that gives me a chronological listing of votes that's fast and easy to enter as I'm reading through the thread, and at the end of it is a formula that gives me the vote 'winner'. As it is, if there's a tie it'll only tell me the one who had the earliest vote, but I just check their number in the tally column (B) and look to see if anyone else matched it. The last couple columns are weird formulas that exist to catch my attention if there's an error in the entries for E or F;I can post the formulas if you really want, but the important thing is everything's working. Thanks for all the help, but I think I've got it pretty well set up.
Everything alright?
Seriously though, I don't use Excel for work, so I've never had much cause to study it. I can put in functions once I know what they do, use the chart wizard, and that's kinda my upper limit.
Step-by-Step
Choose List for the "Allow" field and then click on the Source button:
Select the list of names that should be allowed to count as votes. In your case it would be column A
The result is that now when you type a name it will give you an error if you type something that isn't in the source column (your case would be column A). You also get a nifty little dropdown selector.
Sure does. Just go to the Data tab and find Data Validation