Thread: Finding The Nth Greatest Value Meeting Criteria

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)}

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?

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,

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.

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.

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

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

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?