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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|