Two fold question.

David Peller
David Peller used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sjef BosmanGroupware Consultant

Commented:
It must be more than 10 years ago that I used Lotus 123, so I might be wrong. Maybe some day, you'd better convert your spreadsheet to Excel, even though current Excel version don't accept .wks files (older versions used to, maybe you can find one). What Operating System do you use? Windows/98?

Why @Sum ? I assume your column C should contain a formula, for instance C2's formula should be
=C1-A2+B2

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.
Hello David

Unfortunately the tabbed layout of columns and rows gets messed up when you paste or type directly into the comment field.  Would it be possible to try and paste the content between
[ CODE]contents here[ /CODE]
tags to see of the tabular layout is retained when it is in plain rather than formatted text.  All you do is click the CODE button in the formatting toolbar of the comment box and then paste in between the square brackets as I have shown above.

The alternative would be to create an example lotus *.123 file and attach it here with the extension changed to *.txt.  Application Suites like OpenOffice and LibreOffice can open *.123 spreadsheets.

Being able to see the layout, and better still the formulae you have, would be a big help.
Sjef BosmanGroupware Consultant
Commented:
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

Open in new window


And the last line I don't understand...
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Please try below in C2 and drag down
=SUM(C1,B2-A2)

Open in new window

See below image:
BalanceStill wondering of your last entries, how you got + $6,399.13
David PellerRetired

Author

Commented:
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
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi David,

I am using Ms Excel, but this formula should work in Lotus as you were using Sum function in your initial post.
Sjef BosmanGroupware Consultant

Commented:
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.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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.
Thank you Shums

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial