Return and Average the low values of an Array?!?!?

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.

One more thing...

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.

Thanks very much Mark!

More help please...

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.

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.

BTW...

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.

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

Re: BTW

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.

That being the case...

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}

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.

Wait a minute...

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?

Re: Wait a minute...

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.

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. ; )

See my latest posting below...

Oops! Typo...

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. ; )

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.

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.

...Or, if you prefer...

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