Find Top 3 Values in a Range Only if another Value is less than N

**mmi** · 06-11-2012, 01:55 PM

The concept seems simple to me, but I'm unable to get the right syntax.

I am trying to find the top 3 values in Column C, so long as Column A is less than 6 years away and Column B is less than 100.

I have attached a picture of the cell format I'm using because I am unable to paste a table format into this window for some silly reason.

061112 EXCEL FORUM.jpg

I tried this, but it didn't work:
=LARGE(IF(A1<(NOW()+2190),C2:C5),1)
=LARGE(IF(A1<(NOW()+2190),C2:C5),2)
=LARGE(IF(A1<(NOW()+2190),C2:C5),3)

(I obviously hadn't tried adding a condition for Column B because I was unable to solve the first condition of the date being less than 6 years away)

Thanks
mmi

**Cutter** · 06-11-2012, 02:03 PM

Maybe

=IF(A1<EDATE(TODAY(),72),LARGE(C2:C5,1),"")

**dilipandey** · 06-11-2012, 02:08 PM

Hi mmi,

See the formula in attached file in column D and let me know if this works.. thanks.
top 3 values.xlsx

regards,
DILIPandey

<click on below 'star' if this helps>

**SteveG** · 06-11-2012, 02:12 PM

Hi,

Try,

=IF(B1>100,"",LARGE(IF(AND($A1<=EDATE(A1,72),B1<100),$C$1:$C$5,""),1))

Change the 1 to 2 or 3 where needed.
HTH
Steve

**mmi** · 06-11-2012, 03:13 PM

Thanks guys, but it's not quite what I'm looking for.

I'm looking to return 4, 2, and 1 because they are the the only 3 values that are under 6 years and under 100.

Steve, your formula seems to be the closest, but it returns 5 as my first value (which should be skipped since it has a date farther than 6 years away.

Also, I'm hoping not to have this formula in every line. I just want to have 3 formulas that lookup the whole table and return the 3 highest values only. Hope this helps clarify?

**GazP** · 06-11-2012, 04:54 PM

This will only work if the dates are in ascending order, as in your example:

Create a named range in the name manager with the following:

=OFFSET(Sheet1!$C$1,0,0,MATCH(TODAY()+2191,Sheet1!$A$1:$A$5,1),1)

Then wherever you want the list on the sheet:

=LARGE(RangeName,1)
=LARGE(RangeName,2)
=LARGE(RangeName,3)

ETA: you can also ignore the 'name' step and do

=LARGE(OFFSET(Sheet1!$C$1,0,0,MATCH(TODAY()+2191,Sheet1!$A$1:$A$5,1),1),1)
etc.

**mmi** · 06-11-2012, 05:13 PM

Thanks, my goal is to get it so that I don't have to sort by date, that way I can just return the 3 highest values for a much longer list.

**GazP** · 06-11-2012, 06:03 PM

A slight amendment to dilipandey's solution and I think it works, entered as array formulae (CTRL+SHIFT+ENTER)

{=LARGE(IF(($A$1:$A$5<=(TODAY()+(6*365))*($B$1:$B$5<100)),$C$1:$C$5,0),1)}
{=LARGE(IF(($A$1:$A$5<=(TODAY()+(6*365))*($B$1:$B$5<100)),$C$1:$C$5,0),2)}
{=LARGE(IF(($A$1:$A$5<=(TODAY()+(6*365))*($B$1:$B$5<100)),$C$1:$C$5,0),3)}

As of today it will return, 2, 1 and 0 as results; tomorrow (to include 11/06/2018) results will be 4, 2 and 1. Adjust the '<=(TODAY()+(6*365)' to suit.

Find Top 3 Values in a Range Only if another Value is less than N

LinkBack

Thread Tools

Rate This Thread

Display

Find Top 3 Values in a Range Only if another Value is less than N

Re: Find Top 3 Values in a Range Only if another Value is less than N

Re: Find Top 3 Values in a Range Only if another Value is less than N

Re: Find Top 3 Values in a Range Only if another Value is less than N

Re: Find Top 3 Values in a Range Only if another Value is less than N

Re: Find Top 3 Values in a Range Only if another Value is less than N

Re: Find Top 3 Values in a Range Only if another Value is less than N

Re: Find Top 3 Values in a Range Only if another Value is less than N

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions