Top 5 list if data meets a criteria

[sroeder]

Hi all,

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

Thanks for your help

+-------------------------------------------------------------------
|Filename: Top 5 quotes.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=840
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[Spencer101]

Hi all,

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

Thanks for your help!

Hi,

Enter the below formula in cell C12 and confirm it as an array formul
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


+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[sroeder]

Perfect! Thanks for your help!

Hi,

Enter the below formula in cell C12 and confirm it as an array formul
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------