Unknown Formula?

drc2265

Board Regular
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.

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Dan Waterloo

Well-known Member
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
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
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

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

Dan Waterloo

Well-known Member
These are Array formulas not pivot tables.

drc2265

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

Replies
0
Views
106
Replies
0
Views
166
Replies
9
Views
189
Replies
1
Views
113
Replies
1
Views
335

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,560
Messages
5,765,085
Members
425,258
Latest member
brentmitchell

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.

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

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