Yet Another Excel Thread

[sterlingice]

For my video game thread, I've got an Excel spreadshee that looks something like this.

NES	Castlevania	2005.6.14	NES Classics (1st Ed)	Freebie	Vince
NES	Contra	2005.6.14	NES Classics (2nd Ed)	Freebie	NevStar
NES	Double Dragon	2005.6.14	NES Classics (2nd Ed)	Freebie	RainRaven
NES	Duck Hunt	2005.6.14	NES Classics (2nd Ed)	Freebie	Celeval
NES	Excitebike	2005.6.14	NES Classics (1st Ed)	Freebie	Blade6119
NES	Final Fantasy	2005.6.14	NES Classics (1st Ed)	Freebie	Coffee Warlord
NES	Mega Man	2005.6.14	NES Classics (1st Ed)	Freebie	kingfc22
NES	Metroid	2005.6.14	NES Classics (1st Ed)	Freebie	SirFozzie
NES	Mike Tyson's Punch Out	2005.6.14	NES Classics (2nd Ed)	Freebie	Wolfpack
NES	Ninja Gaiden	2005.6.14	NES Classics (2nd Ed)	Freebie	Izulde
NES	Super Mario Bros	2005.6.14	NES Classics (1st Ed)	Freebie	Chas in Cinti
NES	Super Mario Bros 2	2005.6.14	NES Classics (1st Ed)	Freebie	Radii
NES	Super Mario Bros 3	2005.6.14	NES Classics (1st Ed)	Freebie	Northwood_DK
NES	The Legend of Zelda	2005.6.14	NES Classics (1st Ed)	Freebie	terpkristin
NES	Tetris	2005.6.14	NES Classics (1st Ed)	Freebie	TazFTW

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]

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]

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]

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]

yeah, i meant within a function .

[Wolfpack]

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]

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]

Rule number one in excel:

When in Doubt - PIVOT TABLE!!!!!!

[sterlingice]

Thanks for the help, guys. I have something new to look into tomorrow

SI

[Mr. Wednesday]

Originally Posted by wade moore

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]

Originally Posted by Scarecrow

Rule number one in excel:

When in Doubt - Frogman!!!!!!

Fixed that for ya.

[sterlingice]

Originally Posted by Mr. Wednesday

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]

Originally Posted by thealmighty

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]

Originally Posted by sterlingice

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]

Originally Posted by Mr. Wednesday

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]

PIVOT TABLE!!!!



FM
PS: thanks for the kind words guys, appreciate it

[wade moore]

What was that FM?

Good point actually...

[sterlingice]

These are a neat feature I've never messed with before so I've got a couple of questions.

Count of Answer	Rank
Answer	Bronze	Gold	Silver	Grand Total
Coffee Warlord			1	1
N/A		1		1
NevStar	1			1
Northwood_DK	1			1
SirFozzie			1	1
terpkristin	1			1
Grand Total	3	1	2	6

(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]

Try here

[FrogMan]

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]

Originally Posted by FrogMan

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]

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]

Originally Posted by sterlingice

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]

Originally Posted by lurker

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]

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]

Originally Posted by sterlingice

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]

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]

glad i could help

[FrogMan]

one last time...

PIVOT TABLE!!!!




FM

[gottimd]

Maybe you should try that pivot thing, whatever it is called, does anyone know?

[sterlingice]

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]

Originally Posted by sterlingice

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]

Originally Posted by FrogMan

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]

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.