Who wants to talk countif() tags? - Page 3

fnord_too · #21 08-17-2006, 10:57 PM

[ QUOTE ]
[ QUOTE ]

[ 1 ] amplify voted for durron597 
[ 2 ] BobOjedaFan voted for KyrosMother
[ 0 ] Chuckleslovakian voted for BobOjedaFan
[ 6 ] durron597  voted for sighing
[ 4 ] fnord_too  voted for Tokyo!!
[ 0 ] GetThere1Time voted for fnord_too 
[ 0 ] Jonsan voted for fnord_too 
[ 0 ] kyro voted for durron597 
[ 1 ] KyrosMother voted for durron597 
[ 0 ] Nicholasp27 voted for sighing
[ 0 ] Our House voted for fnord_too 
[ 0 ] pudley4 voted for fnord_too 
[ 1 ] Shortline99 voted for durron597 
[ 3 ] sighing voted for BobOjedaFan
[ 0 ] Thebram voted for durron597 
[ 1 ] Tokyo!! voted for amplify
[ 0 ] traz voted for
[ 0 ] VoraciousReader voted for Shortline99
[ 0 ] wdcbooks voted for durron597 
[ 0 ] WillMagic voted for sighing
[ 0 ] xorbie voted for

[/ QUOTE ]

[/ QUOTE ]

Outstanding!! (Except all the votes on me!)

Stephen H · #22 08-17-2006, 11:05 PM

You should really see the format from a turbo this morning, too...it was something like:

[3] Stephen H - fnord_too

You get the basic idea. Pretty clean; all votes were italicized, leading vote getter in red & bold, 2nd place in blue & bold. No colors on the votes at all. I really liked it.

soah · #23 08-18-2006, 12:15 AM

[ 1 ] amplify - - durron597 
[ 2 ] BobOjedaFan - - KyrosMother
[ 0 ] Chuckleslovakian - - BobOjedaFan
[ 6 ] durron597  - - sighing
[ 4 ] fnord_too  - - Tokyo!!
[ 0 ] GetThere1Time - - fnord_too 
[ 0 ] Jonsan - - fnord_too 
[ 0 ] kyro - - durron597 
[ 1 ] KyrosMother - - durron597 
[ 0 ] Nicholasp27 - - sighing
[ 0 ] Our House - - fnord_too 
[ 0 ] pudley4 - - fnord_too 
[ 1 ] Shortline99 - - durron597 
[ 3 ] sighing - - BobOjedaFan
[ 0 ] Thebram - - durron597 
[ 1 ] Tokyo!! - - amplify
[ 0 ] traz - -
[ 0 ] VoraciousReader - - Shortline99
[ 0 ] wdcbooks - - durron597 
[ 0 ] WillMagic - - sighing
[ 0 ] xorbie - -

Removing "voted for" seems to help a lot. Not sure about the italics.

eviljeff · #24 08-18-2006, 12:56 AM

whoa woah woha waho

dash space dash?

Stephen H · #25 08-18-2006, 12:58 AM

You're not getting your own Morse Code string instead of dash space dash, either.

Stephen H · #26 08-18-2006, 02:33 AM

[ 0 ] Amplify - - BobOjedaFan
[ 1 ] BobOjedaFan - - durron597 
[ 0 ] Chuckleslovakian - - durron597 
 [ 3 ] durron597 - - fnord_too
[ 1 ] fnord_too - - durron597 
[ 0 ] GetThere1Time - - jonsan
[ 1 ] Jonsan - - kyro 
 [ 2 ] kyro - -
[ 0 ] KyrosMother - - kyro 

Here it is without the italics. I think I like the italics, though.
I'll spend a little time porting it to Excel (mine's done in 1-2-3) and then if there's interest I could make my code available.
I assume several people know how to make this work already, but I know I saw at least one request during a game for details. Also, I have nightmares that

[ QUOTE ]
I don't know exactly what you're doing, but can't you take the max of all the votecounts and stick it in a cell, and then compare the max to the individual votecount for each line to set the conditional formatting? (been a while since i screwed around with Excel)

[/ QUOTE ]
is actually what he's doing. I'm pretty sure you don't need to save any intermediate steps into a side cell for any of this garbage. I'm also pretty sure that you don't need to use vlookup, fun as vlookup sounds.

[ 0 ] Amplify - - BobOjedaFan
[ 1 ] BobOjedaFan - - durron597 
[ 0 ] Chuckleslovakian - - durron597 
 [ 3 ] durron597 - - fnord_too
[ 1 ] fnord_too - - durron597 
[ 0 ] GetThere1Time - - jonsan
[ 1 ] Jonsan - - kyro 
 [ 2 ] kyro - -
[ 0 ] KyrosMother - - kyro 

Is the excel version, with italics. I got around to fixing that before I clicked submit on this post.
I'll put my excel code in a seperate post for those that would like it.

Stephen H · #27 08-18-2006, 03:02 AM

My spreadsheet uses columns A-L; several of which are just plain constant text.
For ease of reading, I've also named several ranges and used the range names in the formulas. This has a few other advantages; first, you don't have to worry about the right $ing; anything that needs a $ is a named range. Second, as the number of players change, you just rename the range. I'm afraid that some of these functions, such as index and match, give unpredictable results when there are rows of blank lines at the bottom of the range (Not sure if this problem is in excel, but it's there in lotus). The ranges are named:

$C$1-$C$9: votecounts
$E$1-$E$9: players
$J$1-$J$9: votes

Feel free to avoid using named ranges and just substitute the above ranges wherever the named range appears. And replace 9 with however many players you've got.

I've also had to avoid getting my UBB tags consumed by the board. I've used a { instead of a [, and a } instead of a ], in all cases. Simply replace all instances of { and } with [ and ].
And now, what goes in each column.

A: An if statement to produce tags for colorizing.
=IF(C1=MAX(votecount),"{color:red{}b}",IF(C1=LARGE (votecount,2),"{color:blue}{b}",""))

B: Just the plaintext bracket for surrounding the votecount.
[

C: The standard countif()
=COUNTIF(votes,E1)

D: The other side of the bracket around the vote
]

E: A text list of players. Each row should have an individual player name.
Player

F: The end of the colorization tags for the name. Yes, you could just always put close tags, but why not make it cleaner?
=IF(C1=MAX(votecount),"{/b}{/color}",IF(C1=LARGE(votecount,2),"{/b}{/color},"")

G: The seperator between player name and vote. If using a dash, lead it with a ' so that excel knows it's text, not math.
'- -

H: Colorizing the vote name. To avoid having match() barf on a null vote, I've had to conditional out that possibility.
=IF(J1<>"",IF(INDEX(votecount,MATCH(J1,playe rs,0))=MAX(votecount),"{color:red}{b}",IF(INDEX(vo tecount,MATCH(J1,players,0))=LARGE(votecount,2),"{ color:blue}{b}","")),"")

I: The italics tag for around the vote.
{i}

J: The vote. Typeover as needed. Must, of course, match the way the player is listed in the playerlist for countif() to pick it up.
VoteName

K: The close italics tag.
{/i}

L: The closing color/bold tags for the vote. Same reasoning as above.
=IF(J1<>"",IF(INDEX(votecount,MATCH(J1,playe rs,0))=MAX(votecount),"{/b}{/color}",IF(INDEX(votecount,MATCH(J1,players,0))=LA RGE(votecount,2),"{/b}{/color","")),"")

There may very well be cleaner ways to do this. If someone comes along and says "Oh, you could do such and such and it would be better" then you might want to listen to them. I don't claim to be an Excel master these days.

You can put these formulas in row 1, then copy them down for however many players you have. Be sure to name the correct ranges for this to work.
When you want to post a countif(), just highlight from cell A1 to L9 (or however many players), copy, then paste into a post. It should appear just as above.

A side effect of using a sheet like this - it's harder, as mod, to determine at a glace at the sheet what's going on. It's very cluttered with tags and whatnot.

I think that in long games it would be very nice to have these; anything over 13 and the countif() becomes hard to read. Turbos are probably still good with the normal, uncolored version. Heck, when the votes are all 1's and 2's, you almost don't want all the red and blue cluttering things up.

ScottHoward · #28 08-18-2006, 10:06 AM

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]

[ 1 ] amplify voted for durron597 
[ 2 ] BobOjedaFan voted for KyrosMother
[ 0 ] Chuckleslovakian voted for BobOjedaFan
[ 6 ] durron597  voted for sighing
[ 4 ] fnord_too  voted for Tokyo!!
[ 0 ] GetThere1Time voted for fnord_too 
[ 0 ] Jonsan voted for fnord_too 
[ 0 ] kyro voted for durron597 
[ 1 ] KyrosMother voted for durron597 
[ 0 ] Nicholasp27 voted for sighing
[ 0 ] Our House voted for fnord_too 
[ 0 ] pudley4 voted for fnord_too 
[ 1 ] Shortline99 voted for durron597 
[ 3 ] sighing voted for BobOjedaFan
[ 0 ] Thebram voted for durron597 
[ 1 ] Tokyo!! voted for amplify
[ 0 ] traz voted for
[ 0 ] VoraciousReader voted for Shortline99
[ 0 ] wdcbooks voted for durron597 
[ 0 ] WillMagic voted for sighing
[ 0 ] xorbie voted for

[/ QUOTE ]

[/ QUOTE ]

Outstanding!! (Except all the votes on me!)

[/ QUOTE ]
yo DMB, this look familiar?

fnord_too · #29 08-18-2006, 10:38 AM

Check this out. Google spreadsheets, you can create one and share it, apparently. Good way to put these up for people to play with.