Tip jar

If you like CaB and wish to support it, you can use PayPal or KoFi. Thank you, and I hope you continue to enjoy the site - Neil.

Buy Me a Coffee at ko-fi.com

Support CaB

Recent

Welcome to Cook'd and Bomb'd. Please login or sign up.

March 28, 2024, 11:36:59 AM

Login with username, password and session length

Calling all Excel nerds

Started by The Duck Man, August 26, 2015, 06:42:18 PM

Previous topic - Next topic

The Duck Man

I figure this could be a catch-all area for Excel issues but... me first.

So, for reasons too lamentable to go into, I'm creating a spreadsheet to produce stats for a quiz season. This example is for a team.



The table on the left is the series of scores they've made each week, and also acts as a record of attendance. This row underneath records the finishing position of that team. The table on the right records how many podium positions were reached by each team member. What I'm trying to do is produce the table on the right from the data on the left. I've tried to do within Excel but hit a wall and I can't find the right help-guide online.

So any Excel boffins out there who could make this super cool task go a bit quicker?

Cheers!

syntaxerror

Its ironic that Google cant help you when most pub quizes these days are just Googling competitions. As for Excel I have no idea. I dont really understand your table - if you're just adding one row of scores a week, cant you just use a simple add function? I'm sure it's just me not getting it.

Edit:- if you're after some statistical analysis, I can tell you that John is a workhorse and Adam is a fucking skiver.

mook


Uncle TechTip

Honestly this is fucked up. I've looked at the tables ten times and I still don't get it. On the left each player gets the same score each week - presume they were in a team. So how come on the right they have a different number of podium finishes? If you're still arsed maybe you could explain in more detail. It will probably involve the countif function.

Lee Van Cleef

Yeah. I don't get it. Looking at the data gives me a headache.

momatt

Why do these individuals have podium positions when they're in teams?

Can't you just give the weekly winners a free pint and a big kiss, then leave it at that?

MojoJojo

Pivot table.

That's always the answer with excel - you need a pivot table.

I only use excel when forced but I do think you need a pivot table here.

Uncle TechTip

I think I follow now. On the right is the number of times each Ben has been in the team and achieved that position.

You'll need a lookup on the final team positions regardless of who was in the team. Then look up depending on whether the cell is black or not. If not, look up and count the number of 1s. Put that in each 1 box. Repeat for the other positions.