excel template for calculating trial balances and adjusting entri.
 

columns B and C are the trial balance numbers (debits and credits)(sometimes
blank) columns D and E are adjusting entries (always positive)(debits,
credits)
columns F and G should return correct value and should hold the formula's.
I have been trying with :
=IF(ISBLANK(B9:E9),"",IF(AND(B9=C9,D9=E9),B9+D9, IF(AND(B9C9,E9D9),B9-E9,IF(AND(C9B9,D9C9),D9-C9,""))))

but maybe it could be shorter?

Perhaps try, in F9
=ROUND(MAX(0,B9-C9+D9-E9),0)
and in G9
=-ROUND(MIN(0,B9-C9+D9-E9),0)
then if you do not want zeros to be displayed, use custom number format to
suppress zeros or surround teh whole formula with an IF statement that
returns "" if it evaluates to zero (I prefer the former method). I recommend
using the ROUND function as above as sometimes a simple addition that should
return an integer result can, because of the finite capacity of the
processor, give rise to a rounding error in around the 15th decimal place
and so will not test accurately for zero if left unrounded.

"yesterdaytoday11" wrote in
message ...
columns B and C are the trial balance numbers (debits and
credits)(sometimes
blank) columns D and E are adjusting entries (always positive)(debits,
credits)
columns F and G should return correct value and should hold the formula's.
I have been trying with :

=IF(ISBLANK(B9:E9),"",IF(AND(B9=C9,D9=E9),B9+D9, IF(AND(B9C9,E9D9),B9-E9,
IF(AND(C9B9,D9C9),D9-C9,""))))

but maybe it could be shorter?

this does work as tested,, very nice and compact, thanks alot for your help

"Jack Schitt" wrote:

Perhaps try, in F9
=ROUND(MAX(0,B9-C9+D9-E9),0)
and in G9
=-ROUND(MIN(0,B9-C9+D9-E9),0)
then if you do not want zeros to be displayed, use custom number format to
suppress zeros or surround teh whole formula with an IF statement that
returns "" if it evaluates to zero (I prefer the former method). I recommend
using the ROUND function as above as sometimes a simple addition that should
return an integer result can, because of the finite capacity of the
processor, give rise to a rounding error in around the 15th decimal place
and so will not test accurately for zero if left unrounded.

"yesterdaytoday11" wrote in
message ...
columns B and C are the trial balance numbers (debits and
credits)(sometimes
blank) columns D and E are adjusting entries (always positive)(debits,
credits)
columns F and G should return correct value and should hold the formula's.
I have been trying with :

=IF(ISBLANK(B9:E9),"",IF(AND(B9=C9,D9=E9),B9+D9, IF(AND(B9C9,E9D9),B9-E9,
IF(AND(C9B9,D9C9),D9-C9,""))))

but maybe it could be shorter?