8 Replies Latest reply: Apr 19, 2013 9:28 PM by Barry
Wnlight Level 1 Level 1 (0 points)

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
  • 1. Re: I want the max date but only look at rows with a certain category value.
    Barry Level 7 Level 7 (29,180 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

  • 2. Re: I want the max date but only look at rows with a certain category value.
    Barry Level 7 Level 7 (29,180 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

  • 3. Re: I want the max date but only look at rows with a certain category value.
    Jerrold Green1 Level 7 Level 7 (28,995 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

  • 4. Re: I want the max date but only look at rows with a certain category value.
    Wnlight Level 1 Level 1 (0 points)

    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

  • 5. Re: I want the max date but only look at rows with a certain category value.
    Wnlight Level 1 Level 1 (0 points)

    Barry,

    I have your first solution incorporated already.  I would send you my tables, but I do not know how.  It is working fine!

    Thanks again,

    Warren

  • 6. Re: I want the max date but only look at rows with a certain category value.
    Barry Level 7 Level 7 (29,180 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

  • 7. Re: I want the max date but only look at rows with a certain category value.
    Wnlight Level 1 Level 1 (0 points)

    Thanks again, Barry.

     

    Is there a way (in iPad Numbers) to turn the background red for the cell that contains a date more than two months old?

    I didn't see such a function.

     

    Warren

  • 8. Re: I want the max date but only look at rows with a certain category value.
    Barry Level 7 Level 7 (29,180 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