Finding The Nth Greatest Value Meeting Criteria

**ultra vires** · 09-21-2005, 08:23 AM

I can make it work for the largest but how would you do it for the smallest or for a value between two others

**Alan** · 09-21-2005, 05:43 PM

Hi ultra vires,

Originally Posted by ultra vires

I can make it work for the largest but how would you do it for the smallest or for a value between two others

The example above should do it for the second largest.

Also look at the SMALL and RANK functions for other options.

HTH,

Alan.

**ultra vires** · 09-23-2005, 09:10 AM

Originally Posted by Alan

Hi ultra vires,

The example above should do it for the second largest.

Also look at the SMALL and RANK functions for other options.

HTH,

Alan.

Thanks for that very helpful.

In my sheet I have column which is a list of dates and a column which is a list of times. The first formula will give me the second largest for the day overall which is great and a lot more progress than I had made before. But I want to know the smallest and largest between say 8:30 and 12:00 and 13:30 and 17:00 - is this possible?

**Alan** · 09-27-2005, 01:25 AM

Hi ultra vires,

Originally Posted by ultra vires

Thanks for that very helpful.

In my sheet I have column which is a list of dates and a column which is a list of times. The first formula will give me the second largest for the day overall which is great and a lot more progress than I had made before. But I want to know the smallest and largest between say 8:30 and 12:00 and 13:30 and 17:00 - is this possible?

Sorry, but I don't quite understand.

Could you post a small sample of data, and the result you want to get?

Thanks,

Alan.

**ultra vires** · 09-27-2005, 05:02 AM

Here is a sample of the data

Date Anaes Started Operation Finished
04/07/2005 04/07/2005 14:55 04/07/2005 15:40
04/07/2005 04/07/2005 15:40 04/07/2005 16:08
04/07/2005 04/07/2005 15:00 04/07/2005 15:20
08/07/2005 08/07/2005 09:56 08/07/2005 10:33
08/07/2005 08/07/2005 10:48 08/07/2005 11:34
08/07/2005 08/07/2005 10:34 08/07/2005 10:46
08/07/2005 08/07/2005 14:30 08/07/2005 14:55
08/07/2005 08/07/2005 09:10 08/07/2005 09:31
11/07/2005 11/07/2005 14:07 11/07/2005 14:47
11/07/2005 11/07/2005 13:36 11/07/2005 14:08
11/07/2005 11/07/2005 09:26 11/07/2005 09:10
11/07/2005 11/07/2005 15:10 11/07/2005 15:40
13/07/2005 13/07/2005 15:25 13/07/2005 15:56
13/07/2005 13/07/2005 14:58 13/07/2005 15:21
13/07/2005 13/07/2005 13:45 13/07/2005 14:30
13/07/2005 13/07/2005 14:32 13/07/2005 14:56
20/07/2005 20/07/2005 14:18 20/07/2005 14:52
20/07/2005 20/07/2005 13:57 20/07/2005 14:17
20/07/2005 20/07/2005 15:45 20/07/2005 16:35

What I want to do is find the earliest anaes start time for each day before 12:00 and after 12:00 I also need the last operation finish time before 13:00. Using LARGE will give me the last operation finish time overall.

Hope you can help.

**Alan** · 09-28-2005, 02:19 AM

Hi ultra vires,

Originally Posted by ultra vires

Here is a sample of the data

Date Anaes Started Operation Finished
04/07/2005 04/07/2005 14:55 04/07/2005 15:40
04/07/2005 04/07/2005 15:40 04/07/2005 16:08

{Snipped}

What I want to do is find the earliest anaes start time for each day before 12:00 and after 12:00 I also need the last operation finish time before 13:00. Using LARGE will give me the last operation finish time overall.

Hope you can help.

I am not sure how your data works.

From the above, it appears that you have the following (first line of data):

Date = 04/07/2005
Anaes = 04/07/2005
Started = 14:55
Operation = 04/07/2005
Finished = 15:40

So I am guessing that an operation took place between 14:55hrs and 15:40hrs on 4 Jul 2005?

However, you mention that you want to find the "earliest anaes start time for each day before 12:00".

That doesn't seem to be possible if all the entries in the 'Anaes' column are dates only (no times).

Perhaps there is some standard time before the 'Start' that the anaes event takes place in which case we subtract that standard time (say, 5 hours) from the start time to infer the data you need?

Apologies if I am missing the point here!

Alan.

**ultra vires** · 09-28-2005, 05:29 AM

Sorry - when I read it again it made little sense to me as well.

column A is just the date 4th July, 11th July etc
Column B is Anaesthetic start time as a date time field 04/07/2005 14:35
Column C is the op finish time as a date time field 04/07/2005 15:25

If I had put column d on the sheet it would also be a list of all the dates in the year
column e is morning start times and column f is afternoon start times.

As I have multiple rows for each day and 1,000s of records I need someway of populating columns e and F with the earliest start times for each day for a morning (before 12:00) session and an afternoon session. {=LARGE(($c$2:$c$9=c27)*($j$2:$j$9),1)} gives me the last finish time for each day and {=1/MAX((C28=$C$2:$C$9)*($J$2:$J$9<>0)*(1/$J$2:$J$9))} will give me the start time.

But how can I specify smallest after 12:00 or latest before 13:00 for each day?