Excel formula help please

Excel formula help please

Didn't find your answer?

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.

avatar
By MARKJET
22nd Mar 2010 15:47

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

Thanks (0)
avatar
By IanBrewster
22nd Mar 2010 15:51

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.

Thanks (0)
avatar
By User deleted
22nd Mar 2010 15:56

THANK YOU!

THANK YOU ALL VERY MUCH!

You have just saved me HOURS!

Simples when you know how.

Thanks (0)
avatar
By User deleted
22nd Mar 2010 15:57

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

Thanks (0)
avatar
By User deleted
22nd Mar 2010 15:58

Beaten to it!

PC

Thanks (0)
avatar
By Richard Willis
23rd Mar 2010 08:49

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.

Thanks (0)
avatar
By nogammonsinanundoubledgame
25th Mar 2010 13:05

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

Thanks (0)
avatar
By User deleted
25th Mar 2010 13:29

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.

Thanks (0)
avatar
By nogammonsinanundoubledgame
25th Mar 2010 13:57

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

Thanks (0)