 # RankIF - what formula do i need

• Attached spreadsheet - i need to rank teh return in column F, by long and short in columns D and E

IE a rankif sort of function, rank if long in D and short in E

## Files

• Re: RankIF - what formula do i need

You could use Sumproduct

=IF(\$G6="Long",(SUMPRODUCT((\$G\$6:\$G\$25="Long")*(\$F6<\$F\$6:\$F\$25))+1),"") and copy down for Long Ranking
=IF(\$G6="Short",(SUMPRODUCT((\$G\$6:\$G\$25="Short")*(\$F6<\$F\$6:\$F\$25))+1),"") and copy down for Short Ranking

See attached workbook

## Files

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

• Re: RankIF - what formula do i need

Perfect. Is there a way of getting it to rank such that duplicates are not ranked the same number but are say 10, 11, 12 when 10 and 11 are the same, as opposed to 10, 10, 12?

• Re: RankIF - what formula do i need

Problem is that how do you decide which should be ranked higher/lower when the number is the same? First occurrence gets the higher ranking? Basically 10 is the same as 10 obviously, which of the 10's should be ranked higher?

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

• Re: RankIF - what formula do i need

if they are the same number then they are of equal value to me, but the ranking doenst matter either way around.

Shouldnt be an issue as I have so much data it is unlikely...but just to make sure I wanted to see if it is doable.

• Re: RankIF - what formula do i need

I don't think it is because there is nothing to make numbers that are equal have a different ranking - there would need to be some other factor to decide the ranking. Perhaps someone else has an idea/solution but at this point in time I cannot see a way to differentiate between two numbers that are equal.

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

• Re: RankIF - what formula do i need

OK I have a new problem with this. I want the formula to exclude rows that have no return from the rank. So even if you have long or short, but no actual number to rank, I want it to be ignored. As it stands, I have around 50 items that have long / short but no actual return number, and they are being included in the rank so messing up my vlookups which bring back the top ten and bottom ten

• Re: RankIF - what formula do i need

Just add in an "AND" condition

=IF(AND(\$G6="Long",\$F6>0),(SUMPRODUCT((\$G\$6:\$G\$25="Long")*(\$F6<\$F\$6:\$F\$25))+1),"") - copy down
=IF(AND(\$G6="Short",\$F6>0),(SUMPRODUCT((\$G\$6:\$G\$25="Short")*(\$F6<\$F\$6:\$F\$25))+1),"") - copy down

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

• Re: RankIF - what formula do i need

Still doesn't work.

See in attachment. The Day Rank column now has a blank where there is no exposure data - exactly what i want. BUT it still seems to include it in the rank because there are no 1, 2, 3, 4 etc in my rank...the blanks ahve been included as those values

you see what I mean?

## Files

• Re: RankIF - what formula do i need

Maybe I can leave as is and get my top 10 and bottom ten using the SMALL and LARGE functions

• Re: RankIF - what formula do i need

Does this formula work in G4, copied down?

=IF(OR(K4="",L4=""),"",COUNTIFS(\$K\$4:\$K\$58,K4,\$L\$4:\$L\$58,">"&L4)+1)

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: RankIF - what formula do i need

Quote from NBVC;700822

Does this formula work in G4, copied down?

=IF(OR(K4="",L4=""),"",COUNTIFS(\$K\$4:\$K\$58,K4,\$L\$4:\$L\$58,">"&L4)+1)

No, it gives some duplicate values when it should not.

I have used the small and large functions instead as I mentioned above - The issue is now solved.

Thanks

• Re: RankIF - what formula do i need

In the sample you gave, I did not see any duplicates.

There were rankings of 1 to 5 for "Short" and 1 to 29 for "Long"...

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: RankIF - what formula do i need

NBVC your solution works perfectly - and my understanding is that this would be a better solution as it is much faster than SUMPRODUCT- is that what you have found with COUNTIFS v's SUMPRODUCT?
This sample size is probably not large enough to notice the difference but on large data sets I have always found SUMPRRODUCT much slower I copied the data without the formulas and filtered/sorted the results to show that COUNTIFS does work - If OP has large data set I would go with NBVC's solution

## Files

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

• Re: RankIF - what formula do i need

Hi Anthony,

Thanks for checking and concurring that the solution does work.

Yes, Countifs() is much more efficient than Sumproduct(). And you might not notice much difference in small datasets.

Where there is a will there are many ways. Finding one that works for you is the challenge!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!