Unknown Formula?

[drc2265]

I just want to know if there is a formula that i can use for a free NFL pool I do with some buddies. I want to have numbers for each NFL team, If Player #1 picks team 1, and team 1 wins is there any way to calculate everybodies wins?
IE:
GAME:1 vs 2 3 vs 4
1. Player 1 1 4

Say team #1 and #3 wins, player 1 would have 1 win out of 2 possibilities. Is there any formula to add the wins only up? The way im doing it now is im using the find and replace feature and replacing all the winners with a "1" and all the losers with a space and then using the sum function to add all the "1"s up, which takes a bit of time.

Thanks for all your help.

 

[Dan Waterloo]

Hey drc,

I made a couple of tables to model what you were talking about. The first table consists of the first four columns below. Player is the player in the pool. The table is saying that in week 1, player 1 selected teams 4 and 1 to win and player 2 selected teams 2 and 4 to win. The second table is the results table. It says that in week 1 teams 1 and 3 won their games and in week 2 teams 2 and 3 won their games. (Note: There's a weakness here in that the games could possibly be tied. This can be handled, too, with a bit more work.) Keep reading below.

Player Week Team Result Week Team
1 1 4 0 1 1
1 1 1 1 1 3
2 1 2 0 2 2
2 1 4 0 2 3
3 1 1 1
3 1 3 1
1 2 4 0
1 2 1 0
2 2 2 1
2 2 4 0
3 2 1 0
3 2 3 1

The result column contains an Array formula. It uses named ranges based on the Results table. Team is =Sheet3!$G$2:$G$5 and Week is =Sheet3!$F$2:$F$5.

Then the Array formula in cell D2 (right beneath the header Result) is =SUM((B2=Week)*(C2=Team)).

NOTE: when you enter this fomula you MUST finish pressing <Ctrl><Shift><Enter> all at the same time. Then you can just copy this formula to the cells beneath it.

To get totals for each player in the pool I'd recommend using a pivot table.

 

[Dan Waterloo]

I need to learn how to post columnar data better. Table 1 consists of the first four columns and Table 2 consists of the fifth and sixth columns.

 

[Dan Waterloo]

Hey drc,

I used Named Ranges to make the formulas look a bit cleaner but you may not be used to them. If you didn't create them properly, that might be the cause of ?NAME error.

You shouldn't have 0's in your Week or Team columns.

The tables can also be laid out like this:

Player Week Team Result
1 1 4 0
1 1 1 1
2 1 2 0
2 1 4 0
3 1 1 1
3 1 3 1
1 2 4 0
1 2 1 0
2 2 2 1
2 2 4 0
3 2 1 0
3 2 3 1

Week Team
1 1
1 3
2 2
2 3

Only the Result column has 0's in it.

But I think you also need to learn about Array formulas. Mr. Excel has some very good explanations. I find one or two and post links.

 

[drc2265]

I got to the Pivot Table and Dont understand where im going with that.....

 

[Dan Waterloo]

These are Array formulas not pivot tables.

 

[Dan Waterloo]

Here are a couple of array formula links on this website:
http://www.mrexcel.com/tip011.shtml
http://www.mrexcel.com/tip031.shtml

These can both be found on this page in the Formulas section near the top.
http://www.mrexcel.com/articles.shtml

 

[drc2265]

I know that, i made the table then you said to get results, use a pivot table