A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

General ledger spreadsheet continued



 
 
Thread Tools Display Modes
  #1  
Old June 20th, 2009, 12:04 AM posted to microsoft.public.excel.worksheet.functions
Mark C[_3_]
external usenet poster
 
Posts: 9
Default General ledger spreadsheet continued

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?
--
Mark Christjansen
  #2  
Old June 20th, 2009, 03:32 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default General ledger spreadsheet continued

"Mark C" wrote:
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 -----

"Mark C" wrote in message
...
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?
--
Mark Christjansen


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.