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
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
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
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
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?
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.
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
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?
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)
Re: RankIF - what formula do i need
Quote from NBVC;700822Does 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"...
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
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.
Don’t have an account yet? Register yourself now and be a part of our community!