5 Replies Latest reply: Feb 7, 2014 11:42 PM by Barry
jimps274 Level 1 Level 1 (0 points)

I could use help creating a formula.

 

I have a excel file that I want to move to numbers.  In it, I track the date last put in a column.

 

Each Column is for a date, and each row for an item.   When I put a date in a the column, I am trying to create a forula that woudl return the date last input.

 

That is probably all confusing....but here is the excel formula that works, that does not work in numbers.

 

=IF(B80>0, INDEX(G80:FH80,MATCH(9.99999999999999E+307,G80:FH80)), "Not Played")

 

Or a second one I have also used

 

=INDEX(C5:JZ5,MATCH(9.99999999999999E+307,C5:JZ5))

 

Honestly - I don't understand the forumla, I got it from someone - I just know it works in Excell, but not in numbers.

 

Below is a picture of what it comes out like in Excell.  The column that is not working is the "Last played"

 

Does that make sense?  Any Numbers experts out there that can help me create a forumla in Numbers for this?

 




MacBook Pro, Mac OS X (10.7.1)
  • SGIII Level 5 Level 5 (6,280 points)

    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

  • Badunit Level 6 Level 6 (11,400 points)

    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.

  • jimps274 Level 1 Level 1 (0 points)

    Thanks Scott.  Ok - here are two image files.  Obviosly the file is much bigger - each column is a date - so it goes on for dozens and dozens of columns - which is why I want the "last played" to tell me which date it was.Screen Shot 2014-02-07 at 3.29.15 PM.png

     

    Screen Shot 2014-02-07 at 3.26.06 PM.png

  • SGIII Level 5 Level 5 (6,280 points)

    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

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

    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