• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get top 10 values of selected Quarters

D

devil

New Member
I have a database which have data of various Quarters. Now I want a formula to get a top ten values of selected Quarter. User has a cell where he can select quarter and that cell is linked to formula. I have tried to did it with If and rank but couldn't do it as excel dont accepts more then 8 if in a formula. Need suggestions. :)
 
Hi,


This would work if you can add a helper column by side of the data. Assuming A1:B16 occupies the data, column A is quarter and B have corresponding data, column C would be helping column. Enter this formula:


=IF(A1=$D$2,B1,"")


where D2 is the cell referring to the quarter you are looking for.The resulting report is located in cells F1:G4 using formula:


=LARGE($C$1:C16,F1) where f1 could be 1~10 and is used to fetch first, second, third ( and so forth) highest values.
 
You must log in or register to reply here.
Back
Top