Unknown Formula?

drc2265

Board Regular
Joined
Jul 30, 2007
Messages
96
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
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

Well-known Member
Joined
Jan 4, 2007
Messages
876
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

Well-known Member
Joined
Jan 4, 2007
Messages
876
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

Board Regular
Joined
Jul 30, 2007
Messages
96
I got to the Pivot Table and Dont understand where im going with that.....
 

drc2265

Board Regular
Joined
Jul 30, 2007
Messages
96
I know that, i made the table then you said to get results, use a pivot table
 

Forum statistics

Threads
1,186,165
Messages
5,956,320
Members
438,246
Latest member
Deelea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top