excel template for calculating trial balances and adjusting entri.

Y

yesterdaytoday11

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(B9>C9,E9>D9),B9-E9,IF(AND(C9>B9,D9>C9),D9-C9,""))))

but maybe it could be shorter?
 
J

Jack Schitt

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.
 
Y

yesterdaytoday11

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top