Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|