General ledger spreadsheet continued

[Mark C]

My specific problem is when my spreadsheet contains calculated values for
example: PAID =F5+I5+L5+O5+R5+U5+X5, where PAID is the title of a column and
all of the individual cell references represent PAID values; and RECEIVED
=G5+J5+M5+P5+S5+V5+Y5 is the title of a column and all of the individual cell
references represent RECEIVED values. My next column is BALANCE and the
equation for BALANCE looks like this, =IF(AND(AA5="",AB5=""),"",AC4-AA5+AB5)
where cell "AA5" contains the computed value of all monies received PAID and
cell "AB5" contains the computed value of all monies RECEIVED and cell "AC4"
represent the previous BALANCE. When I copy the formulas down in the
spreadsheet I get repeating values in the cells of the same column called
balance. Example: AC4=70, AA5=50, AB5=20. The new BALANCE; AC5=40 is also
occurring in AC6, AC7, AC8... Is there a solution to this problem?

 

[Aqib Rizvi]

Mark

If you have values in AA and AB, you should base your formula on
values not on text.
Try replacing "" with Zero,

The formula in Column AC should be =IF(AND(AA5=0, AB5=0),0,AC4-
AA5+AB5)
I beleive this will fix the problem.

AQIB RIZVI

 

[JoeU2004]

My next column is BALANCE and the
equation for BALANCE looks like this,
=IF(AND(AA5="",AB5=""),"",AC4-AA5+AB5)
where cell "AA5" contains the computed value of all monies received PAID
and
cell "AB5" contains the computed value of all monies RECEIVED and cell
"AC4"
represent the previous BALANCE. When I copy the formulas down in the
spreadsheet I get repeating values in the cells of the same column called
balance.

Your formula looks right to me off-hand. That is, when you copy down, you
should see =IF(AND(AA6="",AB6=""),"",AC5-AA6+AB6) etc. Right?

I suspect you have manual calculation set. In Excel 2003, click on Tools >
Options > Calculation and set Automatic.

Another alternative is that AA6 and AB6 etc are zero, not "". That would
certainly explain the propagation of the balance. See my PPS below.

PS: For future postings, keep related questions and follow-ups in the same
thread so that others can see the context.

PPS: I presume that the formulas in the PAID and RECEIVED columns are not
really simply =F5+I5+L5+O5+R5+U5+X5 and =G5+J5+M5+P5+S5+V5+Y5. If they
were, then AA5 and AB5 would never be "". On the other hand, if those truly
are the formulas, then again AA5 and AB5 would never be "". That could be
the root cause of your problem. In the future, post the actual formulas,
not just the titles of columns. It is best to copy-and-paste from the fx
field.


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