General ledger spreadsheet

[Mark C]

I am in the process of developing a general ledger spreadsheet for my church.
The spreadsheet shows a list of the different funds in the church and I am
using a formula: =IF(AND(ISBLANK(F5),ISBLANK(G5)),"",H4+(-F5)+G5) for the
balance column of each fund. My problem is when I try to fill in the
"Summary of
Accounts" that utilizes a SUM of all paid and SUM of all received monies, I
get a repeating Sum in the spreadsheet when I copy the formula down. I would
be grateful for any help.
Mark Christjansen

 

[JLatham]

I'm not quite sure of what you mean when you say you get a "repeating Sum".
Where is your SUM formula, and what does it look like?

If it looks like =SUM($I$2:$I$4) (just as an example), then it will retain
those same cell references as you copy it down the sheet. If it looked like
=SUM(I2:I4) the row numbers would change as it is copied down the sheet.

 

[Shane Devenshire]

Hi,

I don't understand your question either, why not show us a sample with what
you get and what you expect.

FYI - you can simplify the
=IF(AND(ISBLANK(F5),ISBLANK(G5)),"",H4+(-F5)+G5)
to
=IF(AND(F5="",G5=""),"",H4-F5+G5)

 

[JoeU2004]

=IF(AND(ISBLANK(F5),ISBLANK(G5)),"",H4+(-F5)+G5)

First, some improvements/corrections. I would write:

=IF(AND(F5="",G5=""),"",H4-F5+G5)

The first change, replacing ISBLANK, is probably a correction. Note that
ISBLANK is true only if there is no formula and no constant in a cell,
whereas F5="" is also true when the formula evaluates to "", as your formula
does. Thus, "" can be propagated throughout your spreadsheet.

The second change is an arithmetic simplification. Although the improved
readability is minor here, it could be significant in more complicated
formulas. Unneeded parentheses, especially when they are nested, often are
the root cause of formula errors.

I get a repeating Sum in the spreadsheet when I copy the formula down.

Your description is unclear. Post the SUM formula and explain what you
don't like about it.


Final note: It would behoove you to use ROUND prolifically in all formulas
that involve or might result in decimal fractions (i.e. dollars and cents).
This is especially important in general ledgers, where the sum of debits and
credits are expected to be exactly the same.

For example, you should compute ROUND(H4-F5+G5,2) and ROUND(SUM(I1:I100),2).
The ROUND(SUM) form should be okay as long as each cell of I1:I100 is
rounded.

The reason for this is complicated. In a nutshell, most numbers with
decimal fractions are not represented internally exactly as they appear in
Excel, which formats only the first 15 significant digits. Sometimes you
can even see the difference within the first 15 significant digits. For
example, IF(100.10-100=0.10,TRUE) results in FALSE(!) because 100.10-100 is
0.0999999999999943 instead of 0.100000000000000.

Not sure if that is what you might mean by "repeating sum"; i.e, a sum with
repeating decimal digits.

FYI, IF(ROUND(100.10-100,2)=0.10,TRUE) results in TRUE, and
ROUND(100.10-100,2) has exactly the same internal representation as 0.10.


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