MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Dustin on March 12, 2001 7:56 AM

Good morning folks,
Here is my dilemma:

I need to identify the lowest value (Resume Cycle-Time)associated with each of many key fields (Requisition Numbers)and then calculate the average of these values on a monthly basis.

Here is an example of the sheet:

----------A---------B---------C------------D--------
1----Req Number--Received----Sent------Cycle Time---
2----001234-------2/12/01----2/12/01-------0--------
3----009876-------2/14/01----2/15/01-------1--------
4----001234-------2/12/01----2/16/01-------4--------
5----001234-------2/12/01----2/17/01-------5--------
6----009876-------2/14/01----2/18/01-------4--------
.
.
How would I write a formula to return the average of the low cycle times for all requisitions received during a monthly period (January, February, March, etc)

Example, in the scenario above, I would want to calculate the average of "0" from line 2 and the "1" from line 3 since they are the low values for the two listed requisitions received in February.

Thank you very much!


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

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.

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

Thanks very much Mark!

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

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.

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

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.

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

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.

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

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}

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

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?

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

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.

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

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

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

...Or, if you prefer...

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