Top 5 list if data meets a criteria
 

Hi all,

I am trying to create a top 5 list but i only want data to be pulled into the list if it meets a criteria from another column. I have attached an example sheet but basically I have 3 columns...a quote number, days since quoted, and quote amount. I want to create a top 5 list showing the top 5 quote amounts IF it has been quoted within 30 days. I am using the LARGE function to list the top 5 quote amounts and the INDEX/MATCH to show which quote number is associated with that quote amount and tried throwing in an IF statement to make it do what I want but I am having some difficulty. If there is a formula that can do this that would be great since I'm not too experienced with macros.

Thanks for your help!

Top 5 list if data meets a criteria
 

Hi,
Am Thu, 11 Apr 2013 14:21:46 +0000 schrieb sroeder:

I am trying to create a top 5 list but i only want data to be pulled
into the list if it meets a criteria from another column. I have
attached an example sheet but basically I have 3 columns...a quote
number, days since quoted, and quote amount. I want to create a top 5
list showing the top 5 quote amounts IF it has been quoted within 30
days. I am using the LARGE function to list the top 5 quote amounts and
the INDEX/MATCH to show which quote number is associated with that quote
amount and tried throwing in an IF statement to make it do what I want
but I am having some difficulty. If there is a formula that can do this
that would be great since I'm not too experienced with macros.

try in C12:
=LARGE($C$2:$C$8,ROW(A1))
and copy dowmn

in B12:
=INDEX($A$1:$A$8,MATCH(C12,$C$1:$C$8,0))
and copy down


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi,

Enter the below formula in cell C12 and confirm it as an array formula by pressing Ctrl, Shift & Enter rather than just Enter then copy down.

=LARGE(IF(B$2:B$8<=30,C$2:C$8,""),A12)

You will see if it's entered correctly as it will be in curly brackets { }

Perfect! Thanks for your help!