» Finding the Score that Was the Nth Highest for a Specified Team

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.

Hi DeepField,

[QUOTE=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.[/QUOTE]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.

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.

Hi,

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

Please advice.

Thanks in advance

Hi mushthaag,

[QUOTE=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.[/QUOTE]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.

[QUOTE=Clara]Hi,

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

Please advice.

Thanks in advance[/QUOTE]

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

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

Well this large function doesnt work

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

Well this large function doesnt work

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

Hi ultra vires,

[QUOTE=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[/QUOTE]The example above should do it for the second largest.

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

HTH,

Alan.

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

[QUOTE=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.[/QUOTE]

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?

Hi ultra vires,

[QUOTE=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?[/QUOTE]Sorry, but I don't quite understand.

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

Thanks,

Alan.

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.

Hi ultra vires,

[QUOTE=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.[/QUOTE]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.

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?

[QUOTE=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?[/QUOTE]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.

[QUOTE=Alan]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.[/QUOTE]

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?

Hi ultra vires,

[QUOTE=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?[/QUOTE]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.

[QUOTE=Alan]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)}


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

HTH,

Alan.[/QUOTE]

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

Hi ultra vires,

[QUOTE=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!![/QUOTE]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.

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?

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?