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

[jdmartin]

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

 

[twerppoet]

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.

 

[jdmartin]

Outstanding ! I'll give this a whirl this evening and let you know the results. Thanks again for all your help.

 

[twerppoet]

No problem.

 

[jdmartin]

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

 

[twerppoet]

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.

 

[twerppoet]

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.

 

[jdmartin]

works perfect now !!! thanks for continuing to help me out on this one....

 

[twerppoet]

Not a problem. Compared to some of the formulas people have asked for help with, this one was easy.

Just wish I'd caught my typo.

 

[jamesmaine]

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

 

[twerppoet]

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.



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.

 

[twerppoet]

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.



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

 

[Teeeeeennna]

I need help with formula for grading.

 

[twerppoet]

View attachment 78729
I need help with formula for grading.

IF (conditional), (if true), if false)

The first field needs to be the entire conditional, another words it should be (AK3 <=9). The way you've entered it the conditional is just whether AK3 exists. Then you're saying the 'if true' action is <=9, a conditional that makes no sense as an action. That is proobably a syntax error. However the main syntax error is that you end up trying to use too many fileds for the IF statment. By making the conditional the second field, you push the grade result to the (if false) field, and the nested IF statement to a non-existant field.

The first part of your forumula should look like this (within the limits that typing on the standar keyboard permts)

IF AK3 <= 9, "C+", IF . . .

 

[Teeeeeennna]

Thank you...