|
Formula Result
Hey All.
I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff 8 John Buff 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesnt cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
If you can post a link to your file (like you did last time) I'll fix ya up!
Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
HEy Biff
Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Sorry One last request, I have added an area to keep track of Favorite wins
and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
No problem.
I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
I have no idea that this could become a problem but you are know what you
talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Here's your file:
http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Hey Biff
Thanks again for your help there is one little problem I see in the outcome if the favourite ties the spread. Right now you have the favourite gets the win but actually I wanted so both teams involved would get the win seeing the favourite didnt actually beat the spread but tied it. Take Care! Rob "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Sorry I missed one other thing as well the bottom right corner of the
spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Here's what you said. I added the ** for emphasis:
So the formula should be if the favorite either *ties* or beats the spread the team who took the favorite should receive a checkmark in column D Let me take a look at it and I'll get back to you. Biff "Killer" wrote in message ... Hey Biff Thanks again for your help there is one little problem I see in the outcome if the favourite ties the spread. Right now you have the favourite gets the win but actually I wanted so both teams involved would get the win seeing the favourite didn't actually beat the spread but tied it. Take Care! Rob "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
I must have missed that. I'll take a look.
Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Youre right that is my mistake..... Sorry
The favorite must either tie or beat the spread to receive a win. The underdog will receive a win if the favorite ties or doesnt cover the spread. "T. Valko" wrote: I must have missed that. I'll take a look. Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Ok, change the following formulas on the Weekly sheets.
On row 43, cell C43: =IF(COUNT(C3,D40:D41)<3,"",IF(AND(C3=0,D40=D41),"T ie",IF(D40-C3=D41,"Push",IF(D40-C3=D41,C40,C41)))) Copy across as needed. If the spread is a tie *both* teams get credit for a win. Where I live we call that a "push". In cell D6: =IF(C6="","",IF(OR(C6=C$43,C$43="Push"),"?","")) Copy across and down as needed. Now: Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff "Killer" wrote in message ... You're right that is my mistake..... Sorry The favorite must either tie or beat the spread to receive a win. The underdog will receive a win if the favorite ties or doesn't cover the spread. "T. Valko" wrote: I must have missed that. I'll take a look. Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
P.S.
For those games where the spread is a "pick" (no spread) you'll need to enter the spread as 0. Biff "T. Valko" wrote in message ... Ok, change the following formulas on the Weekly sheets. On row 43, cell C43: =IF(COUNT(C3,D40:D41)<3,"",IF(AND(C3=0,D40=D41),"T ie",IF(D40-C3=D41,"Push",IF(D40-C3=D41,C40,C41)))) Copy across as needed. If the spread is a tie *both* teams get credit for a win. Where I live we call that a "push". In cell D6: =IF(C6="","",IF(OR(C6=C$43,C$43="Push"),"?","")) Copy across and down as needed. Now: Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff "Killer" wrote in message ... You're right that is my mistake..... Sorry The favorite must either tie or beat the spread to receive a win. The underdog will receive a win if the favorite ties or doesn't cover the spread. "T. Valko" wrote: I must have missed that. I'll take a look. Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
I don't see where you have that. Are you talking about that being on the
Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff _____________________________________ Yes on weekly sheets I want to know how many Fav or dogs won for that week. Thanks again Biff "T. Valko" wrote: Ok, change the following formulas on the Weekly sheets. On row 43, cell C43: =IF(COUNT(C3,D40:D41)<3,"",IF(AND(C3=0,D40=D41),"T ie",IF(D40-C3=D41,"Push",IF(D40-C3=D41,C40,C41)))) Copy across as needed. If the spread is a tie *both* teams get credit for a win. Where I live we call that a "push". In cell D6: =IF(C6="","",IF(OR(C6=C$43,C$43="Push"),"?","")) Copy across and down as needed. Now: Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff "Killer" wrote in message ... You're right that is my mistake..... Sorry The favorite must either tie or beat the spread to receive a win. The underdog will receive a win if the favorite ties or doesn't cover the spread. "T. Valko" wrote: I must have missed that. I'll take a look. Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Ok...
You'll need to move the formulas in row 43 down a couple of rows. (or somewhere else) I just noticed how the table is not square at the bottom right hand side. Let's assume you move the formulas in row 43 to row 45. To count the favs: =SUMPRODUCT(--(C2:AH2<""),--(C2:AH2=C45:AH45)) To count the dogs: =SUMPRODUCT(--(C4:AH4<""),--(C4:AH4=C45:AH45)) To count the pushes: =COUNTIF(C45:AH45,"push") Design tip: formatting should be the very last thing you do! Betting tip: always take the underdog! If you took every underdog in every game at the end of the year you will be ahead. Biff "Killer" wrote in message ... I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff _____________________________________ Yes on weekly sheets I want to know how many Fav or dogs won for that week. Thanks again Biff "T. Valko" wrote: Ok, change the following formulas on the Weekly sheets. On row 43, cell C43: =IF(COUNT(C3,D40:D41)<3,"",IF(AND(C3=0,D40=D41),"T ie",IF(D40-C3=D41,"Push",IF(D40-C3=D41,C40,C41)))) Copy across as needed. If the spread is a tie *both* teams get credit for a win. Where I live we call that a "push". In cell D6: =IF(C6="","",IF(OR(C6=C$43,C$43="Push"),"?","")) Copy across and down as needed. Now: Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff "Killer" wrote in message ... You're right that is my mistake..... Sorry The favorite must either tie or beat the spread to receive a win. The underdog will receive a win if the favorite ties or doesn't cover the spread. "T. Valko" wrote: I must have missed that. I'll take a look. Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Hey Biff I also notice that as well and had already made the correction.
The formulas worked prefect thank you for all your help towards this. I'll keep that in mind next time I create something on making sure the layout is completed first before formatting it. As for your tip on who to take select, the season just past youre totally correct dogs won more then the favs and the fact the NFL teams are evenly you're totally right that the dog are the better choice, but hey that would be to simple to just select dogs each week now wouldnt it¦. Thanks again for your help and good luck in your upcoming season. "T. Valko" wrote: Ok... You'll need to move the formulas in row 43 down a couple of rows. (or somewhere else) I just noticed how the table is not square at the bottom right hand side. Let's assume you move the formulas in row 43 to row 45. To count the favs: =SUMPRODUCT(--(C2:AH2<""),--(C2:AH2=C45:AH45)) To count the dogs: =SUMPRODUCT(--(C4:AH4<""),--(C4:AH4=C45:AH45)) To count the pushes: =COUNTIF(C45:AH45,"push") Design tip: formatting should be the very last thing you do! Betting tip: always take the underdog! If you took every underdog in every game at the end of the year you will be ahead. Biff "Killer" wrote in message ... I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff _____________________________________ Yes on weekly sheets I want to know how many Fav or dogs won for that week. Thanks again Biff "T. Valko" wrote: Ok, change the following formulas on the Weekly sheets. On row 43, cell C43: =IF(COUNT(C3,D40:D41)<3,"",IF(AND(C3=0,D40=D41),"T ie",IF(D40-C3=D41,"Push",IF(D40-C3=D41,C40,C41)))) Copy across as needed. If the spread is a tie *both* teams get credit for a win. Where I live we call that a "push". In cell D6: =IF(C6="","",IF(OR(C6=C$43,C$43="Push"),"?","")) Copy across and down as needed. Now: Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff "Killer" wrote in message ... You're right that is my mistake..... Sorry The favorite must either tie or beat the spread to receive a win. The underdog will receive a win if the favorite ties or doesn't cover the spread. "T. Valko" wrote: I must have missed that. I'll take a look. Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
Formula Result
Good luck! Thanks for the feedback.
Biff "Killer" wrote in message ... Hey Biff I also notice that as well and had already made the correction. The formulas worked prefect thank you for all your help towards this. I'll keep that in mind next time I create something on making sure the layout is completed first before formatting it. As for your tip on who to take select, the season just past you're totally correct dogs won more then the favs and the fact the NFL teams are evenly you're totally right that the dog are the better choice, but hey that would be to simple to just select dogs each week now wouldn't it.. Thanks again for your help and good luck in your upcoming season. "T. Valko" wrote: Ok... You'll need to move the formulas in row 43 down a couple of rows. (or somewhere else) I just noticed how the table is not square at the bottom right hand side. Let's assume you move the formulas in row 43 to row 45. To count the favs: =SUMPRODUCT(--(C2:AH2<""),--(C2:AH2=C45:AH45)) To count the dogs: =SUMPRODUCT(--(C4:AH4<""),--(C4:AH4=C45:AH45)) To count the pushes: =COUNTIF(C45:AH45,"push") Design tip: formatting should be the very last thing you do! Betting tip: always take the underdog! If you took every underdog in every game at the end of the year you will be ahead. Biff "Killer" wrote in message ... I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff _____________________________________ Yes on weekly sheets I want to know how many Fav or dogs won for that week. Thanks again Biff "T. Valko" wrote: Ok, change the following formulas on the Weekly sheets. On row 43, cell C43: =IF(COUNT(C3,D40:D41)<3,"",IF(AND(C3=0,D40=D41),"T ie",IF(D40-C3=D41,"Push",IF(D40-C3=D41,C40,C41)))) Copy across as needed. If the spread is a tie *both* teams get credit for a win. Where I live we call that a "push". In cell D6: =IF(C6="","",IF(OR(C6=C$43,C$43="Push"),"?","")) Copy across and down as needed. Now: Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. I don't see where you have that. Are you talking about that being on the Weekly sheet? You want the total count (for the week) of the number of favs and dogs? Biff "Killer" wrote in message ... You're right that is my mistake..... Sorry The favorite must either tie or beat the spread to receive a win. The underdog will receive a win if the favorite ties or doesn't cover the spread. "T. Valko" wrote: I must have missed that. I'll take a look. Biff "Killer" wrote in message ... Sorry I missed one other thing as well the bottom right corner of the spreadsheet that totals up the winners by favourite and underdogs so I can keep track of this throughout the season. Take Care! "T. Valko" wrote: Here's your file: http://cjoint.com/?fohSgkgqO3 I made some significant changes to most of the formulas on the Master sheet. I deleted that Notes sheet. On the W1 sheet... I put formulas in the 2 columns that had sample data in them. On row 43 I put in a formla that calculates the winner of the game (in the first 2 columns). Note that this formula will account for ties (although they are very rare but if one happens and you didn't account for it, it could really mess things up!). This is important: when you put the teams and scores in rows 40:41, put them in the same order as you have them in rows 2 and 4. From a design standpoint, merged cells are a real PITA and usually cause nothing but trouble. I think you'd be better off keeping the teams, the scores, the line and the winner all in one area instead of having some of this info at the top of the sheet and some of it at the bottom of the sheet. Your file was originally 694kb. It is now 360kb. I reset the used range on each sheet and deleted all of the unnecessary formatting, got rid of all those volatile formulas on the Master sheet. Biff "Killer" wrote in message ... I have no idea that this could become a problem but you are know what you talking about so please take your time. I look forward to this and also learning from your work. Thanks Again Biff "T. Valko" wrote: No problem. I would recommend some changes in some of your formulas. Your file is already "big" and you don't even have any data in it yet. If you kept things the way they are by the time you reached week 17 this file would be almost unusable. I'm going to replace all of those formulas on the Master sheet (not efficient). Give me a couple of days. Biff "Killer" wrote in message ... Sorry One last request, I have added an area to keep track of Favorite wins and Doggie wins can a formula be created around this as well. You'll see an example under week 1 I promise this will be the last request from me. Time to start reading or start paying your for your time. :) Thanks "Killer" wrote: HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks "T. Valko" wrote: If you can post a link to your file (like you did last time) I'll fix ya up! Biff "Killer" wrote in message ... Hey All. I run an football poll and I'm trying to get away from manually putting checkmarks beside the players who select the correct team and was wondering if it's possible to have this done automatically. Here's spreadsheet layout example: A : B C : D 2 Favorite Min 3 Spread 10 4 Underdog Buf Player: B C D 6 Pam Minn 7 Sam Buff ? 8 John Buff ? 9 Steve Minn A : B C D 40 Score Buff 20 41 Minn 23 So the formula should be if the favorite either ties or beats the spread the team who took the favorite should receive a checkmark in column D If the favorite doesn't cover the spread the player who selects the underdog should receive a checkmark in column D The person who didn't select the correct team column D should be left blank. Thanks! |
| All times are GMT +1. The time now is 09:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com