separating positive and negative numbers into two columns

[daflondon]

Hi all
Hope you can help me here. I have a spreadsheet with 3 columns - date,
transaction, amount. The figures in the amount column contain positive
and negative numbers. I want to separate these into two columns so
that I can have credit and debit columns. Apart from moving each one
individually, how can I achieve this result?
Thanks
David

 

[Edwin Tam]

You can do this in two key steps:
1) Using the IF formula to separate the data
2) Then, use the "Paste Special" feature of Excel

Below are the detailed steps:
- For example, in C2, you have your first amount.
So, in D2, you type the formula:
=IF(C2>=0,C2,"")
In E2, you type the formula:
=IF(C2<0,C2,"")
- Use the mouse to select the range D2:E2.
- Drag the handle at the right-button corner of your selected cells
downward until it reaches the last row of your data
- With the cells which contains the formula still selected, COPY the cells.
- From the Edit menu, choose Paste Special
- Choose to paste the "Values". Click OK.
- Finally, if you're satisfied with the result, you may delete the
original amount column (if you no longer need it).


The IF formula above actually performs a logic test on the sign of the
amounts. It is a simple but very powerful Excel worksheet function. For more
information about the formula, you may refer to the online help.


Regards,
Edwin



On 08/10/2007 12:55 AM, in article
(e-mail address removed), "daflondon"

Hi all
Hope you can help me here. I have a spreadsheet with 3 columns - date,
transaction, amount. The figures in the amount column contain positive
and negative numbers. I want to separate these into two columns so
that I can have credit and debit columns. Apart from moving each one
individually, how can I achieve this result?
Thanks
David

 

[JE McGimpsey]

Hi all
Hope you can help me here. I have a spreadsheet with 3 columns - date,
transaction, amount. The figures in the amount column contain positive
and negative numbers. I want to separate these into two columns so
that I can have credit and debit columns. Apart from moving each one
individually, how can I achieve this result?

If you want both the credit and debit columns to be positive numbers,
and your negative numbers are formatted with a - sign, a quick way:

Select the column. Choose Data/Text to Columns. Select Delimited and
Click Next. Enter "-" (without quotes) in the "Other" input box and
click Finish.

 

[daflondon]

Thank for such clear instructions, it worked a treat.
Can you explain how the formula works... I get the If C2>=0 but what
does the "" mean?
thanks
David

 

[Edwin Tam]

IF(C2>=0,C2,"")
Means, Test whether C2 is larger or equal to 0. If TRUE, give the answer C2.
If FALSE, give empty.

"" actually means nothing.
You can experiment by putting something else inside the quotation marks. For
example:
If you put "I am a boy", the formula will give the answer "I am a boy" if C2
is smaller than 0.

Regards,
Edwin


On 09/10/2007 4:43 AM, in article
(e-mail address removed), "daflondon"

Thank for such clear instructions, it worked a treat.
Can you explain how the formula works... I get the If C2>=0 but what
does the "" mean?
thanks
David

 

[daflondon]

Fantastic, thanks very much...I think I can't start experimenting with
IF commands. Like you say, a potentially powerful tool!
cheers
David

 

[Carl Witthoft]

IF(C2>=0,C2,"")
Means, Test whether C2 is larger or equal to 0. If TRUE, give the answer C2.
If FALSE, give empty.

"" actually means nothing.
You can experiment by putting something else inside the quotation marks. For
example:
If you put "I am a boy", the formula will give the answer "I am a boy" if C2
is smaller than 0.

A fine point: assigning "" to a cell assigns a sort-of nothing, but it
is not the same as a cleared cell. This rarely matters unless you're
trying to graph the data.

 

[daflondon]

Thanks for this.... perhaps further on down in my journey of exploring
IF commands this might mean something to me!