Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding a number to itself
I am making a budget spreadsheet in which I keep each month's expenses
in one column and then the annual expenses in another column. For example, the January (month 1) expenses for line item 1001 are $100 and therefore the annual expenses for line item 1001 are $100. In February the 1001 expenses are $200 and therefore the annual 1001 expenses are last month's 1001 expenses ($100) plus this month's 1001 expenses ($200) which is $300. Without keeping a separate column for each month's line item expenses, is there a tricky way of adding a number to itself? That is, when I enter $200 into the February spreadsheet how can I add the annual expenses to it without keeping a separate tally sheet somewhere else? Jim |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding a number to itself
Can be done.....see Don's response.
But what you are asking for is fraught with peril. No way to trouble shoot any errors in data input. Once the new value is added to old, the old data is gone. Say you had 100 in a cell, then you want to add 200. Don's code will allow that but what if you inadvertantly added 209? How will you keep track of current and past entries? You have no 'paper trail" to follow to see how you got the results you have. You'll be scratching your head wondering where and when the data input was not done correctly. Excel sheet has many cells. Why not use them? Gord Dibben MS Excel MVP On Tue, 22 Dec 2009 12:17:43 -0800, RST Engineering wrote: I am making a budget spreadsheet in which I keep each month's expenses in one column and then the annual expenses in another column. For example, the January (month 1) expenses for line item 1001 are $100 and therefore the annual expenses for line item 1001 are $100. In February the 1001 expenses are $200 and therefore the annual 1001 expenses are last month's 1001 expenses ($100) plus this month's 1001 expenses ($200) which is $300. Without keeping a separate column for each month's line item expenses, is there a tricky way of adding a number to itself? That is, when I enter $200 into the February spreadsheet how can I add the annual expenses to it without keeping a separate tally sheet somewhere else? Jim |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding a number to itself
I came to that conclusion myself, the "paper" trail (actually a cell
trail) is a Good Thing® so I made a matrix sheet of account numbers and months, then linked the main sheet to each cell in the matrix sheet so that I have a running tally on the main sheet of that months expenses plus a summed column of the year to date expenses. To change months you do a simple Find&Replace replacing last month's column letter with this month's column letter. Pretty simple once I got my head wrapped around it. Thanks for all your help... Jim On Tue, 22 Dec 2009 14:11:46 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Can be done.....see Don's response. But what you are asking for is fraught with peril. No way to trouble shoot any errors in data input. Once the new value is added to old, the old data is gone. Say you had 100 in a cell, then you want to add 200. Don's code will allow that but what if you inadvertantly added 209? How will you keep track of current and past entries? You have no 'paper trail" to follow to see how you got the results you have. You'll be scratching your head wondering where and when the data input was not done correctly. Excel sheet has many cells. Why not use them? Gord Dibben MS Excel MVP On Tue, 22 Dec 2009 12:17:43 -0800, RST Engineering wrote: I am making a budget spreadsheet in which I keep each month's expenses in one column and then the annual expenses in another column. For example, the January (month 1) expenses for line item 1001 are $100 and therefore the annual expenses for line item 1001 are $100. In February the 1001 expenses are $200 and therefore the annual 1001 expenses are last month's 1001 expenses ($100) plus this month's 1001 expenses ($200) which is $300. Without keeping a separate column for each month's line item expenses, is there a tricky way of adding a number to itself? That is, when I enter $200 into the February spreadsheet how can I add the annual expenses to it without keeping a separate tally sheet somewhere else? Jim |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding a number to itself
Good thinnin'
Gord On Tue, 22 Dec 2009 16:34:41 -0800, RST Engineering wrote: I came to that conclusion myself, the "paper" trail (actually a cell trail) is a Good Thing® so I made a matrix sheet of account numbers and months, then linked the main sheet to each cell in the matrix sheet so that I have a running tally on the main sheet of that months expenses plus a summed column of the year to date expenses. To change months you do a simple Find&Replace replacing last month's column letter with this month's column letter. Pretty simple once I got my head wrapped around it. Thanks for all your help... Jim On Tue, 22 Dec 2009 14:11:46 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Can be done.....see Don's response. But what you are asking for is fraught with peril. No way to trouble shoot any errors in data input. Once the new value is added to old, the old data is gone. Say you had 100 in a cell, then you want to add 200. Don's code will allow that but what if you inadvertantly added 209? How will you keep track of current and past entries? You have no 'paper trail" to follow to see how you got the results you have. You'll be scratching your head wondering where and when the data input was not done correctly. Excel sheet has many cells. Why not use them? Gord Dibben MS Excel MVP On Tue, 22 Dec 2009 12:17:43 -0800, RST Engineering wrote: I am making a budget spreadsheet in which I keep each month's expenses in one column and then the annual expenses in another column. For example, the January (month 1) expenses for line item 1001 are $100 and therefore the annual expenses for line item 1001 are $100. In February the 1001 expenses are $200 and therefore the annual 1001 expenses are last month's 1001 expenses ($100) plus this month's 1001 expenses ($200) which is $300. Without keeping a separate column for each month's line item expenses, is there a tricky way of adding a number to itself? That is, when I enter $200 into the February spreadsheet how can I add the annual expenses to it without keeping a separate tally sheet somewhere else? Jim |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding a number to itself
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding a number to itself
I find myself doing that Don.
Sometimes we give OP's only what they ask for. The old adage..........be careful what you ask for, you just might get it<g Merry Xmas Gord On Wed, 23 Dec 2009 17:02:49 -0600, "Don Guillett" wrote: Agreed. I was just trying to accommodate OP |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding a number to itself
Let's say that the your Y-T-D is column D1 and your monthly column is called C1, then what you want to do is in column D1 enter the formula: =SUM(C1,D1). You are going to get an error message (called a circular reference)close it. Now go to the excel menu (it's the little circle on the left hand corner with the microsoft symbol) and click on excel options, then go to formulas.
There you will enable iterative calculation and enter 1 in the maximum iterations and in the maximum change also. i hope this helps... RST Engineering wrote: Adding a number to itself 22-Dec-09 I am making a budget spreadsheet in which I keep each month's expenses in one column and then the annual expenses in another column. For example, the January (month 1) expenses for line item 1001 are $100 and therefore the annual expenses for line item 1001 are $100. In February the 1001 expenses are $200 and therefore the annual 1001 expenses are last month's 1001 expenses ($100) plus this month's 1001 expenses ($200) which is $300. Without keeping a separate column for each month's line item expenses, is there a tricky way of adding a number to itself? That is, when I enter $200 into the February spreadsheet how can I add the annual expenses to it without keeping a separate tally sheet somewhere else? Jim Previous Posts In This Thread: On Tuesday, December 22, 2009 3:17 PM RST Engineering wrote: Adding a number to itself I am making a budget spreadsheet in which I keep each month's expenses in one column and then the annual expenses in another column. For example, the January (month 1) expenses for line item 1001 are $100 and therefore the annual expenses for line item 1001 are $100. In February the 1001 expenses are $200 and therefore the annual 1001 expenses are last month's 1001 expenses ($100) plus this month's 1001 expenses ($200) which is $300. Without keeping a separate column for each month's line item expenses, is there a tricky way of adding a number to itself? That is, when I enter $200 into the February spreadsheet how can I add the annual expenses to it without keeping a separate tally sheet somewhere else? Jim On Tuesday, December 22, 2009 4:44 PM Don Guillett wrote: Option ExplicitDim oldvalue As DoubleRight click sheet tabview codeinsert Option Explicit Dim oldvalue As Double Right click sheet tabview codeinsert this. Now, when you enter a value in a5 it will be added to what is there '==== Option Explicit Dim oldvalue As Double Private Sub Worksheet_Change(ByVal target As Excel.Range) If target.Address = "$A$5" Then On Error GoTo fixit Application.EnableEvents = False If target.Value = 0 Then oldvalue = 0 target.Value = 1 * target.Value + oldvalue oldvalue = target.Value fixit: Application.EnableEvents = True End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software On Tuesday, December 22, 2009 5:11 PM Gord Dibben wrote: Can be done.....see Don's response. Can be done.....see Don's response. But what you are asking for is fraught with peril. No way to trouble shoot any errors in data input. Once the new value is added to old, the old data is gone. Say you had 100 in a cell, then you want to add 200. Don's code will allow that but what if you inadvertantly added 209? How will you keep track of current and past entries? You have no 'paper trail" to follow to see how you got the results you have. You'll be scratching your head wondering where and when the data input was not done correctly. Excel sheet has many cells. Why not use them? Gord Dibben MS Excel MVP wrote: On Tuesday, December 22, 2009 7:34 PM RST Engineering wrote: I came to that conclusion myself, the "paper" trail (actually a celltrail) is I came to that conclusion myself, the "paper" trail (actually a cell trail) is a Good Thing? so I made a matrix sheet of account numbers and months, then linked the main sheet to each cell in the matrix sheet so that I have a running tally on the main sheet of that months expenses plus a summed column of the year to date expenses. To change months you do a simple Find&Replace replacing last month's column letter with this month's column letter. Pretty simple once I got my head wrapped around it. Thanks for all your help... Jim wrote: On Wednesday, December 23, 2009 5:00 PM Gord Dibben wrote: Good thinnin'Gordwrote: Good thinnin' Gord wrote: On Wednesday, December 23, 2009 6:02 PM Don Guillett wrote: Agreed. Agreed. I was just trying to accommodate OP -- Don Guillett Microsoft MVP Excel SalesAid Software "Gord Dibben" <gorddibbATshawDOTca wrote in message On Wednesday, December 23, 2009 8:03 PM Gord Dibben wrote: I find myself doing that Don.Sometimes we give OP's only what they ask for. I find myself doing that Don. Sometimes we give OP's only what they ask for. The old adage..........be careful what you ask for, you just might get it<g Merry Xmas Gord Submitted via EggHeadCafe - Software Developer Portal of Choice How to display a Gravatar Image with 100 Percent Client Script Code http://www.eggheadcafe.com/tutorials...-gravatar.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding text or number? | Excel Discussion (Misc queries) | |||
adding several number | New Users to Excel | |||
ADDING A NUMBER | New Users to Excel | |||
Adding a number to a cell that already has a number. | Excel Discussion (Misc queries) | |||
adding to a number containing text | Excel Discussion (Misc queries) |