|
Inputting a length of time?
Hi
I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
Let's say your initial value is in A1 and the length of time is in A2. I'm
also assuming that after the length listed, the value is equal to zero. Let's also assume that the # of months since the initial value is A3. =if(A3=A2,(A1*A3)/A2,0) I think that's what you want. HTH, Barb Reinhardt "Link" wrote: Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
Hi
Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
Hi Roger
I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
Hi
I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
assuming you have 31/12/07 in E1
That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
I think I am finally getting somewhere now Rog.
Thanks for your perseverance. I just have two more questions (please): It seems to go for one month too many. In the following example, the payments go for five months instead of four, and include one extra payment: Asset Name Purchase Date Asset Value Number of Months to depreciate Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500 What happens if I have another asset, say in month three? How can I include that asset in the depreciation payment, and still have them both exist for their respective depreciation periods? Thanks again and I look forward to your reply. Cheers, Link. "Roger Govier" wrote in message ... assuming you have 31/12/07 in E1 That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
In regards to my last question, I was hoping to have all items depreciating
in one row, as opposed to adding up multiple rows. Cheers, Link. "Link" wrote in message ... I think I am finally getting somewhere now Rog. Thanks for your perseverance. I just have two more questions (please): It seems to go for one month too many. In the following example, the payments go for five months instead of four, and include one extra payment: Asset Name Purchase Date Asset Value Number of Months to depreciate Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500 What happens if I have another asset, say in month three? How can I include that asset in the depreciation payment, and still have them both exist for their respective depreciation periods? Thanks again and I look forward to your reply. Cheers, Link. "Roger Govier" wrote in message ... assuming you have 31/12/07 in E1 That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
Hi
I was assuming the date you would be entering for asset purchase, would either be beginning of month 01/06/07 or mid month 15/06/07. If you use those dates, then it does depreciate over the correct number of months. As far as additional assets are concerned, they would be entered on succeeding rows. The total depreciation for each month would be the sum of all of the assets individual depreciation amounts in that column. -- Regards Roger Govier "Link" wrote in message ... In regards to my last question, I was hoping to have all items depreciating in one row, as opposed to adding up multiple rows. Cheers, Link. "Link" wrote in message ... I think I am finally getting somewhere now Rog. Thanks for your perseverance. I just have two more questions (please): It seems to go for one month too many. In the following example, the payments go for five months instead of four, and include one extra payment: Asset Name Purchase Date Asset Value Number of Months to depreciate Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500 What happens if I have another asset, say in month three? How can I include that asset in the depreciation payment, and still have them both exist for their respective depreciation periods? Thanks again and I look forward to your reply. Cheers, Link. "Roger Govier" wrote in message ... assuming you have 31/12/07 in E1 That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
So is there no way to incorporate all assets into one row? Thing is I have
so many rows in this bad boy that it's getting a little out of control. Is there any way to add the formulas or add the previous cell. I know it's a tall order, but it'd really help out. Thanks Roger, Link. "Roger Govier" wrote in message ... Hi I was assuming the date you would be entering for asset purchase, would either be beginning of month 01/06/07 or mid month 15/06/07. If you use those dates, then it does depreciate over the correct number of months. As far as additional assets are concerned, they would be entered on succeeding rows. The total depreciation for each month would be the sum of all of the assets individual depreciation amounts in that column. -- Regards Roger Govier "Link" wrote in message ... In regards to my last question, I was hoping to have all items depreciating in one row, as opposed to adding up multiple rows. Cheers, Link. "Link" wrote in message ... I think I am finally getting somewhere now Rog. Thanks for your perseverance. I just have two more questions (please): It seems to go for one month too many. In the following example, the payments go for five months instead of four, and include one extra payment: Asset Name Purchase Date Asset Value Number of Months to depreciate Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500 What happens if I have another asset, say in month three? How can I include that asset in the depreciation payment, and still have them both exist for their respective depreciation periods? Thanks again and I look forward to your reply. Cheers, Link. "Roger Govier" wrote in message ... assuming you have 31/12/07 in E1 That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
Hi
I can't see any way of incorporating into a single row. For each asset you need the Purchase date and the number of months over which to depreciate. There is no way that could be achieved in a single row with multiple assets. What is the problem with having multiple rows? This would be typical of most depreciation schedules, which typically would reside on a separate sheet. The total for each month from this schedule, would then be picked up as a single row of values in any financial projection. -- Regards Roger Govier "Link" wrote in message ... So is there no way to incorporate all assets into one row? Thing is I have so many rows in this bad boy that it's getting a little out of control. Is there any way to add the formulas or add the previous cell. I know it's a tall order, but it'd really help out. Thanks Roger, Link. "Roger Govier" wrote in message ... Hi I was assuming the date you would be entering for asset purchase, would either be beginning of month 01/06/07 or mid month 15/06/07. If you use those dates, then it does depreciate over the correct number of months. As far as additional assets are concerned, they would be entered on succeeding rows. The total depreciation for each month would be the sum of all of the assets individual depreciation amounts in that column. -- Regards Roger Govier "Link" wrote in message ... In regards to my last question, I was hoping to have all items depreciating in one row, as opposed to adding up multiple rows. Cheers, Link. "Link" wrote in message ... I think I am finally getting somewhere now Rog. Thanks for your perseverance. I just have two more questions (please): It seems to go for one month too many. In the following example, the payments go for five months instead of four, and include one extra payment: Asset Name Purchase Date Asset Value Number of Months to depreciate Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500 What happens if I have another asset, say in month three? How can I include that asset in the depreciation payment, and still have them both exist for their respective depreciation periods? Thanks again and I look forward to your reply. Cheers, Link. "Roger Govier" wrote in message ... assuming you have 31/12/07 in E1 That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
Thanks Roger - I will do the proper thing and make the schedule!;-)
Thanks very much for all your help - it is much appreciated. Cheers, Link. "Roger Govier" wrote in message ... Hi I can't see any way of incorporating into a single row. For each asset you need the Purchase date and the number of months over which to depreciate. There is no way that could be achieved in a single row with multiple assets. What is the problem with having multiple rows? This would be typical of most depreciation schedules, which typically would reside on a separate sheet. The total for each month from this schedule, would then be picked up as a single row of values in any financial projection. -- Regards Roger Govier "Link" wrote in message ... So is there no way to incorporate all assets into one row? Thing is I have so many rows in this bad boy that it's getting a little out of control. Is there any way to add the formulas or add the previous cell. I know it's a tall order, but it'd really help out. Thanks Roger, Link. "Roger Govier" wrote in message ... Hi I was assuming the date you would be entering for asset purchase, would either be beginning of month 01/06/07 or mid month 15/06/07. If you use those dates, then it does depreciate over the correct number of months. As far as additional assets are concerned, they would be entered on succeeding rows. The total depreciation for each month would be the sum of all of the assets individual depreciation amounts in that column. -- Regards Roger Govier "Link" wrote in message ... In regards to my last question, I was hoping to have all items depreciating in one row, as opposed to adding up multiple rows. Cheers, Link. "Link" wrote in message ... I think I am finally getting somewhere now Rog. Thanks for your perseverance. I just have two more questions (please): It seems to go for one month too many. In the following example, the payments go for five months instead of four, and include one extra payment: Asset Name Purchase Date Asset Value Number of Months to depreciate Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500 What happens if I have another asset, say in month three? How can I include that asset in the depreciation payment, and still have them both exist for their respective depreciation periods? Thanks again and I look forward to your reply. Cheers, Link. "Roger Govier" wrote in message ... assuming you have 31/12/07 in E1 That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
Inputting a length of time?
You're very welcome. Thanks for the feedback
-- Regards Roger Govier "Link" wrote in message ... Thanks Roger - I will do the proper thing and make the schedule!;-) Thanks very much for all your help - it is much appreciated. Cheers, Link. "Roger Govier" wrote in message ... Hi I can't see any way of incorporating into a single row. For each asset you need the Purchase date and the number of months over which to depreciate. There is no way that could be achieved in a single row with multiple assets. What is the problem with having multiple rows? This would be typical of most depreciation schedules, which typically would reside on a separate sheet. The total for each month from this schedule, would then be picked up as a single row of values in any financial projection. -- Regards Roger Govier "Link" wrote in message ... So is there no way to incorporate all assets into one row? Thing is I have so many rows in this bad boy that it's getting a little out of control. Is there any way to add the formulas or add the previous cell. I know it's a tall order, but it'd really help out. Thanks Roger, Link. "Roger Govier" wrote in message ... Hi I was assuming the date you would be entering for asset purchase, would either be beginning of month 01/06/07 or mid month 15/06/07. If you use those dates, then it does depreciate over the correct number of months. As far as additional assets are concerned, they would be entered on succeeding rows. The total depreciation for each month would be the sum of all of the assets individual depreciation amounts in that column. -- Regards Roger Govier "Link" wrote in message ... In regards to my last question, I was hoping to have all items depreciating in one row, as opposed to adding up multiple rows. Cheers, Link. "Link" wrote in message ... I think I am finally getting somewhere now Rog. Thanks for your perseverance. I just have two more questions (please): It seems to go for one month too many. In the following example, the payments go for five months instead of four, and include one extra payment: Asset Name Purchase Date Asset Value Number of Months to depreciate Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500 What happens if I have another asset, say in month three? How can I include that asset in the depreciation payment, and still have them both exist for their respective depreciation periods? Thanks again and I look forward to your reply. Cheers, Link. "Roger Govier" wrote in message ... assuming you have 31/12/07 in E1 That should have read assuming you have 31/01/07 in E1 (my dates are UK format) Also, I didn't comment upon your statement Changing the number of months to depreciate, has no affect on the date It doesn't, and is not supposed to. It just works out when to end depreciation, and the amount of depreciation to charge per month. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi I should have said hold down the right mouse button as you drag, then on release of button choose Fill months. Secondly, it looks as though you have cell E2 formatted as Date, it should be Number, with as many places as your require. What you should then see, assuming you have 31/12/07 in E1, is 0,0,0,0,0,166.7,166.7,166.7 etc. in cells E2:L2 If you are still having difficulty, let me have your email address and I will send you a sheet already set up. -- Regards Roger Govier "Link" wrote in message ... Hi Roger I tried what you said. Firstly I couldn't find the option to choose Fill Months upon releasing my mouse button after dragging. Then I got these results: Asset Name Purchase Date Asset Value Number of Months to depreciate End of Starting Month StudyStation Jun-07 $10,000 60 Jan-00 Changing the number of months to depreciate, has no affect on the date. I'm just not sure what is supposed to be going on there. I need to find the ending date of the depreciation, but I also need a column to show the monthly amount and it should return to zero after the depreciation time. Would you be kind enough to expand further, please. I really appreciate it. Cheers, Link. "Roger Govier" wrote in message ... Hi Assuming you set out your schedule as follows A Asset Name B Purchase Date C Asset Value D Number of Months to Depreciate E End of starting month e.g 31 Jan 2006 Format cell E, Format CellsNumberCustom mmm yy so it displays as Jan 06 Drag cell E1 across the screen, holding down the fill handle, on releasing the mouse button, choose Fill Months Now in cell E2 enter =IF($A2="","",IF(AND(E$1=$B2,E$1<=DATE(YEAR($B2), MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0)) and copy across, and down If you have the Analysis Toolpak loaded, ToolsAddinscheck Analysis Toolpak, then you could use the shorter formula =IF($A2="","",IF(AND(E$1=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0)) -- Regards Roger Govier "Link" wrote in message ... Hi I am trying to calculate depreciation in my Excel spreadsheet. The depreciation is over 60 months, so for a $6,000 item, the formula is =$6,000/60 = $100 The problem I am encountering is making that calculation stop after 60 months. So in month 61, the cell reads $0, instead of $100. Is there anyway in Excel to 'time limit' the effectiveness of a cell's formula? To make it even more complicated, I will have ongoing items for depreciation, so I will need to add new items to the formula, while simultaneously letting existing items expire, as above. It's pretty hard to explain, so I imagine it will be pretty hard to solve, but I would certainly appreciate any advice. Cheers, Link. |
| All times are GMT +1. The time now is 07:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com