Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
ultra vires
 
Posts: n/a
Default Array with two criteria


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Array with two criteria

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)),C 1: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
  #3   Report Post  
Posted to microsoft.public.excel.misc
ultra vires
 
Posts: n/a
Default Array with two criteria


thanks - thats sorted it


--
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Array formulas with multiple criteria Space Elf Excel Worksheet Functions 2 January 15th 06 02:23 PM
An array and countif based on criteria in each column Rochelle B Excel Worksheet Functions 1 October 25th 05 05:08 AM
Searching for a criteria in array of cells within an IF statement selvaraj Excel Worksheet Functions 1 July 28th 05 03:36 PM
Array Formula w/ Multiple SumIf Criteria Andy Excel Worksheet Functions 3 July 13th 05 08:56 PM
match multiple criteria & return value from array Tat Excel Worksheet Functions 2 June 21st 05 04:31 PM


All times are GMT +1. The time now is 11:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"