# Off Topic > Tips and Tutorials >  >  Finding The Nth Greatest Value Meeting Criteria

## sue

Hi,

i have tried out your example and enter =LARGE(($B$2:$B$6=E2)*($C$2:$C$6),2) and hit it with shift+Ctrl+Enter... but it return a '0' value. please advice is there any other formula that can be use?

----------


## sue

Hi,

i have tried out your example and enter =LARGE(($B$2:$B$6=E2)*($C$2:$C$6),2) and hit it with shift+Ctrl+Enter... but it return a '0' value. please advice is there any other formula that can be use?

----------


## Clara

Hi,

I can get the result. It shows #num! in cell F2

Please advice.

Thanks in advance

----------


## Manjeet

Well this large function doesnt work

{=LARGE(($B$2:$B$6=E2)*($C$2:$C$6),2)}

----------


## Manjeet

Well this large function doesnt work

{=LARGE(($B$2:$B$6=E2)*($C$2:$C$6),2)}

----------


## ExcelTip

Problem:

Listed in Range A2:C6 are players (column C) and their matching teams and scores (columns D:E).
How could we create a formula that will retrieve the second highest score in column C matching each team in column E?								

Solution:	

Using the LARGE function in an array formula, as follows:
{=LARGE(($B$2:$B$6=E2)*($C$2:$C$6),2)}									

(To create Array formula: select the cell after typing the formula,press F2 and then press Ctrl+Shift+Enter)

----------


## DeepField

I am having trouble finding the LARGE() function in my SPANISH version of Excel. Is there a dictionary somewhere or a way to find out the equivalent names in Spanish?

In Access you can type the English function and the application understand and translate it, but the same functionality was not included in Excel.

----------


## Alan

Hi DeepField,





> I am having trouble finding the LARGE() function in my SPANISH version of Excel. Is there a dictionary somewhere or a way to find out the equivalent names in Spanish?
> 
> In Access you can type the English function and the application understand and translate it, but the same functionality was not included in Excel.



One option may be to create a workbook with the language set to English version with a LARGE formula, and then open it with the language set to Spanish version.

No idea if it will work, but seems like it might!

HTH,

Alan.

----------


## mushthaag

i world like to know other kind of formula like sorting like below

2
1
0
-1
-2
-3

if any body know this kind of formular could you please send me it.

----------


## Alan

Hi mushthaag,





> i world like to know other kind of formula like sorting like below
> 
> 2
> 1
> 0
> -1
> -2
> -3
> 
> if any body know this kind of formular could you please send me it.



I don't see what you mean.  That data is already sorted, so there is nothing to do?

In general, to sort, you can look at the RANK function.

HTH,

Alan.

----------


## andre_wium

> Hi,
> 
> I can get the result. It shows #num! in cell F2
> 
> Please advice.
> 
> Thanks in advance



Hi Clara,

You have to enter the formula as an array.  That is, after you have typed the formula into the cell, don't hit Enter, but hit Ctrl+Shift+Enter together.  Then it will work.

Andre Wium

----------


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

----------


## Alan

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

----------


## Guillermo G

The equivalent of LARGE function in spanish is K.ESIMO.MAYOR.

=K.ESIMO.MAYOR(A2:A5;2)

When you do not know the spanish equivalent of an english function you can make a simple macro that will tell you:

Sub Macro1()
  Range("a1").Value = "=large(a2:a5,2)"
End Sub

----------


## ultra vires

> 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

Hi 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

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

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



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

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?

----------


## Alan

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



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.

----------


## ultra vires

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



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?

----------


## Alan

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

----------


## ultra vires

> Hi ultra vires,
> 
> 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)}
> ...



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

----------


## Alan

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

----------

