Thread: Finding The Nth Greatest Value Meeting Criteria

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.

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.

Originally Posted by ultra vires

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?

Sorry for the slow response.

I think I am with it now.

This may be what you need to show the earliest start time in a morning session for a given day:

{=MIN(IF(((A3:A21=D1)*(B3:B21-D1))>0,IF(((A3:A21=D1)*(B3:B21-D1))<0.5,TRUE,999),999)*B3:B21)}

Where A1:C21 are the data you posted above (three columns!) and D1 contains the date you are checking against.

Note that this is an array formula, entered without the braces using Shift-Ctrl-Enter.

If D1 = 8 Jul 2005 then the formula returns:

09:10hrs on 8 Jul 2005 (you can strip out the date if you want by subtracting the integer part or just format to show only the time as you prefer).

If there is no start time in that day, the formula returns a large number that excel cannot interpret as a date / time value and the display shows ###### no matter how wide you make the cell.

Does that do what you need?

Alan.

Hi ultra vires,

Originally Posted by ultra vires

Great thanks - what about if I want the last time in the morning, or first time in the afternoon? would it be easier for me to email you the s/sheet?

Last time in the morning is this I think:

{=MAX(IF(((A3:A21=D1)*(B3:B21-D1))>0,IF(((A3:A21=D1)*(B3:B21-D1))<0.5,TRUE,999),999)*B3:B21)}

First time in the afternoon (defined as starting at noon):

{=MIN(IF(((A3:A21=D1)*(B3:B21-D1))>0,IF(((A3:A21=D1)*(B3:B21-D1))>=0.5,TRUE,999),999)*B3:B21)}


Please do test those to make sure I have understood correctly.

HTH,

Alan.

Hi ultra vires,

Originally Posted by ultra vires

I get a lot of strange times that aren't in the table! Thanks to your help I can do the first time and last time in the day. I still can't work out how to do the first time after 12.00 and the last time before 12.00. I have attached a zip file as I realise my explanation of what I am trying to do is as clear as mud! I hope you can help as I am sooo frustrated to be so close and yet so far!!

It doesn't work, I think, because your data is different from what you posted aboove.

In your post of 27-Sep-2005 at 09:02 PM, you showed your three fields as all containing a date (integer) component.

In the attached file, the second and third columns do not contain date information as far as I can see - they are decimals (times) only.

That is why the formulae I posted probably doesn't work.

Alan.