Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Interesting Counting Question...HELP
I work in Recreation and thought that excel might be able to help us. Here is the background. I am running a sports league. Each week teams play and results are sent to us to post. Players that were penalized during the game are put onto a list and if they are penalized 3 times within 5 weeks they are suspended. Here is my question. Is there a formula in excel that would allow me to determine the 3 time offenders automatically. Example: Ted 5/29 Jay 6/5 David 6/5 John 6/12 Gustavo 6/12 Greg 6/12 Ernesto 6/26 Robert 6/26 Gustavo 6/26 Joel 7/10 Gustavo 7/10 Greg 7/10 Jose 7/24 Gustavo 7/31 Each of these players were penalized on these dates. For example purposes look at Gustavo. He has been penalized at least 3 times in a 5 week period. He would be suspended for 1 week because of it. Usually I would have to compare this list each week with a calendar and manually add/edit. Any help would greatly be appreciated... Please email me with any questions. Thank you, T. Larson -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#2
|
|||
|
|||
T,
With your names in column A and dates in column B, starting in row 2 (with headers in row 1), and the effective date entered into C1 (so that you don't have to rush and can easoly review previous weeks), enter this formula into cell C2: =IF(AND(B2<=$C$1,B2=$C$1-35), SUMPRODUCT(($A$2:$A$15=A2)*($B$2:$B$15=$C$1-35)*($B$2:$B$15<=$C$1)),"") All on one line. Change all of the $15's to reflect your actual last value's row number. HTH, Bernie MS Excel MVP "onesidered" wrote in message ... I work in Recreation and thought that excel might be able to help us. Here is the background. I am running a sports league. Each week teams play and results are sent to us to post. Players that were penalized during the game are put onto a list and if they are penalized 3 times within 5 weeks they are suspended. Here is my question. Is there a formula in excel that would allow me to determine the 3 time offenders automatically. Example: Ted 5/29 Jay 6/5 David 6/5 John 6/12 Gustavo 6/12 Greg 6/12 Ernesto 6/26 Robert 6/26 Gustavo 6/26 Joel 7/10 Gustavo 7/10 Greg 7/10 Jose 7/24 Gustavo 7/31 Each of these players were penalized on these dates. For example purposes look at Gustavo. He has been penalized at least 3 times in a 5 week period. He would be suspended for 1 week because of it. Usually I would have to compare this list each week with a calendar and manually add/edit. Any help would greatly be appreciated... Please email me with any questions. Thank you, T. Larson -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#3
|
|||
|
|||
You could use a pivot table, selecting only 5 weeks data at a time, this would then list all the players and a count of the number of cautions -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#4
|
|||
|
|||
T,
I forgot to say: after you enter the formula in cell C2, copy it down column C to match your data, and then you can filter the table based on column C, to show values greater than 2 (or greater than or equal to 3). HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... T, With your names in column A and dates in column B, starting in row 2 (with headers in row 1), and the effective date entered into C1 (so that you don't have to rush and can easoly review previous weeks), enter this formula into cell C2: =IF(AND(B2<=$C$1,B2=$C$1-35), SUMPRODUCT(($A$2:$A$15=A2)*($B$2:$B$15=$C$1-35)*($B$2:$B$15<=$C$1)),"") All on one line. Change all of the $15's to reflect your actual last value's row number. HTH, Bernie MS Excel MVP "onesidered" wrote in message ... I work in Recreation and thought that excel might be able to help us. Here is the background. I am running a sports league. Each week teams play and results are sent to us to post. Players that were penalized during the game are put onto a list and if they are penalized 3 times within 5 weeks they are suspended. Here is my question. Is there a formula in excel that would allow me to determine the 3 time offenders automatically. Example: Ted 5/29 Jay 6/5 David 6/5 John 6/12 Gustavo 6/12 Greg 6/12 Ernesto 6/26 Robert 6/26 Gustavo 6/26 Joel 7/10 Gustavo 7/10 Greg 7/10 Jose 7/24 Gustavo 7/31 Each of these players were penalized on these dates. For example purposes look at Gustavo. He has been penalized at least 3 times in a 5 week period. He would be suspended for 1 week because of it. Usually I would have to compare this list each week with a calendar and manually add/edit. Any help would greatly be appreciated... Please email me with any questions. Thank you, T. Larson -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#5
|
|||
|
|||
Thank you for the swift replies... Bernie... I have entered everything as you indicated... and I don't know what to do next... This is a little more advanced than I am with Excel. I have followed your instruction to a T.... I just need to know what comes next? Sorry for my lack of competence in excel.... HELP... Thanks again, T. -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#6
|
|||
|
|||
T.,
Make sure that the dates are actual dates, and not strings. Excel can't do math (easily at least) on strings that only look like dates. The date cells should be formatted as dates, and should show something like 6/15/2005 in the formula bar, no matter what shows on the spreadsheet. If it doesn't, format the cells for date, then put a 1 into a blank cell, copy that cell, then select your dates and choose pastespecial / multiply, and press OK. That will cause Excel to convert the strings into actual dates, and then the formula will at least have a better chance of working. If you can't get it to work, contact me privately by replying to me through this message, and take the spaces out of my address and change the dot to . Attach a copy of your workbook, and I will fix it to work. HTH, Bernie MS Excel MVP "onesidered" wrote in message ... Thank you for the swift replies... Bernie... I have entered everything as you indicated... and I don't know what to do next... This is a little more advanced than I am with Excel. I have followed your instruction to a T.... I just need to know what comes next? Sorry for my lack of competence in excel.... HELP... Thanks again, T. -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#7
|
|||
|
|||
Bernie, Thanx for all of your help in this. The dept. really appreciates this. It works great! I do have one more question though. I have figured out a small formula that I want to have the results show in a seperate cell. Example. Gustavo has 3 therefore he is Suspended This is my formula... =IF(C5=3,"Suspended Player",""). And it works... My question now is... can there be a formula that says... If cell ="" then Over 5 Weeks OR if cell = 3 or more then Suspended Player? Thanks again for the assistance. T. -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#8
|
|||
|
|||
T.,
How about: =IF(C5=3,"Suspended Player","Not more than 2 within last 5 weeks") HTH, Bernie MS Excel MVP "onesidered" wrote in message ... Bernie, Thanx for all of your help in this. The dept. really appreciates this. It works great! I do have one more question though. I have figured out a small formula that I want to have the results show in a seperate cell. Example. Gustavo has 3 therefore he is Suspended This is my formula... =IF(C5=3,"Suspended Player",""). And it works... My question now is... can there be a formula that says... If cell ="" then Over 5 Weeks OR if cell = 3 or more then Suspended Player? Thanks again for the assistance. T. -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#9
|
|||
|
|||
How did you ever get so good at this Bernie? Wow... that worked great, BUT (I'll bet you knew there was a catch) All of the cells with a value of "" are now considered "suspended players" instead of "Past 5 weeks" We are almost there... thanx again. Who knew that excel was such a powerful tool? -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
#10
|
|||
|
|||
onesidered,
How did you ever get so good at this Bernie? Wow... Practice! ;-) How about: =IF(AND(C5<"",C5=3),"Suspended Player","Not more than 2 within last 5 weeks") -- HTH, Bernie MS Excel MVP "onesidered" wrote in message ... All of the cells with a value of "" are now considered "suspended players" instead of "Past 5 weeks" We are almost there... thanx again. Who knew that excel was such a powerful tool? -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977 View this thread: http://www.excelforum.com/showthread...hreadid=393435 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
interesting question can anyone help | Excel Discussion (Misc queries) | |||
Counting question | Excel Worksheet Functions | |||
Counting Question | Excel Discussion (Misc queries) | |||
Counting question | Excel Discussion (Misc queries) | |||
Counting question | Excel Discussion (Misc queries) |