Formula assistance for hockey pool

qmann73

New Member
Joined
Mar 22, 2019
Messages
7
i'm trying to complete a simple hockey pool excel sheet but having an issue with a formula. I basically have a formula that says: =IF(C3:C202="TBL",('Amount of Games'!B3),"")
This is doing what I want but with one team. it is basically saying if this range of cells says TBL copy the cell from the other page. What I want to do is put multiple variables in there. I want it to say if any of these cells say TBL copy this cell but if any of these cells say BOS copy this other cell, but if any of these cells say CAR then copy this other cell... etc. etc. I can't seem to figure out the variable to have multiple cells as a choice. there seems to be a true false options and no if/else/then.

any help would be appreciated.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Welcome to the forum.

Firstly, your spreadsheet will explode if you omit CGY, the eventual Stanley Cup winner.

Secondly, what you really want to do instead of composing complex nested IF statements is utilise functions SUMIFS and COUNTIFS to create conditions and extract info from the raw data; also, learning how to multiply arrays together to do the same would be the way to go.

Perhaps watching some video tutorials on youtube would spark some ideas for you. Would that help, do you think?
 
Last edited:
Upvote 0
should be an interesting playoffs. Thanks for the advice. I will begin researching and then post more specific issues if I have any questions.
 
Upvote 0
i'm not quite sure if count if and sum-ifs will work here. I basically have a worksheet where a user will input the amount of games they think a team will play in the playoffs.

Teams
Total Games
TBL
24
BOS
18

<tbody>
</tbody>

From there I have a sheet with statistics. The statistics look as below. What I want to happen is for the formula to lookup any player that matches the Team and multiply any matches with the PPG. So in this example Kucherov (TBL) would have a total of 1.6*24. When this is complete I'm just going to do a sort from highest to lowest and it will be based on the points the players will get with number of games played the determining factor

Player
Team

PPG
Nikita Kucherov
TBL
1.6
Connor McDavid
EDM
1.53
Patrick Kane
CHI
1.4

<tbody>
</tbody>


<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
so basically the first sheet looks like this.


Book1
AB
1RkPlayer
2
31Nikita Kucherov
42Connor McDavid
53Patrick Kane
64Sidney Crosby
75Leon Draisaitl
86Johnny Gaudreau
97Brad Marchand
108Nathan MacKinnon
119Brayden Point
1210Patrice Bergeron
1311Blake Wheeler
1412Aleksander Barkov
1513Mikko Rantanen
1614David Pastrnak
1715Steven Stamkos
1816Mitch Marner
1917Alex Ovechkin
2018Auston Matthews
2119Taylor Hall
2220Jonathan Huberdeau
Sheet1
Cell Formulas
RangeFormula
G3=IF(C3:C202="TBL",('Amount of Games'!B3),"")
G4=IF(C4:C203="TBL",('Amount of Games'!B4),"")
G11=IF(C3:C202="TBL", ('Amount of Games'!B3))
 
Upvote 0
sorry i missed some there. I basically want the formula to look down column C and say if column C and 'Amount of Games' column A both = TBL enter cell B3 from 'Amount of Games' sheet. if this is false I want it to check for BOS, if this is false I want it to check for CLG and so on. if it finds two positive matches I want it to post a specific cell. hope this isn't too confusing.



Book1
ABCDEFG
1RkPlayerTmPTS
2?
31Nikita KucherovTBL1.616
42Connor McDavidEDM1.53 
53Patrick KaneCHI1.4
64Sidney CrosbyPIT1.29
75Leon DraisaitlEDM1.27
86Johnny GaudreauCGY1.24
97Brad MarchandBOS1.23
108Nathan MacKinnonCOL1.23
119Brayden PointTBL1.2216
1210Patrice BergeronBOS1.22
1311Blake WheelerWPG1.19
1412Aleksander BarkovFLA1.18
1513Mikko RantanenCOL1.18
Sheet1
Cell Formulas
RangeFormula
G3=IF(C3:C202="TBL",('Amount of Games'!B3),"")
G4=IF(C4:C203="TBL",('Amount of Games'!B4),"")
G11=IF(C3:C202="TBL", ('Amount of Games'!B3))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top