Having problem with cell formula....need assistance please

Discussion in 'iWork Forum' started by jdmartin, Dec 7, 2011.

  1. jdmartin
    Offline

    jdmartin iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    NE Indiana, USA
    Ratings:
    +0 / 0
    I am working on an Ipad Numbers spreadsheet, and one of the calculations I need to do is to determine the number of days between two dates.

    I am using the DATEDIF function, but the problem I'm having is this....

    My start date is in A2. Then I have dates from A3 through A45. The formula that I have that calculates the first date difference
    is = (DATEDIF (A2-A3,"D")) The date in A2 is the "start date" and all other dates need to reference A2 as the start date. Now I want to "fill" the remaining cells of the column that contain the days difference value, in this case that column is "R". I tap on the R3 cell, select the "fill" option, and then drag down the border until I have included all the cells that I want to contain this formula. The problem is that A2 does not remain the start date in the formula. Without having to enter the formula manually into every cell in the R column, how can I fill or paste this formula so that it becomes A2-A4, A2-A5, A2-A6, and so on....

    Thanks....

    Jim
    Last edited: Dec 7, 2011
  2. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    Use the INDIRECT function to get the data from A2, instead of doing it directly. (DATEDIF (INDIRECT("A2")-A3,"D"))

    In order to enter the "A2" into the INDIRECT function you will select the address cell, then use the "abc" key to enter the address. You can leave the addr-style default, or delete it. Don't forget to delete the extra comma if you do.

    I tested this with a simple sheet, as you described. Because the INDIRECT function does not directly point at a cell, it stays the same in when replicated via a Fill.
  3. jdmartin
    Offline

    jdmartin iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    NE Indiana, USA
    Ratings:
    +0 / 0
    Outstanding ! I'll give this a whirl this evening and let you know the results. Thanks again for all your help.
  4. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    No problem.
  5. jdmartin
    Offline

    jdmartin iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    NE Indiana, USA
    Ratings:
    +0 / 0
    Ok, I'm still doing something wrong....please take a look at the formula I tried to enter, per your directions, and let me know where the syntax error is. I'm still learning how to use Numbers to enter formulas.....not exactly what I'm used to seeing in Excel......Let me know what I need to do to fix this

    photo(1).PNG
  6. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    Looks like you accidentally deleted one of the parenthesis and tried to re-add it manually. You can only use the parenthesis key for expressions. Using them to bracket the formula variables causes a syntax error.

    Attached Files:

  7. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    Going back and looking at my first reply, I see it is my fault. That minus sign should have been a comma. (DATEDIF (INDIRECT("A2"),A3,"D")) instead of (DATEDIF (INDIRECT("A2")-A3,"D")).

    That's what I get for trying to type it instead of just taking a screen shot like I usually do.
  8. jdmartin
    Offline

    jdmartin iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    NE Indiana, USA
    Ratings:
    +0 / 0
    works perfect now !!! thanks for continuing to help me out on this one....

    photo(1).PNG
  9. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    Not a problem. Compared to some of the formulas people have asked for help with, this one was easy. :D

    Just wish I'd caught my typo.
  10. jamesmaine
    Offline

    jamesmaine iPF Noob

    Joined:
    Apr 28, 2012
    Messages:
    1
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I'm also having problems with formulas in numbers. I used to know excel basics but cannot figure out formula use in numbers.
    1A is dates, in the Sun, April 25, 2012 format. I am attempting several things:
    1- build a formula that will let me know the frequency of events on Sun, Mon, Tues etc.
    2- enter data into other fields in the row ONLY of there is data is column A (ie A39 is a new record, I'd place today's date, then in another field it would calculate age based in DOB-Today() ). Struggling with what should be simple formulas. Any help would be great
  11. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    Hmm.

    So you want a column of dates, from which you want a summary of how many fall on each day of the week (2 on Sunday, 3 on Monday, etc.). Is that right? I'm not sure how to go about that. I may look into it later, no promises, but it's getting late tonight.

    The second one shouldn't be too hard you just need to enclose your first formula in an IF statement. You select the if-expression variable and tap the cell you are testing then enter ≠0. You then tap the if-true variable and enter your formula. I chose the DATEDIF formula as the easiest way to compute a difference in years. If you need something else to happen when the if-expression is false you would enter it in the place of the if-false variable. Otherwise ignore or delete that part of the function. For the heck of it I entered the string "N/A", so that when there is no entry in field B8, N/A appears in cell E8.

    AgeToday.jpg

    By selecting a calc method of "Y" i get my results in whole years. If B8 is a birthday then E8 will be that person's age as of the current day..

    Hold in mind that using the TODAY formula like it did in cell D8 means it is always today. If you want todays date entered, and for it to remain static, you have to enter it manually.

    To save a boringly repetitive column you could simply replace the D8 address in the DATEDIF statement with the TODAY function.
  12. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,407
    Thanks Received:
    2,033
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,317 / 1
    Ok, I finally got around to trying to count the days, so to speak. I could not find a direct way of doing so. Every attempt to apply a formula instead of a condition to the COUNTIF function failed, and I had less luck with the SUMIF function.

    The best I could come up with is a two step. First you have to convert the date to weekday using either the WEEKDAY function or the DAYNAME function. After that you can use the COUNTIF function to test against the resulting column; text or numeric respectively.

    As you can see from the screen shot below I used the DAYNAME function and tested against the text string. Also created a similar setup for the months.

    B-DayCount.jpg

    If there is anything you don't understand please let me know and I will elaborate.

Share This Page

Search tags for this page
apple numbers datedif function
,
apple numbers indirect function
,
apple numbers mac function for todays date -excel
,
countif
,

datedif now() numbers ipad

,
datedif numbers 09
,
greyed
,
how to calculate number of days in apple numbers between two dates
,
indirect function numbers mac
,
indirect mac numbers
,

ipad numbers datedif

,
ipad sumif syntax numbers ipad
,
iwork datediff function
,
iwork numbers sheets the formula contains a syntax error
,
iwork numbers the address function use
,
iwork numbers this formula cannot reference to its own cell
,
numbers equation to calculate age ipad
,
numbers ipad fill formula reference
,
this formula cannot reference its own cell
,
weekday function