+ Reply to Thread
Results 1 to 24 of 24

Finding The Nth Greatest Value Meeting Criteria

  1. #1
    sue
    Guest

    largest Nth number

    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?

  2. #2
    sue
    Guest

    largest Nth number

    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?

  3. #3
    Clara
    Guest

    It doesn't work for large

    Hi,

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

    Please advice.

    Thanks in advance

  4. #4
    Manjeet
    Guest

    Large Function

    Well this large function doesnt work

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

  5. #5
    Manjeet
    Guest

    Large Function

    Well this large function doesnt work

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

  6. #6
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Finding The Nth Greatest Value Meeting Criteria

    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)

  7. #7
    Registered User
    Join Date
    06-02-2005
    Posts
    1

    Function names in spanish

    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.

  8. #8
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: DeepField

    Hi DeepField,

    Quote Originally Posted by 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.

  9. #9
    Registered User
    Join Date
    09-20-2005
    Posts
    1

    Arrow

    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.

  10. #10
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: mushthaag

    Hi mushthaag,

    Quote Originally Posted by 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.
    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.

  11. #11
    Registered User
    Join Date
    09-21-2005
    Posts
    1

    RE: It doesn't work for large

    Quote Originally Posted by Clara
    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

  12. #12
    Registered User
    Join Date
    01-13-2004
    Posts
    14

    How would you do it for the smallest value

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

  13. #13
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: ultra vires

    Hi ultra vires,

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

  14. #14
    Registered User
    Join Date
    09-02-2005
    Posts
    1

    Large Spanish Function

    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

  15. #15
    Registered User
    Join Date
    01-13-2004
    Posts
    14
    Quote 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?

  16. #16
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: ultra vires

    Hi ultra vires,

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

  17. #17
    Registered User
    Join Date
    01-13-2004
    Posts
    14
    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.

  18. #18
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: ultra vires

    Hi ultra vires,

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

  19. #19
    Registered User
    Join Date
    01-13-2004
    Posts
    14
    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?

  20. #20
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41
    Quote 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.

  21. #21
    Registered User
    Join Date
    01-13-2004
    Posts
    14
    Quote Originally Posted by 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.
    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?

  22. #22
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: ultra vires

    Hi ultra vires,

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

  23. #23
    Registered User
    Join Date
    01-13-2004
    Posts
    14
    Quote Originally Posted by 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.
    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!!
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: ultra vires

    Hi ultra vires,

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1