+ Reply to Thread
Results 1 to 13 of 13

Top 10 values for date period

  1. #1
    Registered User
    Join Date
    03-14-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Top 10 values for date period

    Is there an easy way to have an array formula that will display the top10 values for a certain date period? Note: this period must be variable.

    The static way works of course: =SMALL(C1855:C1977,ROW()-(ROW($J$1)-1)) but I need the possibility to change the date range (C1855:C1977) based on other input, secondly, my date range can vary, values are not necessary adjacent. Can this be done with a formula? Writing a macro to calculate the new array is possible, but it's something I'd like to avoid in this case.

    Thanks
    Last edited by NBVC; 03-16-2011 at 07:40 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Top 10 values for date period

    You can insert an IF statement within

    e.g

    =SMALL(IF(C1855:C1977<=X1,IF(C1855:C1977>=X2,C1855:C1977)),ROW()-(ROW($J$1)-1))

    where X1 and X2 contains the start/end dates

    confirmed with CTRL+SHIFT+ENTER

    is this what you mean?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-14-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Top 10 values for date period

    Not quite, since I cannot know the date range (C1855:C1977) you are using in your formula. Valid values could as well be in C2004 or C1593.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Top 10 values for date period

    I am not following... can you post a sample workbook showing what it is you are after?

  5. #5
    Registered User
    Join Date
    03-14-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Top 10 values for date period

    I have table with layout:

    A B C D
    Date Amount Description User

    Table is just a list with all values under each other, table can of course be sorted by date ascending.

    Now, I want to create a dashboard on a new worksheet that allows for selecting month/year and that will update all the graphs including a "Top 10" table with biggest amounts in descending order.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Top 10 values for date period

    With my formula above, you can expand the range as you wish... the formula looks for all rows with dates in the range you determine, and finds SMALLest (use LARGE if you want largest) of cell values that have a date in that range.

    If you post an actual sample workbook, it might be clearer.

  7. #7
    Registered User
    Join Date
    03-14-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Top 10 values for date period

    Please find an example in attachment.
    Attached Files Attached Files

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Top 10 values for date period

    I think it would be:

    =SMALL(IF(A:A>=$G$6,IF(A:A<=$G$7,B:B)),ROWS($A$1:$A1))

    but I strongly suggest you reduce the range size to absolute max necessary, as array formulas over large ranges become very inefficient.

  9. #9
    Registered User
    Join Date
    03-14-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Top 10 values for date period

    We are getting there. My mistake in making the copying the wrong rows in my example file. There is a minor error in your formula since, it does not add up the 'k' value. Of course, now I want the corresponding description and user to appear in a column next to the value. Should I do this with the index formula?
    Attached Files Attached Files

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Top 10 values for date period

    My formula was meant to be placed in a single cell. Then confirmed with CTRL+SHIFT+ENTER and then copied down 10 rows....

    See attached column I.

    To get the associated description:

    Please Login or Register  to view this content.
    copied down and then to next column to get associated user.

    Note: The above assumes only one match per amount.

    If there are multiple amounts that can be the same, then you need to use:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER in one cell, then copy down and over to next column
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-14-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Top 10 values for date period

    This is excellent! Really what I've been looking for. I think this will help a lot of other folks too. So if you are looking for topx or top 10 values table, example above is the way to go.

  12. #12
    Registered User
    Join Date
    03-14-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Top 10 values for date period

    Hi,

    could it be that it does not completely 'obey' the date criteria?

    I've attached an example.

    Regards
    Attached Files Attached Files

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Top 10 values for date period

    In that case, the safest thing is you would need to incorporate the date criteria in column J and K

    e.g.

    In J11:

    Please Login or Register  to view this content.
    and in

    K11:

    Please Login or Register  to view this content.

+ 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