Personal Ledger Problem

[Steve]

Here's my problem:

I am keeping track of my Deposits and Withdrawls in Excel, so that I have
up-to-date info on how much I have in my account. Currently I have it set up
so that when a transaction occurs I hard code in date, description,withdrawl
amt, or deposit amt and the the Balance Col contaitns the following formula:
=IF(D32&E32="","",F31-D32+E32).

The next objective I want to concur is that right now I have a check that
has not been cashed by the recipient. My only balance is off by the amount
of that check. As a check to make sure I have not missed any transactions I
want to be able to input my online balance into a cell and then have the cell
next to it automatically take the last balance number on my spreadsheet and
tell me how much my balance off by.

I was trying to do something with IF formulas using that if the balance cell
below it is blank then use the cell above it, but I couldn't get it to work.

Please help.

Thanks,

Steve

 

[Eduardo]

Hi,
I don't understand your formula, if D32&E32="","", what about the previous
balance?,
could you post an example of your sheet, maybe the solution is this, I
suppose that your initial balance started in F1, then in G1 you enter the
balance from the web, in H1 enter
=g1-F1-sum($D$1:$D$1000)+sum($E$1:$E$1000)

 

[JoeU2004]

I want to be able to input my online balance into a cell and then
have the cell next to it automatically take the last balance number
on my spreadsheet and tell me how much my balance off by.

If the current online balance is in A1, then:

=INDEX($F:$F, MATCH(9E307,$F:$F)) - $A$1

This assumes that the entire column F is used only for tracking current
running balance. (It may also contain some text, e.g. titles and null
strings.) Alternatively, replace $F:$F with the specific range that might
the current balance or null strings (""), e.g. $F$2:$F$100.

The MATCH() expression returns the offset within the range $F:$F of the last
cell with a numeric value less than 9E307, just about the largest possible
value; ergo, the cell offset of the last numeric value. The INDEX()
expression returns the value of the cell identified by MATCH().

the Balance Col contaitns the following formula:
=IF(D32&E32="","",F31-D32+E32).

Unless you have the "Precision as displayed" calculation option set, it is
prudent to round most financial calculations to the smallest coin of the
realm, e.g. cents for the US. So I would suggest:

=IF(D32&E32="", "", ROUND(F31-D32+E32, 2))

=ROUND(INDEX($F:$F, MATCH(9E307,$F:$F)) - $A$1, 2)

The need to round such calculations arises from the internal representation
and arithmetic that Excel (and most applications) uses on binary computers
(viz. floating point arithmetic). Consequently, most decimal fractions
cannot be represented exactly, and small numerical aberrations arise as a
consequence. For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!),
whereas IF(ROUND(10.1 - 10, 2) = 0.1,TRUE) returns TRUE.


----- original message -----