Skip navigation

Chart with time durations do not work

884 Views 12 Replies Latest reply: Nov 4, 2012 9:49 PM by George Coghill RSS
colicoid Calculating status...
Currently Being Moderated
May 25, 2012 1:06 PM

I have the following columns:

 

Labels
Startdate
Enddate
Duration
Period AJan 1 2012Jan 2 20121d
Period BJan 1 2012Jan 3 20122d

 

The Duration column is calcualted by subtracting the startdate column from the enddate column. Not a problem for Numbers so far.

 

Now I want to show the duration column as a simple bars chart along with the lables so that you can see which period had the longest duration.

When I create the chart it I get the X axis but the Y axis is blank.

 

Is this a bug?

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 25, 2012 1:37 PM (in response to colicoid)

    From the Numbers '09 User Guide:

     

    "Chapter 7    Creating Charts from Numerical Data"

     

    Note that the type of data is specified—"from Numerical Data."

     

    You'll need to convert your Duration data in column D to Numerical data.

     

    Current formula: =C-B

     

    Revised: =DUR2DAYS(C-B)

     

    Picture 6.png

     

    Regards,

    Barry

  • George Coghill Level 2 Level 2 (475 points)
    Currently Being Moderated
    Aug 16, 2012 9:58 PM (in response to Barry)

    This helped me too. I am trying to create a sleep log. Perhaps you can help with another related issue. I have two columns, one for sleep time, and one for wake time. In another table, I am calculating the duration by subtracting the one from the other, however for future dates the function is returning zero, but I would prefer for it to just be blank if both the cell references are blank.

     

    In other words, I only want a value in the duration table column if I have entered both a sleep time and a wake time in the other table. Is this possible? If so, how do I do this? I have tried SUMIF but didn't do the trick (or I did it incorrectly). It still returned a zero for the future dates.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Aug 17, 2012 12:06 AM (in response to George Coghill)

    Hi George,

     

    Sleep time in column B, wake time in column C of Table 1, Duration of sleep in column B of Table 2:

     

    B2: =IF(AND(LEN(TABLE 1::B)>0,LEN(TABLE 1::C)>0),TABLE 1::C-TABLE 1::B,"")

     

    Note: The 'times' in Table 1 are Date and Time values, with the date set to the date you make the entry (unless you include a date in the entry). You will get correct results IF each time is entered on the date that it occurs. eg. If you entered a sleep time of 8:00 PM on August 15, 2012, then entered the wake time, 8:00 AM on August 16, you would get a (correct) result of 12h 00m.

     

    Regards,

    Barry

  • George Coghill Level 2 Level 2 (475 points)
    Currently Being Moderated
    Nov 3, 2012 2:49 PM (in response to Barry)

    Barry,

     

    Thanks! Worked perfectly. I'm trying to use your example a step further. In order to graph the results, I need to convert them to duration using the DUR2HOURS function.

     

    Based on your system above, I'm using =DUR2HOURS(TABLE 2::B) [I think that's correct, my chart has column names).

     

    I deduced how you achieved the If/Then conditons using >0 and the "" to return blank data if not True. But my formula is generating a syntax error:

     

    =IF(DUR2HOURS(TABLE 2::B)>0),DUR2HOURS(TABLE 2::B),"")

     

    Any insight on what I am doing wrong here? Thanks!

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 3, 2012 4:49 PM (in response to George Coghill)

    Hi George,

     

    The most common cause I've seen for syntax errors in complex formulas is an unequal number of left and right parentheses. (and/or parentheses in the wrong location(s)).

     

    Here's your formula:

     

    =IF(DUR2HOURS(TABLE 2::B)>0),DUR2HOURS(TABLE 2::B),"")

     

    Here's a corrected version of that formula, to which I've added colour to emphasise the opening and closing parentheses for each function:

    Picture 15.png

    Copying the formula containing the error into a word processor, then picking out the various components using colour and/or other formatting can help track down syntax errors. In this case, it left me with an unpaired closing parenthesis after the zero, which I removed before copyng the image above.

     

    Regards,

    Barry

  • George Coghill Level 2 Level 2 (475 points)
    Currently Being Moderated
    Nov 4, 2012 12:45 AM (in response to Barry)

    Barry,

     

    First, your color-coding is genius and should be added to Numbers itself. Makes troubleshooting much easier!

     

    I've applied your correction to no success. Because Numbers adds the colored "pill" outline, it confuses me when I see the plain text versions (does the "pill" include the parenteses or not?)

     

    So I've linked an image of the actual formula as I see it for a single cell:

     

    /___sbsstatic___/migration-images/202/20200036-1.png

    Again, I appreciate your time and help on this!

  • George Coghill Level 2 Level 2 (475 points)
    Currently Being Moderated
    Nov 4, 2012 12:48 AM (in response to George Coghill)

    I'm not sure if that image link is showing up so here is the link to the image file:

     

    http://cl.ly/KeYD

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 4, 2012 1:35 AM (in response to George Coghill)

    Hi George,

     

    As you suspected, your inserted image did not come through. Here it is, from a screen shot of the file linked in your follow-up message:

     

    Picture 16.png

     

    The lozenge contains only the cell (or range) reference. References entered as text generate the lozenge only if there are no syntax errors to that point in the formula.

     

    You note only that this is 'not working.' Does the formula generate an error flag (red triangle)? If so, what is the error message presented when you click (once) on the triangle?

     

    I suspect, from trying DUR2HOURS() on an empty cell, that the message is ' The function "DUR2HOURS" expects a duration, but found "." '

     

    Since the purpose of your formula is to convert duration values above zero hours to a number and to return a null string for noo-duration values, you might try this:

     

    IFERROR(DUR2HOURS(Total Sleep::'11/3/12"),"")

     

    This formula will return a zero value if the target cell contains a zero hours duration value, but will throw an error if the cell contains a non-duration value. IFERROR is used as an error trap t catch this error, and return an empty string should an error occur.

     

    Regards,

    Barry

  • George Coghill Level 2 Level 2 (475 points)
    Currently Being Moderated
    Nov 4, 2012 6:22 AM (in response to Barry)

    Barry,

     

    I was not aware that the red triangle when clicked offered up more details, and you are correct -- it informed me that it was expecting a duration but the cell contained a string.

     

    Your solution completely worked, thanks once again. I'm still new to spreadsheets and I know my lack of awareness of what kinds of functions are available can hold me back.

     

    Thanks again for the help, hopefully I can repay the favor if you ever need Photoshop or Illustrator help, more up my alley than Numbers

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 4, 2012 11:32 AM (in response to George Coghill)

    George,

     

    Apple provides a couple of excellent resources with Numbers '09:

     

    The Numbers '09 User Guide is a general guide to the application. The first three chapters should be considered 'required reading.' After those, check the titles, skim the first page or two, then consider it a reference to use when you need to know/learn more about a particular topic.

     

    The iWork Formulas and Functions User Guide lists all the functions supported in Numbers, with a description of each, its syntax, and one or more examples of how the function may be used in practice.

     

    Both guides are searchable PDF documents. Both may be downloaded via the Help menu in Numbers '09.

     

    Regards,

    Barry

  • George Coghill Level 2 Level 2 (475 points)
    Currently Being Moderated
    Nov 4, 2012 9:49 PM (in response to Barry)

    Barry,

     

    I've poked around a bit in the manual, but I think you are right about sitting down and reading. Thanks for the tips on the chapters to focus on.

     

    I've only really gotten into spreadsheets recently, and have discovered I really enjoy manipulating data

     

    Thanks again for all your help!

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.