Currently Being ModeratedMay 25, 2012 1:37 PM (in response to colicoid)
Currently Being ModeratedMay 26, 2012 6:44 AM (in response to Barry)
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.
Currently Being ModeratedAug 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.
Currently Being ModeratedAug 17, 2012 12:06 AM (in response to George Coghill)
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.
Currently Being ModeratedNov 3, 2012 2:49 PM (in response to 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!
Currently Being ModeratedNov 3, 2012 4:49 PM (in response to George Coghill)
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:
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.
Currently Being ModeratedNov 4, 2012 12:45 AM (in response to 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!
Currently Being ModeratedNov 4, 2012 1:35 AM (in response to George Coghill)
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:
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.
Currently Being ModeratedNov 4, 2012 6:22 AM (in response to 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
Currently Being ModeratedNov 4, 2012 11:32 AM (in response to George Coghill)
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.
Currently Being ModeratedNov 4, 2012 9:49 PM (in response to 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!