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

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

  1. jdmartin

    jdmartin
    Expand Collapse
    iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    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
     
    #1 jdmartin, Dec 7, 2011
    Last edited: Dec 7, 2011
  2. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,428
    Thanks Received:
    4,274
    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

    jdmartin
    Expand Collapse
    iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    0
    Outstanding ! I'll give this a whirl this evening and let you know the results. Thanks again for all your help.
     
  4. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,428
    Thanks Received:
    4,274
    No problem.
     
  5. jdmartin

    jdmartin
    Expand Collapse
    iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    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

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,428
    Thanks Received:
    4,274
    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

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,428
    Thanks Received:
    4,274
    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

    jdmartin
    Expand Collapse
    iPF Noob

    Joined:
    Dec 7, 2011
    Messages:
    9
    Thanks Received:
    0
    works perfect now !!! thanks for continuing to help me out on this one....

    photo(1).PNG
     
  9. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,428
    Thanks Received:
    4,274
    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

    jamesmaine
    Expand Collapse
    iPF Noob

    Joined:
    Apr 28, 2012
    Messages:
    1
    Thanks Received:
    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
     

Share This Page



Search tags for this page
apple numbers mac function for todays date -excel
,
countif
,
datedif now() numbers ipad
,
im having problem with charting on my ipad please help
,
indirect function numbers mac
,

ipad numbers datedif

,
iwork numbers sheets the formula contains a syntax error
,
iwork numbers the address function use
,
syntax error on numbers ipad
,
weekday function