+ Reply to Thread
Results 1 to 9 of 9

ranking formula for filtered data

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    ranking formula for filtered data

    good time of a day to all!

    if anyone knows how to get the ranking function working correctly while ranking the filtered numbers? I hid/filtered the extra rows that are not relevant (but they still contain numbers) and was about to rank the filtered stuff but unfortunately the formula takes into account all rows and columns that sit into reference area...


    pls someone advise

    my understanding that if we work with filtered data we need to use some kind of relative connections or smth??

    thanks a lot in advance!

    Ivan

  2. #2
    Registered User
    Join Date
    10-15-2012
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: ranking formula for filtered data

    Hi, you can't just rank directly after filter. After filter, you can copy the data to another sheet and then rank it.

  3. #3
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: ranking formula for filtered data

    well yeah this is a way...but generally, can you possibly use a formula that will know that the data is filtered and will only sort the visible part of the area???

  4. #4
    Registered User
    Join Date
    10-15-2012
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: ranking formula for filtered data

    By the way, if you don't want to copy it to another sheet, you can use esCalc-an Excel helper. Because, you can choose Hide or Delete when filter. If you choose hide, the rank will contain other irrelevant rows and columns like Excel. But if you choose Delete, it won't contain. But in Excel, when you filter, the data is hided as default but not deleted.

  5. #5
    Registered User
    Join Date
    10-15-2012
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: ranking formula for filtered data

    Quote Originally Posted by ivansamsonov View Post
    well yeah this is a way...but generally, can you possibly use a formula that will know that the data is filtered and will only sort the visible part of the area???
    There is no such formula as I know. If there is, such formula will be complicated. If your data is not so big (millions of), copy it to another sheet is the simplest way.

  6. #6
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: ranking formula for filtered data

    ok thanks a lot mate !

  7. #7
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: ranking formula for filtered data

    Hi ivansamsonov

    Do a search on the forum, may be of some benefit!
    http://www.excelforum.com/excel-gene...d-results.html
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  8. #8
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: ranking formula for filtered data

    I like the way questions being treated here, thanks a lot, Mr. Kevin!

    will surely use the link provided

  9. #9
    Registered User
    Join Date
    10-15-2012
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: ranking formula for filtered data

    Hi ivansamsonov,
    Besides the SUMPRODUCT function mention in Kevin, I'd like to add another ways listed in another forum: http://www.mrexcel.com/forum/excel-q...ered-list.html

+ 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