Excel: Calculating a Win Streak

[MooSki]

Hi guys!

I have been handed a piece of work that has me stumped. Essentially, there us a summary sheet for Sales that has various data on there like average weekly sales (per quarter, year) max sales and other items.

Item ............... Mathew ... Dean ... David ...
Ave QTR
Ave YR
Max WK
...
Consec Target

I have been asked to add to this summary an item which shows the number of consecutive weeks that each salesperson achieves a target. I immediately thought it was like a "Win Streak" like you see within sports, so I googled for that, found something that I thought I might be able to work with, but alas the code I found for a user defined function didn't take into account a lookup for salesperson, ie only asked for a range of cells to look at and the value you are looking for.

The data I have isn't sorted, is irregular (salesmen can be absent from work on certain weeks) and the new data is appended to the bottom of the list. An example:

Year	Week	Name	Total
2010/11	7	Mathew	10
2010/11	7	Dean	10
2010/11	7	Stu	30
2010/11	7	Martin	40
2010/11	7	David	50
2010/11	7	Richard	10
2010/11	8	Mathew	30
2010/11	8	Dean	40
2010/11	8	Martin	10
2010/11	8	David	10
2010/11	8	Richard	20
2010/11	9	Mathew	10
2010/11	9	Dean	30
2010/11	9	Stu	40
2010/11	9	Martin	50
2010/11	9	David	10
2010/11	9	Richard	30
2010/11	10	Mathew	40
2010/11	10	Dean	50
2010/11	10	Stu	10
2010/11	10	Martin	10
2010/11	10	Richard	10

Note that David is not in week 10, for instance.

What I would like is for a couple of items in the summary page; one showing the current "target streak", another for the best or maximum streak.

As the data can't be sorted, I have thought that this might be an array problem within some code, only bringing certain items into the array depending on whose name is at the top of the column in the summary sheet. However, I just can't get my head around how I can get this to work.

Any help would be greatly appreciated.

Thanks in advance,
Mooski.

 

[randy700]

Here's one way...
Record a macro that performs the following steps.
1. Sort by Week, Name
2. Inserts this formula in the Win Streak column (E2):
=IF(C2=C1,IF(D2>=$F$1,E1+1,0),1)
3. Copy the formula to the last record.
4. Copy column E, then Paste Values.
5. Re-sort by Year, Week

This assumes your data is in columns A thru D and the "goal" is in E1.


Randy

 

[SkipVought]

Just a bit different than Randy's
[tt]
1. Sort by YEAR, NAME, WEEK

2. Heading in E1: Win Streak

3. Formula in E2
E2: =IF(AND(C2=C1,B2=IF(ISNUMBER(B1),B1,0)+1),IF(ISNUMBER(E1),E1,0)+1,1)

4. Pivot Table on your table, using MAX of Win Streak, Name Sorted Descending by Max of Win Streak...

Max of Win Streak
Name Total
Richard 4
Mathew 4
Dean 4
Martin 4
David 3
Stu 2
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]