+ Reply to Thread
Results 1 to 4 of 4

Rank list excluding certain criterias

  1. #1
    Registered User
    Join Date
    02-23-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    9

    Rank list excluding certain criterias

    Help!

    I have 3 columns:
    a) Security Types (e.g Equity, Currency, Options, etc...)...column E in the example
    b) Long or Short positions (denoted by "L" and "S")...column H in the example
    c) P&L performances (in %)...column U in the example

    I'm trying to rank a list of stock's monthly P&L performances, but excluding ones that are short positions (column b) and ones that are currency positions (column c). I did the following formula

    =IF(AND(H12="L",E12<>"Currency"),1+SUMPRODUCT(($H:$H=H12)*($U:$U>U12)),"N/A")

    I copied down this column and although it excludes currency from the ranking, it merely 'skips' the number. for example, a currency P&L would have had a ranking of 7, but I have excluded that. However, the ranking will go 1...2...3...4...5....6...8 for the equities.

    Can anyone help on this?

    thanks

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Rank list excluding certain criterias

    Based on your sample formula the implication is that you are using XL2007 or above (you can't use entire column references in SUMPRODUCT/Arrays pre XL2007)

    Assuming the above to be the case you should be using COUNTIFS rather than SUMPRODUCT - the former is significantly more efficient.

    In the example you're not excluding Currency from the rank.

    Please Login or Register  to view this content.
    If you opt to persist with SUMPRODUCT for whatever reason I would strongly advise you restrict the precedent ranges as much as possible.

  3. #3
    Registered User
    Join Date
    02-23-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    9

    Re: Rank list excluding certain criterias

    Thank you so much! works exactly as intended

  4. #4
    Registered User
    Join Date
    02-23-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    9

    Re: Rank list excluding certain criterias

    ok i'm an idiot...got it
    Last edited by karvan; 02-24-2011 at 12:37 AM. Reason: solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1