Home |
Search |
Today's Posts |
#1
|
|||
|
|||
dragging a formula
I have 2 columns, both with dates. I am trying to calculate the difference in
years between the two. I am using the formula =YEAR(C4)-YEAR(B4) This seems to work well, my problem is that when I enter the formula for the first few cells manually, then try to click and drag the formula down the entire column, the results are just repeats of those first few cells I entered manually. Clicking on the new cells, the formula is correct, and if I click in the formula bar then hit enter, the correct result then appears. Is there a way to drag the formula all the way down the column without having to manually click in every formula bar to have the correct result appear? 11/3/1948 8/31/2005 57 6/29/1954 8/31/2005 51 3/11/1965 8/31/2005 40 10/1/1926 8/31/2005 79 If I click and drag that third column down, all I will get is 57,51,40,79,57,51,40,79 over and over. |
#3
|
|||
|
|||
1547 Thank you's :)
"Peo Sjoblom" wrote: Toolsoptionscalculation and check automatic -- Regards, Peo Sjoblom (No private emails please) "P Bates" <P wrote in message ... I have 2 columns, both with dates. I am trying to calculate the difference in years between the two. I am using the formula =YEAR(C4)-YEAR(B4) This seems to work well, my problem is that when I enter the formula for the first few cells manually, then try to click and drag the formula down the entire column, the results are just repeats of those first few cells I entered manually. Clicking on the new cells, the formula is correct, and if I click in the formula bar then hit enter, the correct result then appears. Is there a way to drag the formula all the way down the column without having to manually click in every formula bar to have the correct result appear? 11/3/1948 8/31/2005 57 6/29/1954 8/31/2005 51 3/11/1965 8/31/2005 40 10/1/1926 8/31/2005 79 If I click and drag that third column down, all I will get is 57,51,40,79,57,51,40,79 over and over. |
#4
|
|||
|
|||
My Pleasure,
Note that a dates like 12/31/04 and 01/01/05 will return 1 using your formula, do you want that to happen? If not you can use DATEDIF, not documented except in excel 2000 and here http://www.cpearson.com/excel/datedif.htm -- Regards, Peo Sjoblom (No private emails please) "P Bates" wrote in message ... 1547 Thank you's :) "Peo Sjoblom" wrote: Toolsoptionscalculation and check automatic -- Regards, Peo Sjoblom (No private emails please) "P Bates" <P wrote in message ... I have 2 columns, both with dates. I am trying to calculate the difference in years between the two. I am using the formula =YEAR(C4)-YEAR(B4) This seems to work well, my problem is that when I enter the formula for the first few cells manually, then try to click and drag the formula down the entire column, the results are just repeats of those first few cells I entered manually. Clicking on the new cells, the formula is correct, and if I click in the formula bar then hit enter, the correct result then appears. Is there a way to drag the formula all the way down the column without having to manually click in every formula bar to have the correct result appear? 11/3/1948 8/31/2005 57 6/29/1954 8/31/2005 51 3/11/1965 8/31/2005 40 10/1/1926 8/31/2005 79 If I click and drag that third column down, all I will get is 57,51,40,79,57,51,40,79 over and over. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions |