Return 2nd largest value (ignoring duplicates) with criteria

Hi Sqwirtle

See the attached file where I have written an array formula to obtain the desired result. thanks.
second largest.xlsx

Regards,
DILIPandey


<click on below 'star' if this helps>

Sqwirtle,

Using the setup in dilipandey's workbook, this regular formula returns the correct result in both scenarios:

Please Login or Register  to view this content.

haha, I tried following that formula and it gave me a headache. Maybe I've been looking at this problem too long or I just have no idea what your formula does

If possible, can you give a quick explanation what it's basically doing? If that's not possible, it's all good.

Anyways it works! Thanks so much!


Edit: Although it works, I am getting a Circular Reference Warning. Any ideas ?

Here is a curveball, what if I wanted to do the same thing but instead of returning that 2nd largest number I wanted to return the corresponding number in column C

A	B	C
1	25	101
1	40	102
1	40	103
2	25	104
2	35	105
2	35	106
2	35	107
2	35	108
2	35	109

I originally took Tigeravatar's formula and added another index in front of it with a match for the row which worked. The problem though is that I am still getting that underlying circular argument and it is bogging down the whole spreadsheet very badly.

Any suggestions?

That formula shouldn't cause a circular reference (unless you have the formula somewhere in A1:B9). I'd have to see an example workbook that is experiencing the problem.

As for what the formula does (warning, gets kind of long)...

ROW(INDIRECT("1:"&ROWS(B1:B9))))

This basically creates an array of sequential numbers, 1 through the number of rows in the data. In this case, the data is 9 rows, so it creates the following array:
{1,2,3,4,5,6,7,8,9}

That array is fed to the Large() function with the purpose of returning the numbers in descending order. In Excel terms:
Large(index_of_values,{1,2,3,4,5,6,7,8,9})

So it returns Large(_,1) then Large(_,2), etc to get all of the values in descending order.



The index_of_values is generated from this part:
INDEX((A1:A9=2)*B1:B9,)

Basically, that says if A1:A9=2, then return B1:B9. A1:A9=2 will return an array of True/False values. Anything True will return a 1 and anything False will return a 0. That gets multiplied to B1:B9, so anything that met the criteria will return successfully, and anything that was false will return a 0 (because it gets multiplied by 0). So now we have the values according to the criteria. The Large() function discussed earlier then sorts them in descending order so that the max value is first.



Next is the CountIf part, which is used to get the 2nd largest value:
COUNTIF(B1:B9,MAX(INDEX((A1:A9=2)*B1:B9,)))+1

You'll notice the use of the Index() with the use of (A1:A9=2)*B1:B9. This is the second time this formula we're using it. That's because this time, that array of values isn't being sorted, its being evaluated by Max(). That returns the largest value. The largest value is fed to the CountIf function, so now we know exactly how many times that max value occurs. This is useful because the Large() function earlier sorted the values in descending order. So if the max value is repeated 4 times, the first 4 values are the max value. So we add +1 to the count so that we return the next number in the series, which is the 2nd largest number ignoring duplicates. Now that we have which position it is, we can feed that to the overarching Index() formula:


Index(array_of_sorted_values, position_number)
The array_of_sorted_values is generated from the Large(Index(),Row()) function. The position_number is from the CountIf(value,Max(Index())+1) function. So now, out of that sorted list, it grabs the 2nd highest number.


Hopefully that explanation helps.

Originally Posted by Sqwirtle

Here is a curveball, what if I wanted to do the same thing but instead of returning that 2nd largest number I wanted to return the corresponding number in column C

Didn't see this post until after I was done writing up the explanation. Formula changes quite a bit, here you go:

Please Login or Register  to view this content.

Originally Posted by Sqwirtle

The problem though is that I am still getting that underlying circular argument and it is bogging down the whole spreadsheet very badly

As mentioned in the explanation post, I'd need to see an example workbook that is experiencing the problem in order to diagnose it.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread