Posted by Mark W. on March 12, 2001 8:13 AM

Dustin, create a "Lowest" column in your data set by

entering the formula, =(D2=MIN(IF(B2=$B$2:$B$6,$D$2:$D$6,"")))+0,

into cell E2 and copying down.

Now all you have to do is create a PivotTable with

"Lowest" in the PAGE area (and set to 1), "Received"

in either the ROW or COLUMN area and grouped by Month, and

"Cycle Time" in the DATA area using the Average summary

function.

Posted by Mark W. on March 12, 2001 8:16 AM

Dustin, I should have indicated the the Lowest formula,

{=(D2=MIN(IF(B2=$B$2:$B$6,$D$2:$D$6,"")))+0}, is an

array formula which must be entered using Shift+Ctrl+Enter.

Posted by Dustin on March 12, 2001 8:33 AM

Posted by Dustin on March 12, 2001 10:07 AM

Mark,

The formula that you gave me does return the single lowest value fom the entire list. I need to extend this a bit so that I get the lowest value for each of the req numbers, for example, the lowest cycle time for req number 001234, the lowest cycle time for req number 009876, etc, not just the lowest cycle time overall.

Posted by Mark W. on March 12, 2001 10:15 AM

Here's the needed revision...

Dustin, you're right! I hurriedly submitted my

previous response and then rushed off to a meeting.

While in that meeting my mind wandered and I

realized that I hadn't fully met your requirement.

Here' my revision:

{=(D2=MIN(IF(A2&TEXT(B2,",MYY")=$A$2:$A$6&TEXT($B$2:$B$6,",MYY"),$D$2:$D$6,"")))+0}

You'll notice that this formula consider's each

distinct requisition number and a monthly grouping.

Let me know if I've missed the mark again.

Posted by Mark W. on March 12, 2001 10:27 AM

This formula might be "overkill". It's not clear

to me from your description that you'd ever have

an requistion with different Received dates.

Posted by Dustin on March 12, 2001 10:42 AM

Re: Here's the needed revision...

Mark,

Thanks again for your help. We are getting closer. The revision you gave me does return a one for rows with the lowest cycle time where the req number and monthly grouping are unique. However, the formula returns a "1" for each instance of the combination. For example, in my data, I have two instances of Req 0001234 that have a cycle time of "2". The formula results in each one of these receiving a "1".

When I go through the next portion of my calculation, the averaging of these low cycle times, I will skew my result by having multiple instances of the same req in the range to be averaged.

Am I making any sense?

Dustin, you're right! I hurriedly submitted my

Posted by Dustin on March 12, 2001 10:48 AM

Mark,

No case where a unique Req Number would have multiple received dates. The date we receive the requisition is static for all future calculations, discussion, etc. Sorry for not being clear on that.

Posted by Mark W. on March 12, 2001 11:04 AM

Dustin, that being the case you should be able to

get by with:

{=(D2=MIN(IF(A2=$A$2:$A$6,$D$2:$D$6,"")))+0}

Posted by Mark W. on March 12, 2001 11:08 AM

Re: Here's the needed revision...

Dustin, that's not happening for me. Are you

sure that you've faithfully reproduced my formula?

Let's pickup our thread with my latest posting

below.

Posted by Mark W. on March 12, 2001 11:13 AM

Dustin, your sample data doesn't have such a case.

And, what behavior do you expect from this formula

in such a case? Would you care to revise your

sample data to include such a case?

Posted by Dustin on March 12, 2001 11:26 AM

Mark,

This is getting more confusing by the minute, if you are agreeable to it, send me a phone number and we can talk through this over the phone.

If not, here is a revised example:

--------A------------B---------C------------D-----

1----Req Number--Received----Sent------Cycle Time

2----001234-------2/12/01----2/12/01-------0----

3----001234-------2/12/01----2/12/01-------0----

4----009876-------2/14/01----2/15/01-------1----

5----009876-------2/14/01----2/15/01-------1----

6----001234-------2/12/01----2/16/01-------4----

7----001234-------2/12/01----2/17/01-------5----

8----009876-------2/14/01----2/18/01-------4----

9----001234-------2/12/01----2/19/01-------7----

The other columns in the sheet would include items such as the name of the person being submitted , etc...other things to make each record unique, but not necessarily come into play in this formula (maybe they do and I just haven't figured out how - HUGE apologies if that info would have saved some confusion)

As I tryed to explain earlier, I just need to get

a formula to return the monthly average of the low cycle times for each of the req numbers tracked on the sheet.

Send me a phone number and I'll give you a call.

Thanks again.

Posted by Mark W. on March 12, 2001 11:46 AM

The devil is always in the details...

Dustin, I doubt that a phone call will be necessary,

if you allow me to choose the 1st occurance of a

unique requistion, cycle time pair as does the

following formula:

{=AND(D2=MIN(IF(A2=$A$2:$A$9,$D$2:$D$9,"")),A2&TEXT(D2,",0")<>$A$1:A1&TEXT($D$1:D1,",0"))+0}

This whole experience is a testimonial to the "art"

of selecting test data. While the data you selected

exhibits multiple requistion, cycle time pairs, the

results are skewed because there are 2 of each. Also,

it would have been nastier if the first occurrence of

each requisition wasn't the solution. ; )

Posted by Mark W. on March 12, 2001 11:47 AM

See my latest posting below...

Posted by Mark W. on March 12, 2001 11:50 AM

Dustin, I doubt that a phone call will be necessary,

if you allow me to choose the 1st occurance of a

unique requistion, cycle time pair as does the

following formula:

{=AND(D2=MIN(IF(A2=$A$2:$A$9,$D$2:$D$9,"")),A2&TEXT(D2,",0")<>$A$1:A1&TEXT($D$1:D1,",0"))+0}

This whole experience is a testimonial to the "art"

of selecting test data. While the data you selected

exhibits multiple requistion, cycle time pairs, the

results AREN'T skewed because there are 2 of each. Also,

it would have been nastier if the first occurrence of

each requisition wasn't the solution. ; )

Posted by Dustin on March 12, 2001 11:55 AM

Re: The devil is always in the details...It WORKED!!!

Thanks so very much Mark. I understand your point about selecting the "best" test data.

Posted by Mark W. on March 12, 2001 12:10 PM

This nested IF() is more efficient...

Dustin, these nested IF() solution is more efficient...

{=IF(D2=MIN(IF(A2=$A$2:$A$9,$D$2:$D$9,"")),IF(AND(A2<>$A$1:A1),1,0),0)}

...because it only tests for the 1st instance of

a requisition number if you're dealing with the record

containing the minimum cycle time for that requisition.

Posted by Mark W. on March 12, 2001 3:07 PM

{=IF(D2=MIN(IF(A2=$A$2:$A$9,$D$2:$D$9,"")),AND(A2<>$A$1:A1)+0,0)}