In an Excel spreadsheet (Excel 2003) I have a huge column of figures some plus, some minus - all I want to do is to have column 2 for all debits and column 3 for all credit figures.
Please would someone tell me what formula I can use to do this?
Many thanks.
Replies (9)
Please login or register to join the discussion.
there's probably and easier way but...
This is how I would do it...
If the first number is A1 ,
in B1 I would put.
=if(a1>0,a1,"")
and in C1
=if(a1<0,a1,"")
Then highlight and copy them down to the bottom of the row of figures
Formula
You could try something like this
in A2 =IF(A1<0, A1, "")
in A3 =IF(A1>=0, "", A1)
then copy down to the bottom of your data.
THANK YOU!
THANK YOU ALL VERY MUCH!
You have just saved me HOURS!
Simples when you know how.
IF
There may be a neater way, but the one I use is:
Say your entries are in A2, A3 etc
In column 2: IF(A2>0,A2,"")
In column 3: IF(A2<0,-A2,"")
You can also use MAX and MIN functions, which work just as well.
PC
Not your original query BUT...
One I use a lot in order to sort a column of debits and credits so as to match like figures is (in a new column): -
=IF(A1<0,-A1,+A1)
This returns a +ive figure for both Drs & Crs which, when sorted pulls all similar numbers, + or -, together.
Have to say ...
... that in every excel application that I have ever used that has any connection with Pacioli, I have always found it more convenient for the subsequent manipulation of data to leave the balances in one column, their sign depicting whether credit or debit. Separating it out into columns dedicated to the sign of the value usually (in my experience) is nothing more than an unjustified knee-jerk assumption that whatever was the clearest way of managing data when written out by hand on a large sheet of paper must necessarily be the best way of managing data in a computerised medium, whilst in the end providing no benefits whatsoever.
Just my tuppence-worth.
With kind regards
Clint Westwood
Well said
Clint I dont disagree - but when one exports data from VT the result is a debit and a credit column on one spreadsheet.
In that case ...
... I can see the possible benefits of then merging the columns of the exported data. It is going the other direction that mystifies me.
With kind regards
Clint Westwood