Moving negative numbers to another cell automatically

[Amaranty]

Hello,

I have an issue with two columns in Excel 2007. The first one has a bunch of numbers that run from top to bottom and I would like to move all of the negative numbers from the first column to the second one. I understand that this can be done with the IF command but I am quite clueless what the formula should be.

I hope someone can help me out.

Thanks in advance!

[L-Drr]

if your total list of values is in column A, type this in B1 and drag down:

=IF(A1<0;A1;"")

Maybe my semicolons should be commas for you.
This does not remove the negative data from the first column. If you also want to create a colomn with only the positive values, put this in the first cell and drag down:

=IF(A1>=0;A1,"")

[Amaranty]

Hi,

Thanks for the help! If I want to use the second function in the first column to remove the negative data then how can I do it if I have a mathematical formula in the first column?

[Pepe Le Mokko]

You'll need VBA, otherwise you can use filters

[L-Drr]

You can't use formulas to replace data, because they will always keep referring (or trying to refer) to the original data.
So if you want this to be done realtime, use VBA like Pepe said.
If you only have to do this once (in a while), you could also use the formulas in another column and then copy that column and past as values. That way you'll have the right data without referrals, but if your list is updated, you'll have to do the copy&pasteasvalues again...

[greggpetersen75]

It would make more sense to keep the original data the same the create an if statement in Column B that is for positive numbers

Please Login or Register  to view this content.

then In column C have the Formula

Please Login or Register  to view this content.

That way you keep the integrity of data.

The last option is to use Copy and Paste Special Values to remove the formula.