Skip navigation

I want the max date but only look at rows with a certain category value.

361 Views 8 Replies Latest reply: Apr 19, 2013 9:28 PM by Barry RSS
Wnlight Calculating status...
Currently Being Moderated
Apr 17, 2013 7:55 PM

I want a way to get the max date but only look at rows with a certain category value - ignoring the other rows.  My detail table contains expenditures including date (col A) and category (col D) the number of rows will increase with expenditures over time.  My summary table will have a cell for each category and display the last expense date for that category using a functionality that I must ask of you, dear community.

 

I am using the latest numbers on an iPad (4) with IOS6.

 

Secondarily, I would like to add another cell in the summery table with the value (col E) of the last expense for each category.

 

Thank you,

Warren

iPad (4th gen) Wi-Fi, iOS 6.0.2, Email gmail
  • Barry Level 7 Level 7 (29,095 points)

    Hi Warren,

     

    Provided the entries remain in ascending order by date, a simple Lookup on the categories will return the most recent entry for each.

    Picture 5.png:

    Values in column D of Most recent are entered values.

     

    Values in columns A, B and C are calculated using this fomula, entered in A2, then filled down to A5 and right to column C:

     

    A2: =LOOKUP($D2,Main :: $D,Main :: A)

    One disadvantage here is that the simple method doesn't allow for restricting the report to a range of dates within the table. More on that later.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)

    ...later...

     

    With the addition of an auxiliary column to the Main table, a second header row to the Most recent table, and a minor modification to the formula on the second table, the tables can handle a range of dates set by entering the first and last date into A1 abd B1 respectively of the summary table, Most recent.

    Picture 7.png

    Note that the selected range, shown with a green background in the auxiliary column, does not contain any category B expenses. Using LOOKUP, this would result in a repeat of the January 6 expense bering listed in this row. Switching to VLOOKUP, which can be set to require an exact match, allows the result shown—if there are no expenses in a given category, the formula returns "none" (or whatever message you substitute for "none" in the formula in that column).

     

    Formulas:

    Main::A2: =IF(OR(B<Most recent :: $A$1,B>Most recent :: $B$1),"x"&E,E)

     

    Fill down to the end of column A.

     

    This column must be located on the left side of the table (ie. must be column A), but may be hidden.

     

    Most recent::A2: =IFERROR(VLOOKUP($D,Main :: $A:$D,COLUMN()+1,FALSE),"none")

    "FALSE" will display as "Exact match" in Numbers's formula editor.

     

    Fill down to the end of the table and right to column C.

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (28,215 points)

    Hi Warren,

     

    Thanks for stopping by with an interesting question. You've posted in the Mac Numbers forum, with a question about your iPad version of Numbers, but the answers you get here should apply. The illustrations and descriptions may vary a bit.

     

    My approach would be to add a hidden auxiliary column in the main table for each category. In these hidden columns, the dates will be repeated, but only for the categary that column is assigned to. In the summary table then you need only look for the latest date in the corresponding hidden column. Using Barry's first post's illustration as a starting point, your first hidden auxiliary column would contain the formula:

     

    =IF(D = "A", A, "")

     

    This expression says: IF the category column contains the category name "A", then repeat the contents of column A (teh date) here, else write a null string.

     

    The to find the latest date for a category A entry, you would write in your summary table:

     

    =MAX(E)

     

    where E is the hidden column for category A dates.

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)

    Wnlight wrote:

     

    Barry, thanks.

    As I see it, the auxiliary column would have to be refreshed for each category, or perhaps there be such a column for each category.  As I stated in my original question, I am limited to Numbers on my iPad until a time when I may secure a Mac.  This answer appears to me to be a Mac Numbers solution.  I will not require date ranges until next year, as my data grows.

    Warren

    Hi Warren,

     

    The auxiliary column (4lookup) updates automatically from the dates set in A1 and B1 of the summary table. It indexes all four categories (in my example), and would do the same for a larger number of categories as well.

     

    Mainly useful in filtering out expenses more recent than you want to see or determining if there has been at least one expense within a given category within a specified time period.

     

    Developed on a Mac, but should also work on iPad, provided the functions are supported in Numbers for iOS.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)

    In Numbers '09 (Mac) the way is through conditional formatting, and the conditional format rules are set in the Cell inspector. I don't know if conditional formatting is supported in Numbers for iOS.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.