12 Replies Latest reply: Nov 4, 2012 9:49 PM by George Coghill
Level 1 (5 points)

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?

• Level 7 (29,610 points)

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)

Regards,

Barry

• Level 1 (5 points)

Thanks a lot Barry, that worked.

One additional comment to anyone else that might try this.

If you have your copy of Numbers in a different language the function names will vary. So look it up.

• Level 2 (475 points)

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.

• Level 7 (29,610 points)

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

• Level 2 (475 points)

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!

• Level 7 (29,610 points)

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

=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:

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

• Level 2 (475 points)

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:

Again, I appreciate your time and help on this!

• Level 2 (475 points)

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

http://cl.ly/KeYD

• Level 7 (29,610 points)

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:

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

• Level 2 (475 points)

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

• Level 7 (29,610 points)

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

• Level 2 (475 points)

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!