RankIF - what formula do i need

Important Notice


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.

    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

    Sumproduct_Ranking.xlsx 10.91 kB – 144 Downloads

    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?

    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

    CountifRanking.xlsx 39.02 kB – 131 Downloads

    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!

Register Yourself Login