PDA

View Full Version : Yet Another Excel Thread

sterlingice
06-15-2005, 05:22 PM
For my video game thread, I've got an Excel spreadshee that looks something like this.
<table x:str="" style="border-collapse: collapse; table-layout: fixed; width: 380pt;" border="0" cellpadding="0" cellspacing="0" width="508"> <tbody><tr style="height: 11.25pt;" height="15"><td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Castlevania</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Vince</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Contra</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (2nd Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">NevStar</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Double Dragon</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (2nd Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">RainRaven</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Duck Hunt</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (2nd Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Celeval</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Excitebike</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Blade6119</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Final Fantasy</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Coffee Warlord</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Mega Man</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">kingfc22</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Metroid</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">SirFozzie</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Mike Tyson's Punch Out</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (2nd Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Wolfpack</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Ninja Gaiden</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (2nd Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Izulde</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Super Mario Bros</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Chas in Cinti</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Super Mario Bros 2</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Radii</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Super Mario Bros 3</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">Northwood_DK</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">The Legend of Zelda</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">terpkristin</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl25" style="height: 11.25pt;" height="15">NES</td> <td class="xl25">Tetris</td> <td class="xl25">2005.6.14</td> <td class="xl25">NES Classics (1st Ed)</td> <td class="xl25">Freebie</td> <td class="xl25">TazFTW</td></tr></tbody> </table>
I'm trying to create a separate sheet to tabulate scores, perhaps using COUNTIF, using a table like this:

Name
CoffeeWarlord 0 1 0 3

=COUNTIF(Games!F2:F204,A4)

But I need to verify two things- if the name is the same and if the rank of the question is the same (freebie, bronze, silver, gold). Is there an easy way to do this with countif or a similar command.

If that wasn't clear, I essentially, need to count up how many freebie, bronze, silver, and gold answers people made on a separate table.

SI
mhass
06-15-2005, 05:50 PM
If I get what you're asking, and I might not, I think you just need an And() function in your countif.

countif(range,and(name,level)) for each name/level combo

edit: now that I look at that, it won't work. Ignore me.
wade moore
06-15-2005, 06:56 PM
Unfortunately, I'm 99% sure you cannot do 'and' statements in excel... (without vbscript at least)...


What I would do is concatinate (sp?) the two in a 3rd field and use that in your countif...
FrogMan
06-15-2005, 07:42 PM
I had the same idea as mhass, but the "and" won't work because your range will have two columns.

I'm not sure if wade meant that we can't do "and" statement at all in excel, or only in the countif. It's possible to do "and" in excel, just not in that function.

I'd also use a concatenate(A2;B2) to create a new column that would have the player's name and the level of the question in one cell, then do the countif on four consecuive cells next to the name of the player in your other sheet...

FM
wade moore
06-15-2005, 07:46 PM
yeah, i meant within a function ;).
Wolfpack
06-15-2005, 08:10 PM
I'll concur with FrogMan. It's not the most pretty, but the concatenation is a quick way to come up with a total. Basically, just have a column on sheet two with everybody's name in it, then put in a formula like

"=COUNTIF(Sheet1!c1:c100,"=Freebie"&A1)"

where A1 is a player's name (in this example).
FrogMan
06-15-2005, 08:12 PM
NO NO NO!!! Forget what I said, my brain was turned off... 2 words

PIVOT TABLE!!!

But you'll need headers on your columns... After that, it'll do everything by itself...

Let me know if you need a walkthrough...

FM
Scarecrow
06-15-2005, 09:38 PM
Rule number one in excel:

When in Doubt - PIVOT TABLE!!!!!!
sterlingice
06-15-2005, 10:57 PM
Thanks for the help, guys. I have something new to look into tomorrow :D

SI
Mr. Wednesday
06-15-2005, 10:59 PM
Unfortunately, I'm 99% sure you cannot do 'and' statements in excel... (without vbscript at least)...You most certainly CAN do 'and' in Excel formulas -- it's a function. Same as or.
thealmighty
06-15-2005, 11:04 PM
Rule number one in excel:

When in Doubt - Frogman!!!!!!
Fixed that for ya.
sterlingice
06-15-2005, 11:07 PM
You most certainly CAN do 'and' in Excel formulas -- it's a function. Same as or.
Yes, you can do "and"- it's pretty common. But what I'm asking can't be set up with "and" in conjunction with "countif" at least in any incarnation I can think of. Basically, because you'd be asking excel to be both an iterator and something that's just a simple counting function.

SI
sterlingice
06-15-2005, 11:09 PM
Fixed that for ya. Very true. FM is an Excel deity and that's no slight on anyone else who has posted here- many of you have had lots of helpful stuff. I've seen his hattrick spreadsheet and and it's like heaven if the afterlife were designed by computer scientists and math majors who liked to play soccer sims.

SI
Mr. Wednesday
06-15-2005, 11:19 PM
Yes, you can do "and"- it's pretty common. But what I'm asking can't be set up with "and" in conjunction with "countif" at least in any incarnation I can think of. Basically, because you'd be asking excel to be both an iterator and something that's just a simple counting function.My usual way of handling that would be by using an auxiliary column in either the worksheet to be counted or a mirror sheet.
mhass
06-16-2005, 08:36 AM
My usual way of handling that would be by using an auxiliary column in either the worksheet to be counted or a mirror sheet.
That's what I was trying to say. Make a column for each combination to the right of your answers. Use If(And( [Name Answ] = [Name Col], [Ques Level] = [Ques Col]), 1, 0). Then Sum your columns at the bottom. Put those sums in your results table.
FrogMan
06-16-2005, 08:44 AM
PIVOT TABLE!!!!

:D

FM
PS: thanks for the kind words guys, appreciate it :)
wade moore
06-16-2005, 11:18 AM
What was that FM? ;)

Good point actually...
sterlingice
06-16-2005, 11:59 AM
These are a neat feature I've never messed with before so I've got a couple of questions.

<table border="1"><tbody><tr> <td>Count of Answer</td><td>Rank</td><td> </td><td> </td><td> </td></tr><tr> <td>Answer</td><td>Bronze</td><td>Gold</td><td>Silver</td><td>Grand Total</td></tr><tr> <td>Coffee Warlord</td><td> </td><td> </td><td>1</td><td>1</td></tr><tr> <td>N/A</td><td> </td><td>1</td><td> </td><td>1</td></tr><tr> <td>NevStar</td><td>1</td><td> </td><td> </td><td>1</td></tr><tr> <td>Northwood_DK</td><td>1</td><td> </td><td> </td><td>1</td></tr><tr> <td>SirFozzie</td><td> </td><td> </td><td>1</td><td>1</td></tr><tr> <td>terpkristin</td><td>1</td><td> </td><td> </td><td>1</td></tr><tr> <td>Grand Total</td><td>3</td><td>1</td><td>2</td><td>6</td></tr></tbody></table>

(I've decided to stop asking "Can I do this" = "Is there a way to do this in a way that doesn't result in a lot of pain")

1. Can I set the order of the columns above? I'd much rather it be "Bronze/Silver/Gold"

2. I can make a simple "total points" column based on the three columns. But what if I add a new type of trivia question to my contest and suddenly have "gold, silver, bronze, super" or something. When I update the pivot chart data, will the new "super" column wipe out the total points column?

3. Can I sort the list by that "total points" column so that it's ranked based on who has the most total points without having to do a "sort" every single day? Is there a built in way to do that with pivot tables?

SI
gottimd
06-16-2005, 12:03 PM
Try here (http://www.mrexcel.com/board2/)
FrogMan
06-16-2005, 12:06 PM
I've got excel in French here, so the terms may not be right...

1) right click on the column header, there should be a "order" in there, you can move the columns to where you want them

2) the pivot table will expand. Only problem is if you add some calculated formula to the right of your grand total column, that will be wiped out. Now I'm thinking, is your grand total automatically calculated by the pivot table or not? if not, there's a way to have the table do that. If it is, then it is part of the table and will expand if more columns are needed.

3) double click on the cell that reads "Answer" (should be grey) and click "advanced" you can set sorting in there...

FM
sterlingice
06-16-2005, 12:11 PM
I've got excel in French here, so the terms may not be right...

1) right click on the column header, there should be a "order" in there, you can move the columns to where you want them
Good deal. That was easy :)

2) the pivot table will expand. Only problem is if you add some calculated formula to the right of your grand total column, that will be wiped out. Now I'm thinking, is your grand total automatically calculated by the pivot table or not? if not, there's a way to have the table do that. If it is, then it is part of the table and will expand if more columns are needed.

3) double click on the cell that reads "Answer" (should be grey) and click "advanced" you can set sorting in there...
Is there a way to set up my Grand Total to not just be a count of the number of answers, but ((Count of Gold *5) + (Count of Silver * 3) + (Count of Bronze * 1))? Then 2 and 3 are solved at the same time because then I just sort on Grand Total.

SI
FrogMan
06-16-2005, 12:26 PM
I'm trying to find a way to do it inside the pivot table but I can't seem to find one. Best way would be to hide the current grand total and create the addition in an outside column, with the good weight for every type of answer. Dont worry, if you ever come to introduce a new level, it will ask you if you want to replace the outside grand total with a new column from the pivot table before it does so. You can then say no, move the grand total column one to the right and add the level to your pivot table afterward...

FM
lurker
06-16-2005, 05:49 PM
Good deal. That was easy :)


Is there a way to set up my Grand Total to not just be a count of the number of answers, but ((Count of Gold *5) + (Count of Silver * 3) + (Count of Bronze * 1))? Then 2 and 3 are solved at the same time because then I just sort on Grand Total.

SI

You should be able to do this using a calculated field. Make sure the pivot bar toolbar is showing. Click on "Pivot Table", then Formulas, then Calculated Field. You can create the formula in there and it should show up in your table. Hope this is what you were looking for -- I just skimmed your post.
wade moore
06-16-2005, 09:39 PM
You should be able to do this using a calculated field. Make sure the pivot bar toolbar is showing. Click on "Pivot Table", then Formulas, then Calculated Field. You can create the formula in there and it should show up in your table. Hope this is what you were looking for -- I just skimmed your post.

Keep in mind if you add a calculated field, your grand total become essentially worthless...

It will add Gold + Silver + Bronze + Calculated Field..

Any time I added a calculated field, I automatically hide the Grand Total colum..
sterlingice
06-16-2005, 10:16 PM
I've been fighting with how to do the calculated field. I just can't figure out how to make the total. Every time I do something, it adds a second row to each set of data and not a column at the end of the table like I want. I guess it's just that I can't figure out how to select the data I want to use.

SI
wade moore
06-16-2005, 10:24 PM
I've been fighting with how to do the calculated field. I just can't figure out how to make the total. Every time I do something, it adds a second row to each set of data and not a column at the end of the table like I want. I guess it's just that I can't figure out how to select the data I want to use.

SI

Send the spreadsheet to wademoore AT gmail.com
sterlingice
06-17-2005, 02:33 AM
Frankly, I have no idea how I did it but thanks to wade's instructions via gmail and some tinkering on my own, I have an auto updating, auto counting, and auto sorting (based on total points) pivot table. Also, I have no idea how it knew to sort via the score field but I'm not complaining as it works after about 30 minutes of messing with it.

SI
wade moore
06-17-2005, 04:33 AM
glad i could help ;)
FrogMan
06-17-2005, 07:38 AM
one last time...

PIVOT TABLE!!!!


:D

FM
gottimd
06-17-2005, 07:41 AM
Maybe you should try that pivot thing, whatever it is called, does anyone know? :D
sterlingice
06-17-2005, 12:10 PM
So, does anyone know how I magically got it to sort? At that point, I was trying anything I could come up with and wasn't paying all that much attention.

SI
FrogMan
06-17-2005, 12:13 PM
So, does anyone know how I magically got it to sort? At that point, I was trying anything I could come up with and wasn't paying all that much attention.

SI
With the pivot table menu bar active, click the title above the list of players. Then click the pivot table drop down on the menu bar and find something something that looks like "sort and top 10" (I got it in French here, it says "Tri et Top 10")... you should find your sorting in there...

FM
sterlingice
07-01-2005, 01:37 PM
With the pivot table menu bar active, click the title above the list of players. Then click the pivot table drop down on the menu bar and find something something that looks like "sort and top 10" (I got it in French here, it says "Tri et Top 10")... you should find your sorting in there...

FM
Now it's starting to do screwy things as to how it sorts. It will sort by the games list rather than by the total points and I can't find how to sort by that. If I select "sort"- it only allows me pick "count of game" and "answer". It won't let me select the columns by which it sorts.

SI
lurker
07-01-2005, 02:17 PM
Highlight the data in the pivot table, not including the header rows. Then go to data, then sort. If you want to sort by total points, and that starts in cell G5, for example, put that in the Sort By spot.

This is even easier if you have your toolbar set up to have the sort arrow on there. Then you choose the total points column header and click the arrow.