Condtional Range - Drawdown Ranking

**dan_hibiki** · 04-07-2011, 11:18 AM

hi guys,

i'm kinda stuck here. attached u'll find a sheet that contains data in coulumns a to c.

i wanna do the following: i want to create a ranking of the five highest values in column a and the corresponding values in columns b and c.
a contains drawdowns, b the drawdownperiod and c the date the drawdown began.

for example: highest value in column a is 5,20196, the drawdown period is 456 days, which started on 05/17/2006.
in excel i can easily put up a ranking, but with that given data i have two problems:
1. i find the drawdown of 5,20196 several times in the same period (456 days)
2. the second highest value in column a is in the same drawdownperiod (456 days), which i am not interested in.

i need the second highest value in column a, which is not in the same drawdownperiod of the highest value (5,20196 days) ...and so on.
that should be 4,3843 (period=64 days, starting 13.11.08)

so far i tried alot of stuff with sumproduct and max if, but it doesn't work.
hope u can help me on that! :/

many thx

dan

**Cutter** · 04-07-2011, 12:00 PM

This array formula will give you the MAX() found in A:A where B:B value does not equal 456.

=MAX(IF(B:B<>456,A:A))

entered with Ctrl+Shift+Enter

you can replace the 456 with a cell refernce that holds that value

**NBVC** · 04-07-2011, 12:01 PM

Note: can't use whole column references in Excel 2003 with that formula though

**Cutter** · 04-07-2011, 12:10 PM

Thanks NBVC
I was just going to add that because when I saved the sample file that message popped up.

Here is the array formula for the 3rd highest:

=MAX(IF((B1:B3000<>G2)*(B1:B3000<>G3),A1:A3000))

where G2 holds the 456 value and G3 holds the 64 value (from previous formula)

adjust the ranges as necessary

Here is your file with the formulas I used to get the top 3 results

**Marcol** · 04-07-2011, 02:24 PM

Another approach maybe?

Try this
In D2

=IF(C2<>C3,IF(C2="","",C2),"")

In E2

=IF(OR(D2="",D2=0),"",MAX(INDIRECT("A"&ROW()-(COUNTIF(C:C,C2)-1)&":A"&ROW())))

In E2

=IF(E2<>"",IF(RANK(E2,E:E)<=$G$1,RANK(E2,E:E),""),"")

Drag/Fill all three Down

Put a number in G1 to call the Last Rank you are interested in, then filter Column F for non-blanks

A method without filters is in Sheet2

Use the grouping buttons to show hidden columns.

Do you need Column B, or is in an automatic download?

Hope this helps.