Array with two criteria

[ultra vires]

Is it possible to find the minimum value (earliest time) using an array?

Column A contains the date field column B is the operation start time and column C contains the surgeon.

Obviously there will be many operations per day per surgeon - I need to find the earliest start time for each surgeon on each day.

I can get the start time using an arrray with one criteria but not two, what is the easiest way of doing this?

[Dave Peterson]

I think you'd be better served by looking at data|pivottables.

These kinds of summary tables can show you the minimum value for each surgeon
and day.

Add headers if you don't have them
select your range a1:cxxx
data|pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the surgeon button to the row field
drag the date button to the column field
drag the time button to the data field

double click on that "sum of time" button and choose Min.

=======
But you could use an array formula for each surgeon/day:

=MIN(IF((A1:A10="name1")*(B1:B10=DATE(2006,3,2)),C1:C10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

And you'll need a formula for each doctor/date.


ultra vires wrote:
>
> Is it possible to find the minimum value (earliest time) using an
> array?
>
> Column A contains the date field column B is the operation start time
> and column C contains the surgeon.
>
> Obviously there will be many operations per day per surgeon - I need to
> find the earliest start time for each surgeon on each day.
>
> I can get the start time using an arrray with one criteria but not two,
> what is the easiest way of doing this?
>
> --
> ultra vires
> ------------------------------------------------------------------------
> ultra vires's Profile: http://www.excelforum.com/member.php...fo&userid=4855
> View this thread: http://www.excelforum.com/showthread...hreadid=518224

--

Dave Peterson

[ultra vires]

thanks - thats sorted it