David Peller
asked on
Two fold question.
Due to an accounting error in my personal checkbook (which has been ongoing for over a year), I am finally attempting to find the error. As such, I am using Lotus 1,2.3 to copy all entries in my check book ( in effect mirroring my check register) and then to proof it against my bank monthly statements. In order to “speed up” the process, I have been using the @SUM button, which has functioned quite well until about half way through the entries.
In several instances there have been negative results so that the balance appears as ($xxx.xx). The second (payment/debit) entry, when attempting to subtract this from the above balance, results in a positive/added sum. For the purpose of clarity, I have cut and pasted below that which actually appears.
Col. A Col..B Col. C
($9,241.33)
$600.00 ($9,841.33)
3440.07 ($6,401.26)
2.13 ($6,399.13)
$125.00 $6,399.13
Column A is the payment/debit amount.
Column B is the deposit/credit amount.
Column C is the balance amount.
My question is: By using the @sum button how can I obtain the correct balance amount (preferably with the parenthesis and dollar sign/$ ?
Second part of the question: Inasmuch as Col. A is entered and appears as a positive/+ amount, would it be best to have a minus sign (-) preceding the entered amount, or perhaps parenthesis? If so, what would be the most simplistic way to include the sign on the several hundred entries (which I really would not want to do by having to start all over again?
Needless to say, an early reply to my email address would be most gratefully appreciated. Thank you. DP
In several instances there have been negative results so that the balance appears as ($xxx.xx). The second (payment/debit) entry, when attempting to subtract this from the above balance, results in a positive/added sum. For the purpose of clarity, I have cut and pasted below that which actually appears.
Col. A Col..B Col. C
($9,241.33)
$600.00 ($9,841.33)
3440.07 ($6,401.26)
2.13 ($6,399.13)
$125.00 $6,399.13
Column A is the payment/debit amount.
Column B is the deposit/credit amount.
Column C is the balance amount.
My question is: By using the @sum button how can I obtain the correct balance amount (preferably with the parenthesis and dollar sign/$ ?
Second part of the question: Inasmuch as Col. A is entered and appears as a positive/+ amount, would it be best to have a minus sign (-) preceding the entered amount, or perhaps parenthesis? If so, what would be the most simplistic way to include the sign on the several hundred entries (which I really would not want to do by having to start all over again?
Needless to say, an early reply to my email address would be most gratefully appreciated. Thank you. DP
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
At first glance, I think I like Shum's approach and will try same. I have, however, been corresponding with Sjef and perhaps we can arrive at a solution.
In answer as to how i got + $6,399.13; that occurred by using t he @SUM of Lotus, which was an incorrect reply.
Not trying to waste your time, Shum, please let me know what program you're using as by including the parenthesis (negative) sums, I'd like to know what you're using.
I do hop that Sjef will get a copy of this email as well.
Thanks to all and best regards
In answer as to how i got + $6,399.13; that occurred by using t he @SUM of Lotus, which was an incorrect reply.
Not trying to waste your time, Shum, please let me know what program you're using as by including the parenthesis (negative) sums, I'd like to know what you're using.
I do hop that Sjef will get a copy of this email as well.
Thanks to all and best regards
Hi David,
I am using Ms Excel, but this formula should work in Lotus as you were using Sum function in your initial post.
I am using Ms Excel, but this formula should work in Lotus as you were using Sum function in your initial post.
The parentheses come from the column or cell format. I think it's similar in 123: click the column header to select the whole column and then format those cells.
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split: Shums, BillDL and Sjef Bosman
If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
I have recommended this question be closed as follows:
Split: Shums, BillDL and Sjef Bosman
If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
Thank you Shums
Why @Sum ? I assume your column C should contain a formula, for instance C2's formula should be
Open in new window
if your debit numbers are always negative. You can copy the formula to the cells below, the formula will change automatically.If you want a full column to be displayed differently, adapt the format for the whole column. Click the column header to select the entire column, and a right-click might reveal the necessary formatting options.