I have a simple formula =IF(C2<C3,1," "). This formula works perfectly when
I type it in. I have a spreadsheet with over 10000 lines and want to copy
this formula down. When I copy the formula and paste it, it no longer gives
me the correct output. What can I do?

That depends what you actually want to compare.
If, in the line below the original you want C3<C4 then it should work, but
if eg you always want to compare to C2 (eg C2<C4) then you need to tell
Excel it's an absolute reference ie enter C2 as $C$2 (or if you only want to
lock the row number C$2).

--
Ian
--
"sgausti" wrote in message
...
I have a simple formula =IF(C2<C3,1," "). This formula works perfectly
when
I type it in. I have a spreadsheet with over 10000 lines and want to copy
this formula down. When I copy the formula and paste it, it no longer
gives
me the correct output. What can I do?

Make sure you have calculation set to automatic:
tools|options|calculation tab

And I'd use this formula instead:

=if(c2<c3,1,"")

cells that contain spaces look empty, but can screw up other formulas if you're
not careful:

=if(d2="","nothing in there","not empty")

sgausti wrote:

I have a simple formula =IF(C2<C3,1," "). This formula works perfectly when
I type it in. I have a spreadsheet with over 10000 lines and want to copy
this formula down. When I copy the formula and paste it, it no longer gives
me the correct output. What can I do?

--

Dave Peterson

Thanks Dave... This was my problem. Didn't have automatic selected.

"Dave Peterson" wrote:

Make sure you have calculation set to automatic:
tools|options|calculation tab

And I'd use this formula instead:

=if(c2<c3,1,"")

cells that contain spaces look empty, but can screw up other formulas if you're
not careful:

=if(d2="","nothing in there","not empty")

sgausti wrote:

I have a simple formula =IF(C2<C3,1," "). This formula works perfectly when
I type it in. I have a spreadsheet with over 10000 lines and want to copy
this formula down. When I copy the formula and paste it, it no longer gives
me the correct output. What can I do?

--

Dave Peterson

Where is this formula on? Are they referencing the same relative
positions?

For example, if the Value is in D3, and you drag the formula down to
D4, the new formula will be =IF(C3<C4,1," ")


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=556122