Using CountIf Function - Capital Depreciation Schedule
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
ADVERTISEMENT
Jun 27, 2014
I am trying to set up a worksheet that shows shifts using a Capital "D" and a lower case "d" (one is for a 12 hour shift, the other for only 6 hours). Is there any way to set up a Countif formula that makes the distinction?
View 12 Replies
View Related
Aug 17, 2009
I'm trying to apply a VLOOKUP function to my schedule sheet. Attached to this post is a copy of the sheet.
The shifts that I'm entering in B7:AC7 should be looked up in the table located at K21:Q25. I'd like the looked up value to display in B8:AC8.
View 7 Replies
View Related
Jan 3, 2012
I am using COUNTIF functions with Defined Name lists to quickly determine if a certain number is on a list. As a backdrop, I am using Chemical Abstract Service (CAS) numbers and attempting to somewhat streamline chemical approval for a small company. CAS numbers are often in the format of XX-XX-X with varied amounts of numbers. One of the defined lists however does not have dashes.
Thus, I am using cell B1 to enter the CAS# once and then for each list having a column to itself with an associated worksheet with a defined name list. Most of the columns have the function =B1 with the conditional formatting of =COUNTIF(definedname,BX) and formatted to turn red if the chemical is on the list. This is working for all of the columns except for the list that needs the dashes removed. For instance, CAS 64-67-1 is put in B1 and cell B5 has the formula =SUBSTITUTE(B1,"-","") which brings the number to 64671 which matches the number in my defined name list. However, the cell will not turn red. What am I missing?
View 9 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
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
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
May 14, 2014
important columns are: "D", "G", "L".
this is about a project is a granite to be installed in 104 units, these units are divided in 4 types (column "L") K1, K2, K3, K4
I tried IF, SUMIF, COUNTIF, ETC but I think that I need is some combined function that I'm not able to figure out.
as you can see in the attached file, my units 101, 102, etc is not been installed yet (column H is empty), but the units 209, 210, 211, 214 yes was installed on 5/6 for example.
for example, if i use =COUNTIF(H4:H20,">0"), will return me all the H that is not empty (=3), how many kitchen was installed so far, but, I would like to know how many of these returned units (3), is my different types: K1, K2, K3 and K4....
View 5 Replies
View Related
Sep 1, 2008
I am trying to count values in cells of column A only if there is something (any value) in corresponding cells in columns B, C, D, and E. If there are no values in cells of columns B, C, D, and E do not count the cell in column A.
View 10 Replies
View Related
Jan 29, 2007
I'm trying to do a count where column C="Employee" & column E="2008". Below is the formula I have tried and is obviously not working.
=countif(and(C113:C143="Employee",E113:E143="2008")
View 9 Replies
View Related
Feb 15, 2007
I'm trying to use the worksheet function countif in my code and it does not recognize it. But it does recognize counta. Here's a sample of my code.
Private Sub UserForm_Activate()
Dim MyUnion As Range
Set MyUnion = Union(Rows(12), Rows(14), Rows(16), Rows(18), Rows(20), Rows(22), _
Rows(29), Rows(31), Rows(33), Rows(35), Rows(37), Rows(39), _
Rows(46), Rows(48), Rows(50), Rows(52), Rows(54), Rows(56), _
Rows(63), Rows(65), Rows(67), Rows(69), Rows(71), Rows(73))
txtTtlDays.Text = Application.worksheetfunction.countif(MyUnion, "&A")
End Sub
View 4 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
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 26, 2014
I'm trying to create a formula to return the number of cells where the last 8 characters of a cell end with the string "landline"
My (failed) attempt at creating this is as follows:
=COUNTIF RIGHT(A2:A322,8)="Landline"
View 8 Replies
View Related
May 2, 2014
I want to use the countif function for a certain array. The range is set by another cell which is made up out of a percentile of an entire row.
The problem is is that excel doesn't see the value it displays so i continuously get a value of 0 in the countif cell. If i fill in the range by hand, which is exactly the same range as the outcome of the percentile the countif cell does give the correct value.
So in short the problem is i guess that the countif cell does not recognize the value because this value is made up by a formula.
View 8 Replies
View Related
May 19, 2014
Is it possible to use a variable in Countif?
See attached sheet. When a number repeats for the 3rd time, I need to reset the
Countif function.
For example;
Countif($A$5:A20,A20).
I need to change the $A$5 to the current row number so we can start the count again. I need to do this several times during the sheet.
We are counting numbers until they repeat for a 3rd time and then we start the count again at that point.
View 1 Replies
View Related
Mar 9, 2009
I am trying to count number of staff working in any day without deleting the empty cells.(see attached sample sheet). I tried sumproduct as I have two criteria but it is giving me a "zero" result. I only want to count cells in range B3:B32 that does not have "#" sign if there is a staff name if there is a staff name id corresponding cell in Column A within A3:A32.
View 3 Replies
View Related
Mar 12, 2007
I have a table in which we track our daily issues.. A1 - A6
Resource/Subject/Date/Time Of Failure/Escalated/Tech Int
I want to be able to count the amount of escalations we have per month...
We just enter a Y or N for the escalation field.
I'm not great with Excel and can do a count with the COUNTIF but don't know how to separate my results so that the count is done per month..
Can someone point me in the right direction?
*EDIT* Also my date format is as follow's 04-Jan-07. Should I change that to make it easier to calculate the results?
View 9 Replies
View Related
Jun 5, 2008
I am using the following COUNTIF formula which I am sure is supposed to prevent '#N/A' from appearing (According to [url]
View 12 Replies
View Related
Nov 5, 2011
I am trying to return how many cells contain a string of text as entered by the user but I am struggling to put wildcards around my search term. Below is the code I have so far.
Code:
Dim search as String
Dim occurance As Integer
search = InputBox("Search for", "Search Term")
occurance = WorksheetFunction.CountIf(Cells, search)
MsgBox (occurance)
View 2 Replies
View Related
Dec 19, 2012
How do I setup a COUNTIF function so that it 'searches/uses' more than one column for results.
Example: I have a spreadsheet with 20 columns - starting at A1 to T1 - and ending at A100 toT100. I want to use the COUNTIF function to 'search' all of these from A1/T1 to A100/T100 and tell me how many times the number 21 appears across that range of cells.
This COUNTIF function will reference data on a sheet called 50 - so at the moment my current single colum COUNTIF looks like this -=COUNTIF('50'!A1:A100,21) - and it works fine for one colum, but I need to search 20 columns.
View 4 Replies
View Related
Aug 16, 2007
I have two columns on sheet called back1! if a number is put in a cell from P5 down to P40 and a corrosponding number matches in K5 to K40 I want to be able to count it as 1.
So if a number is in k6 and a number is in P6 it counts as 1.
and so on down the cells.
View 9 Replies
View Related
Jul 3, 2008
Look at 6 weeks of data, using "Tue" as the search parameter. I need to find the last 3 tuesdays (from current cell) and then sum the corresponding values in the cell I designate.
I'm fluent with LOOKUP and already have it finding and summing last few Tuesdays and corresponding sales totals, but I need it to only back THREE Tuesdays consistently, not 2 or 4, which it is sometimes doing now??? How do I force it to look back at "tue" and when it has found three times, it knows to stop and not consider any other Tuesdays it may find further back in the data?
View 9 Replies
View Related
Apr 23, 2006
I'm trying to count the number of times a pupil gets a detention which has been rescheduled.
In the main worksheet below I want to be able to count instances like the one on row 11 where John Smith has had a rescheduled detention.
I want to be able to connect this rescheduled detention to John Smith on a different worksheet in the same workbook. On the screenshot below I would like to have Column D as a running total of rescheduled detentions.
I have tried all sorts of permutations of the COUNTIF function but without success.
View 5 Replies
View Related
May 24, 2007
I'm trying to simply count a range of cells using the countif function. The range is on a different sheet within the same workbook then where the formula is. The formula is
=COUNTIF('Aggregated Results'!L3:L22,"yes"). It returns 0 (zero) for the count which is incorrect as three Yes's appear in the range of cells.
View 4 Replies
View Related
Aug 18, 2009
look at the attached file. How can I have the text values in Column A automatically be underlined if the concerning value in Column C drops below the value "1"? Note that Column C wont give you number values in this sample book as it refers to another file on my hd.
View 4 Replies
View Related
Jul 18, 2006
I have a range of numbers between 1 and 25 in 50 cells.
How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?
Or if there is another function to use, let me know.
View 11 Replies
View Related
