Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
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 { } |
#4
|
|||
|
|||
Perfect! Thanks for your help!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Provide a list of all cells that meets this criteria | Excel Worksheet Functions | |||
Validation Drop Down List if cell meets criteria | Excel Worksheet Functions | |||
Adding data in a list that meets a specific criteria | Excel Worksheet Functions | |||
Retrieving an Item from a List that Meets Multiple Criteria | Excel Worksheet Functions | |||
Retrieving a Value from List that meets multiple Criteria | Excel Discussion (Misc queries) |