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?
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 can get the result. It shows #num! in cell F2
Please advice.
Thanks in advance
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)}
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)
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,
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.Originally Posted by DeepField
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 mushthaag,
I don't see what you mean. That data is already sorted, so there is nothing to do?Originally Posted by mushthaag
In general, to sort, you can look at the RANK function.
HTH,
Alan.
To help us help you, try to do the following:
1) Be precise about what you want to do, and provide a sample of your data / inputs - exactly as they are.
2) State the formula(e) / code that you have tried. People are happy to help , but if you haven't even given it a go, you are less likely to get help, or the help you get will be very basic.
3) State the results you are getting from your formula(e) / code already.
4) State the outputs that you *want* to be getting.
Hi Clara,Originally Posted by 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
Hi ultra vires,
The example above should do it for the second largest.Originally Posted by ultra vires
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
Thanks for that very helpful.Originally Posted by Alan
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,
Sorry, but I don't quite understand.Originally Posted by ultra vires
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,
I am not sure how your data works.Originally Posted by ultra vires
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?
Sorry for the slow response.Originally Posted by ultra vires
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.
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?Originally Posted by Alan
Hi ultra vires,
Last time in the morning is this I think:Originally Posted by ultra vires
{=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.
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!!Originally Posted by Alan
Hi ultra vires,
It doesn't work, I think, because your data is different from what you posted aboove.Originally Posted by ultra vires
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks