 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2007Formula 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 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! If you can post a link to your file (like you did last time) I'll fix ya up! Biff > Hey All. > [quoted text clipped - 33 lines] > > Thanks! HEy Biff Here's the link you have requested. http://www.nghl.net/Downloads/Football_Pool.zip Thanks > If you can post a link to your file (like you did last time) I'll fix ya up! > [quoted text clipped - 37 lines] > > > > Thanks! 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 > HEy Biff > [quoted text clipped - 45 lines] > > > > > > Thanks! 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 > Sorry One last request, I have added an area to keep track of Favorite > wins [quoted text clipped - 59 lines] >> > > >> > > Thanks! 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 > No problem. > [quoted text clipped - 71 lines] > >> > > > >> > > Thanks! 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 >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 [quoted text clipped - 83 lines] >> >> > > >> >> > > Thanks! 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 > Here's your file: > [quoted text clipped - 112 lines] > >> >> > > > >> >> > > Thanks! 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 > Hey Biff > [quoted text clipped - 138 lines] >> >> >> > > >> >> >> > > Thanks! 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! > Here's your file: > [quoted text clipped - 112 lines] > >> >> > > > >> >> > > Thanks! I must have missed that. I'll take a look. Biff > 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 [quoted text clipped - 130 lines] >> >> >> > > >> >> >> > > Thanks! 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. > I must have missed that. I'll take a look. > [quoted text clipped - 134 lines] > >> >> >> > > > >> >> >> > > Thanks! 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),"Tie",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 > You're right that is my mistake..... Sorry > [quoted text clipped - 160 lines] >> >> >> >> > > >> >> >> >> > > Thanks! P.S. For those games where the spread is a "pick" (no spread) you'll need to enter the spread as 0. Biff > Ok, change the following formulas on the Weekly sheets. > [quoted text clipped - 193 lines] >>> >> >> >> > > >>> >> >> >> > > Thanks! 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 > Ok, change the following formulas on the Weekly sheets. > [quoted text clipped - 190 lines] > >> >> >> >> > > > >> >> >> >> > > Thanks! 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 >I don't see where you have that. Are you talking about that being on the > Weekly sheet? [quoted text clipped - 221 lines] >> >> >> >> >> > > >> >> >> >> >> > > Thanks! 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. > Ok... > [quoted text clipped - 246 lines] > >> >> >> >> >> > > > >> >> >> >> >> > > Thanks! Good luck! Thanks for the feedback. Biff > 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. [quoted text clipped - 285 lines] >> >> >> >> >> >> > > >> >> >> >> >> >> > > Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.