Hello all. I've browsed the forums for help quite a bit but this is my first post. I found posts that have got me this far but am a bit stumped now. I apologize ahead of time for the lengthy post. I thought there would be value in explaining what I am doing in detail, at the risk of people losing interest before replying.
I have a spreadsheet with multiple worksheets that contain data for a super bowl squares thing I have been running for the last 9 years. I am putting together some fun stats on different things for people to see. Here is a sample of the last few years from the main worksheet called Winners.
[TABLE="width: 400"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Quarter[/TD]
[TD]Winner[/TD]
[TD]Square[/TD]
[TD]AFC Score[/TD]
[TD]NFC Score[/TD]
[TD]Winnings[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]800[/TD]
[/TR]
</tbody>[/TABLE]
***SKIP THIS IF YOU KNOW HOW THE SUPER BOWL SQUARES WORKS***
For those that don't know how super bowl squares works there is a board with 100 squares. I happen to number the squares 1 to 100 ahead of time for ease of people picking, and some people like to pick a particular number. The actual square number is really meaningless otherwise. People buy one or more squares on the board and once they are all sold, numbers 0-9 are randomly generated along the top and side which represent the teams in the super bowl. At the end of each quarter you take the score of the game and match it up with the corresponding square on the board. For a score of 7-3 (lets assume the team along the top has 7, and the team along the side has 3) you find 7 along the top, and 3 along the side and find the square that intersects. For a double digit score you take the right most number, so 14-7, you would look for 4 and 7.
***END OF EXPLANATION***
I am working on some stats on scores that have won. Here is my worksheet for scores.
[TABLE="width: 259"]
<tbody>[TR]
[TD]Score[/TD]
[TD]Times In The Money[/TD]
[TD]Money Won[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My first issue is with the times in the money calculation. I have it working with the following formula.
=SUMPRODUCT(--(--RIGHT(Winners!E2:E37,1)=A2))+SUMPRODUCT(--(--RIGHT(Winners!F2:F37,1)=A2))
I am happy with that, except that I have to specifically define the cells its counting (E2:E37 + F2:F37). So each year when I add more data, I have to adjust the formula. If I try and use Winners!$E:$E it throws an error because it trys to add #VALUE! which gets returned for the blank fields beyond my data. I know there is an iferror function which can convert it to a value of 0, but I can't seem to figure out where to plug that function in.
My second issue if figuring out the amount of money each number has won. I'm sure the formula would be very similar to the first one, except rather than counting 1 for each true returned in the formula, I want to sum the Winnings value in the worksheet Winners.
Thank you for reading my post. If you have any suggestions, please reply.
I have a spreadsheet with multiple worksheets that contain data for a super bowl squares thing I have been running for the last 9 years. I am putting together some fun stats on different things for people to see. Here is a sample of the last few years from the main worksheet called Winners.
[TABLE="width: 400"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Quarter[/TD]
[TD]Winner[/TD]
[TD]Square[/TD]
[TD]AFC Score[/TD]
[TD]NFC Score[/TD]
[TD]Winnings[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]800[/TD]
[/TR]
</tbody>[/TABLE]
***SKIP THIS IF YOU KNOW HOW THE SUPER BOWL SQUARES WORKS***
For those that don't know how super bowl squares works there is a board with 100 squares. I happen to number the squares 1 to 100 ahead of time for ease of people picking, and some people like to pick a particular number. The actual square number is really meaningless otherwise. People buy one or more squares on the board and once they are all sold, numbers 0-9 are randomly generated along the top and side which represent the teams in the super bowl. At the end of each quarter you take the score of the game and match it up with the corresponding square on the board. For a score of 7-3 (lets assume the team along the top has 7, and the team along the side has 3) you find 7 along the top, and 3 along the side and find the square that intersects. For a double digit score you take the right most number, so 14-7, you would look for 4 and 7.
***END OF EXPLANATION***
I am working on some stats on scores that have won. Here is my worksheet for scores.
[TABLE="width: 259"]
<tbody>[TR]
[TD]Score[/TD]
[TD]Times In The Money[/TD]
[TD]Money Won[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My first issue is with the times in the money calculation. I have it working with the following formula.
=SUMPRODUCT(--(--RIGHT(Winners!E2:E37,1)=A2))+SUMPRODUCT(--(--RIGHT(Winners!F2:F37,1)=A2))
I am happy with that, except that I have to specifically define the cells its counting (E2:E37 + F2:F37). So each year when I add more data, I have to adjust the formula. If I try and use Winners!$E:$E it throws an error because it trys to add #VALUE! which gets returned for the blank fields beyond my data. I know there is an iferror function which can convert it to a value of 0, but I can't seem to figure out where to plug that function in.
My second issue if figuring out the amount of money each number has won. I'm sure the formula would be very similar to the first one, except rather than counting 1 for each true returned in the formula, I want to sum the Winnings value in the worksheet Winners.
Thank you for reading my post. If you have any suggestions, please reply.