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

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.

:

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

...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.

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

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

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

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