Straight Line Depreciation
Feb 15, 2006
I have a list of items on which I need to calculate the SLD, normally I would just divide the Acq Cost by the Salvage period (5yrs) to calculate the yearly depreciation. However I have been asked to calculate it from the Cap Date, therfore the first year would only be a portion of the year and the final year would also be a potion of the year.
So I'm trying to calculate the depreciation period for an asset with an Acq Cost of £12500 and a Cap Date of 27th July 04 over a 5yr period from that date.
View 9 Replies
ADVERTISEMENT
Sep 24, 2008
if I wanted to slowly move a shape (small circle) in a straight line between cells R20 & W7.
The straightest line possible is by drawing an arrow line between the 2 cells.
Is there a way to then use this arrow as a guide for moving the shape?
In other words, would it make sense for the porgram to draw a transparent line between the cells, and use that line as a "path" along which to move the shape?
I just don't know if this is worth the trouble.
View 9 Replies
View Related
May 8, 2008
Drawing Straight Lines In A Chart And Mark Intersections. I want to make a graphic like this in Excel.
View 7 Replies
View Related
Jan 31, 2012
Is it possible to install a dropdown menu straight onto the Quick Access Toolbar in 2010?
Tried
Code:
Application.CommandBars("Quick Access toolbar")
To reference it, but no joy.
View 3 Replies
View Related
Sep 15, 2007
My problem is the following:
I'm trying to model straight line depreciation over 5 years for certain investments. This could for example be done like this: ....
View 9 Replies
View Related
Mar 20, 2009
I'm trying to calculate the Depreciation of the fixed asset for some items. I've tried the formulas that came with Excel but i don't know its not working or not give the correct value
so I attached a file as an example what I'm trying to have is
1- straight line method along the asset life
2- salvage must be ( 1 )
3- if the purchase date is equal to or before the middle of the month (14-15/02/2004) so the start of depreciation have to start from the beginning of the current month ( Feb) but if the date excess the day 15 ( 16/02/2004) the middle of the month , the the depreciation must start from the next month.
View 8 Replies
View Related
Jun 11, 2014
I am in need of a formula to calculate monthly straight-line depreciation assets based on the current month’s days.
So, if an asset costs $10,000 and has 15 years of useful life, in June this would calculate 15 divided by 12 divided by 31 times 10,000. I want to put cost in one cell and date in another, for the formula.
BTW, someone is using this formula, =(16736*(1/15)*(6/12))*31/184
View 9 Replies
View Related
Feb 2, 2008
Using the declining balance depreciation formula how can I have the individual time frame results put into to cells?
View 9 Replies
View Related
Mar 18, 2013
how to calculate debt repayments using this approach.
As shown on the attached file repay issue.xlsx I have an example where a loan is taken out regularly (in the example every 6 years) but repaid over a shorter period (in this example every 3 years).
Using "IF(MOD(ROW" the formula identifies correctly when the loan has to be renewed, and calculates correctly how much needs to be borrowed.
C14 =IF(MOD((ROW(A14)-ROW($A$8)),B$3)=0,B9*C$5
Because Row 14 is six rows below the start (Row 8) which is the interval set by B3, it enters the loan percentage
(C5) of the asset value (B9) into C14.
Problem comes in the following rows. I need the formula to establish how many rows the "active" row is below the last "renewal", then deduct one fraction of the payback period for each row. By sheer coincidence the formula below works on the second row of the datsabase and deducts one third of the loan value because C8 WAS the previous "renewal", and row 9 is one below it:
=IF(MOD((ROW(A9)-ROW($A$8)),B$3)=0,B9*C$5,IF(MOD((ROW(A9)-ROW($A$8)),B$3)=1,C8-(C8*(COUNT(A9-A$8)/C6))))
I cannot "hard wire" the cell references into the formula because the renewal intervals and number of paybacks range from 25 year mortgages paid every year without being renewed to a one year loan on an item renewed every two years
View 2 Replies
View Related
Apr 4, 2014
I am looking to reassess the useful life of existing fixed assets by one/two years.
For example:-
Original cost $1,727.00
Start Date 30/12/2008
Current End of life 29/12/2013
New End of Life date 29/12/2014
5 year - 20%
NBV as at 30/06/2013 $174.12
How can I calculate the new rate, as I can not change the prior financial years depreciation. I am looking for a calculation, to test the assumptions, and see the overall change. This is just one example.
View 1 Replies
View Related
Feb 10, 2013
I am attempting to create a capital depreciation schedule that gives us our total operating impact for a given calendar year. I can populate my depreciation based on the purchase month, and fill across the schedule the monthly depreciation cost, however, I'm not sure how to stop the depreciation after the item has depreciated fully (say after 36 months).
For example, if someone were to select March 2013 in B5, monthly depreciation should begin in E5 and spread across ONLY 36 months. If they were to select April 2013, the depreciation would begin in F5 and extend ONLY 36 months.
I think it might be possible to do this with an COUNTIF function, but it is not working for me.
View 3 Replies
View Related
Jan 25, 2010
I am working on a depreciation schedule in which I want the monthly depreciation of an asset to automatically calculate and, if the asset is fully depreciation, caclulate a zero or the balance to be depreciation (if less than the monthly depreciation). Please see below example. As you can see my asset is fully depreciated at the end of February but because there remains a $0.01, the formula is calculating another month in March and then reversing it in April (less the $0.01). Here's the formula I'm using. What am I doing wrong?
Column H is March, Column C is my monthly depreciation, and column E is my beginning book value:
=-IF(ABS(SUM($F2:H2))>=$E2,(SUM($F2:H2)+$E2),IF($E2=$C2,$C2,$E2)))
Purchase
PriceMonthly DepreciationAccumulated Depreciation 12/31/20091/1/2010 Beginning Book ValueJan-10Feb-10Mar-10Apr-10May-10
LCD PROJECTOR 797.12 13.29 (770.54) 26.58 (13.29) (13.29) (13.29) 13.28 -
View 9 Replies
View Related
Feb 23, 2013
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies
View Related
Nov 17, 2012
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
View 5 Replies
View Related
Mar 30, 2013
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
View 1 Replies
View Related
Aug 5, 2014
I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
View 7 Replies
View Related
Aug 20, 2014
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
View 1 Replies
View Related
May 19, 2014
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
View 9 Replies
View Related
Feb 6, 2013
I need the Service Order tab to populate with data from the CPR tab based on the IO#. Since there is more than 1 occurrence of an IO# I need a formula to list the 1st, 2nd, 3rd, etc result of the vlookup in the cell under the 1st.
=VLOOKUP($E$2,CPR!$L$4:$AA$12,2,FALSE),
When I drag this down I get the 1st occurrence of the IO# 777 I need all of them.
B C D
Atex ID#(unique id) Campaign name Site
Row 7 1st occurrence of IO# 777
Row 8 2nd occurrence of IO# 777
Row 9 3rd occurrence of IO# 777
View 2 Replies
View Related
Apr 30, 2013
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
View 2 Replies
View Related
Jun 21, 2008
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
View 9 Replies
View Related
Dec 8, 2007
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
View 9 Replies
View Related
Nov 29, 2013
I have a spread sheet with product codes on and 5 different prices types but they have only pulled through 4 times so need to insert a 5th line for each product code
View 2 Replies
View Related
Nov 6, 2009
With the attached spreadsheet i cannot workout how with the line graph not to continue the line if there is nothing in row 10 of the table.
for eaxmple if there is no value in cell M10, i would like the actual addtion line to stop at the last value on the graph that was in L10.
View 4 Replies
View Related
Mar 30, 2014
I have a multiple line texts I want to convert it in single line like for Example:
The following contents is to be converted in a single line
contents:
[aaaaaaaa] [bbbbbbb]......so on
[cccccccc] [ddddddd]......so on
[eeeeeeee] [fffffffffff]......so on
Result:
[aaaaaaaa] [bbbbbbb] [cccccccc] [ddddddd] [eeeeeeee] [fffffffffff]....so on
View 4 Replies
View Related
Mar 14, 2014
I have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.
Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.
What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:
1. Looks up last row (row 30)
2. Copies last row (row 30)
3. Pastes to next empty row (row 31)
4. Pastes values only to second last row (row 30)
I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.
View 5 Replies
View Related
May 21, 2010
I have a chart sheet with a line graph that contains two series: I want to change their thickness, but I am not able to format it with VBA.
View 6 Replies
View Related
Aug 22, 2012
Creating a macro that will identify the last blank line in column J and insert a line in it? The macro will need to look from the "bottom" as there is blanks cells above the last line.
View 5 Replies
View Related
May 20, 2008
I'm working with matrix in vba and i'm trying to put all values of a specific line in that matrix in a line in excel. For example:
Matrix =
1 2
3 4
and i want Range("B1:B2") = 3 | 4 (the second line of that matrix).
Is there any way of doing this without perfoming a loop?
[something like Range("B1:B2").value = 'range'(matrix(0,0) , matrix(0,1)) ]
View 9 Replies
View Related
Mar 14, 2013
The following code adds a new row at a given point (Based on a fomula in a column returning "1")
The code works well BUT I want to merge this new row from column A to coulmn M
Im not sure how to do this, (I dont want to merge the entire row)
Example Code:
Sub ExtLne()
Dim Lastrow As Long
Dim I As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
I = 1
While I
View 2 Replies
View Related
