Help creating a formula

Hi jimpos274,

Don't see a screenshot. If you are trying to post one, you can do it this way:

1. shift-command-4

2. holding mouse down drag crosshairs across the area you want to capture, and release

3. a file named something like Screen Shot 2014-01-29 at 7.27.28 AM.png will be placed on desktop

4. in editor here click camera icon, 'Choose File', navigate to the file on the Desktop and click, click 'Choose', click 'Insert Image'

Sometimes the "camera" doesn't work the first time, so you just redo the steps in 4.

SG

Excel stores dates as numbers. That Excel formula is looking for the largest number in the column then giving you the content of that cell so as to preserve the date format.

In Numbers you can use the MAX function.

It looks as if your dates may not necessarily be in ascending order.

If you're looking for the last date listed in the column, as opposed to the latest date in the column, and your dates are in column G, then you could try:

=INDEX(G,COUNTA(G),1)

If you have defined Header row(s), then you may need to add +1, etc.

=INDEX(G,COUNTA(G)+1,1)

SG

Hi Jim,

If I'm reading your table correctly, it appears that all dates a particular song has been played are listed in the same row as the song title.

If that's the case, your formula in G3 would be:

=MAX(H3:XX3)

With the letter identification of the last column in the table replacing XX.

The formula would then be filled down from G3 to the last row containing a song title.

Two other suggestions:

Numbers does not really perform well with large tables. The underlying model for a Numbers spreadsheet is a number of small, single purpose tables using no extra rows or columns.

As a first order revision to this table, I would suggest splitting the table after column G, then adding a single header column before what was column H. The seven column table consisting of columns A to G would then becone a summary table. The only entered data in this table would be the labels "Worship song (Wed)" through to "Last Played" in row 1. Row 2 would be empty to match the position of the song titles on this table with the positions on the larger table, and both row 1 and row 2 would be set as Header Rows to permit their being frozen in place, allowing other roll to scroll behind these two.

Everything else on that table, including the song titles, would be formula generated from data on the larger table.

The larger table would become a data collection table, containing only one or two formulas—the formula in row 2 that counts the number of songs used on each day, and possibly one to generate the weekly dates in row 1. All other data on that table would be directly entered by typing from the keyboard, pasting after copying from an outside source, or by inserting usng a script or menu item.

The main advantage here is that this arrangement would lessen th liklihood of accidently altering formulas during data entry by moving all data entry to a separate table, and by making it possible to lock the summary table, where no data entry takes place.

A second suggestion would be to limit the number of columns in the Data table to one for the song titles plus enough to collect the data for a period of either one or two years.

The main question to determine whether this would be a useful step is 'Do you have a use for the actual dates on which a song was played over two years ago, or is the number of times it was played in each year sufficient at that temporal distance?'

Making this change would involve some additional annual maintenance, with a couple of hitches that I haven't yet looked at, so unless the ie=dea interests you, I'll let the second 'sleeping dog' lie.

Regards,

Barry