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

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

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

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

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

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

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

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

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

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