Count Periods Where Threshold Is Exceeded
Mar 20, 2009
I am trying to find a way to identify the nmber of periods where a particular value is exceeded.
Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days.
Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods.
The data is formatted as follows: ...
View 7 Replies
ADVERTISEMENT
Mar 25, 2014
From G13:G33 I have an array of values of which I have computed.
E36 is where I have set my threshold value.
What is the excel formula to use, to count the PERIODS (not number of times), which this threshold value has been exceeded?
View 3 Replies
View Related
Mar 25, 2014
Resouce Capacity Management .xlsx
How do I make my Pivot Table count/Sum the Threshold of each resource within department is within our 80% to 120% threshold?
View 1 Replies
View Related
Jul 22, 2012
I have an Excel macro which works well in comparing two columns A and B. It populates column C with unique values in A and not in B, populates column D with unique values in column B and not in A and puts the values found in both A and B in the fifth column (E).
Data in the columns A and B of Sheet 1 has exceeded 1,048,576 (the maximum allowable in Excel 2007) and i will copy the overflow data in Sheet 2's column A and B and i will like the comparison to continue in the next worksheet.
I need the macro to treat the second worksheet as a continuation of the first sheet and not as a separate data. (The values i am comparing have exceeded 1million).
Sub twocols()
Dim d As Object, na&, nb&, a, b
Dim e, p&, q&, r&, m
Set d = CreateObject("scripting.dictionary")
[Code] .......
View 2 Replies
View Related
Nov 30, 2013
I am attempting to create a formula that will count the number of times, lets say letter A, occurs in column E. However, the tricky part that I have been confused on is that I only want to count how many times A occurs between each hour of the day. I need to count number of times "A" occurs between each hour of the day for the entire month.
My Worksheet looks as such: Column A contains the dates for the month of November, Column B contains times that random instances occur, Column C is not important, Column E contains a Letter A-N that pertains to what occurred at a time in column B. SO, Column B and column E coordinate with each other.
Here is an example. Column A - Cell A1 - 11/01/2013 , A2 - 11/02/2013, A3 - 11/03/2013.
Column B - Cell B1 - 12:01 AM , Cell B2 - 14:03 , Cell B3 - 15:23
Column C - Cell E1 - A , Cell E2 - A, Cell E3 - B
So I would like Column F to display that between 12:00 - 12:59 Am there was X number of times the letter A occurred throughout the entire month.
View 4 Replies
View Related
Apr 6, 2012
I am currently using this formula to count dates between 2 date periods If specific condition exists.
=COUNTIFS(
Data!S1:S100000,"*KP*",
Data!X1:X100000,">=10/1/2010",
Data!X1:X100000,"=10/1/2010",
Data!X1:X100000,"
View 3 Replies
View Related
Nov 18, 2008
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
View 9 Replies
View Related
Jul 2, 2013
In the attached table the fees generated are on a sliding scale and the total is the fee generated within these ranges
I would like a formula in b10 to give me the value based on the total sale figure, i.e. 80.00. I've searched the board and tried some very long "if" statements and "lookup" table but to no avail.
Is there a formula I could use to calculate the desired value, preferable using cell references and not the actual values.
Sale Value
% Fee
Upto 2000
1%
2001 to 3000
1.50%
[Code] ........
View 2 Replies
View Related
Feb 20, 2010
my data will look like table below:
amount
(A1)
threshold(10)
(B1)
threshold(15)
(C1)
threshold(25)
(D1)
9.80 0.00 0.00 0.00 0.26 0.00 0.00 0.00 0.53 0.53 0.00 0.00 0.40 0.40 0.00 0.00 0.77 0.77 0.00 0.00 1.20 1.20 0.00 0.00 0.33 0.33 0.00 0.00 3.40 3.40 0.00 0.00 5.67 5.67 5.67 0.00 4.00 4.00 4.00 0.00 1.20 1.20 1.20 1.20 3.10 3.10 3.10 3.10 0.53 0.53 0.53 0.53 1.73 1.73 1.73 1.73
i need a formula to zerorize the amount that is within the threshold value &
just display the same amount as input if exceed.
column A is my input, colum B,C,D is my working area where there are few threshold value.
for example,
the B column give the threshold value = 10,
thus cell B2 & B3 were zerorize. ( not B2 only)
View 9 Replies
View Related
Feb 19, 2013
I have a financial model that returns ratios for various years. I would like to highlight in the summary part of the model those years where the ratio is over a set threshold.
For example:
A
B
C
D
...
1
2013
2014
2015
2016
...
2
31%
29%
41%
28%
...
3
Max:
30%
4
2 years over Max: 2013, 2015
In the above example, the threshold (Max) is 30% (cell B3), so the value in A4 would be "2 years over Max: 2013, 2015"
I have been able to do it manually by putting together a COUNTIF function along with various IF statements as follows:
=COUNTIF(A2:D2,">="&B3)&" years over Max: "&IF(A$2>$B$3,A$1&", ,"")&IF(B$2>$B$3,B$1&", ","")&IF(C$2>$B$3,C$1&", ","")&IF(D$2>$B$3,D$1&", ","")...
I even managed to get rid of the final "," by adding a second "," at the end and replacing the expression ", ," using the SUBSTITUTE function (yes, I am a bit **** when it comes to details).
My problem is that I currently have 16 years of projections and, although the above formula works, it requires manual changes every time I add / remove years.
I know that I can do it easily in VBA but the Excel file is to be shared with others via email and I know that their systems are setup to deactivate macros by default (and I don't want to rely on the user having to manually activate macros).
View 3 Replies
View Related
May 22, 2013
1 TO 10 25.00
11 TO 50 15.00
51 TO 100 9.00
101 TO 250 5.40
In this there is an area where it is cheaper to buy for example 12 instead of 10 and I am trying to work out a formula to deal with this funny step change down as people buy more.
View 2 Replies
View Related
May 14, 2014
The user enters data into Column E on Sheet1 and i want my code to display a pop-up box when a cell's value exceeds 500. I've tried the two codes below which i thought would work as Excel didn't highlight any breaks when i wrote the code, but no pop-up box is being generated when values > 500.
ATTEMPT 1:
Private Sub Threshold_Check2(ByVal Target As range)
Dim cell As range
For Each cell In ActiveSheet.UsedRange.Columns(5).Cells
If cell.Value > 500 Then
MsgBox "Value within 15% of Threshold"
Next cell
End Sub
ATTEMPT 2:
Sub Threshold_Check(ByVal Target As range)
Set Target = range("E1:E150")
For Each cell In range("E1:E150")
If Target.Value > 500# Then
MsgBox "Value within 15% of Threshold"
End If
End Sub
View 5 Replies
View Related
Jan 27, 2012
Is there a formula or combination of formulas that will return the word "Order" if the values in a column go above a certain threshold then start to decrease?
For example we have a list of data in a column that is imported each hour from an external database. Each new value is copied into the next cell down in column B. If the latest value was to go above, say, 100 is there a way of returning "Order" as soon as one of the subsequent values is lower than the previous one?
View 5 Replies
View Related
Mar 25, 2012
I am trying to put a message box related to a cell if a threshold values is reached.
E.g.: Cell A: 85
If cell A values moves to 86 I am trying to put in a message that says something like " This value is not allowed". Is there a way to do this ?
View 8 Replies
View Related
May 28, 2009
I have 2 sheets having name DB and Limit. In DB sheet i enter data and in Limit sheet I check each ID avalable Limit I want to ask if it is possible that a macro checks available limit in Limit sheet when I enter values in DB Sheet in Columns C & D and gives me warning message if entered values in DB sheet exceed from available limit in Limit sheet ....
View 9 Replies
View Related
Aug 15, 2007
I'm struggling with an alternative method of suppressing the # DIV/0 in my worksheet. I'm familiar with the ISERROR function and it's use as well as using =IF(A2=0,"",A1/A2), however I'm still getting #DIV/0 errors and I can't use ISERROR because
I have exceeded the number of nested IF's.
I've attached an example. In the example, the only time the #DIV/0 appears is when "Y" appears in the Commit and In-House column for all rows. This is the condition I'm trying to suppress the #DIV/0 error for.
All the formula's I'm using are Array Formula, as are all my attempts to suppress the error....
View 9 Replies
View Related
Sep 23, 2008
I hope the title makes sense. I have a spreadsheet that I'm trying to use to calculate volumes of liquid. For each liquid there is a minimum and maximum amount that can be included in a vial, and a checkbox to indicate if it should be included or not. What I'm having trouble with is some logic (other than a million if= statements) to determine the amount of each needed.
If only a few are selected, using the max value for each is fine as it is below the volume of the vial, so I want to use the max. But if enough are selected, the max for each would exceed the volume of the vial, and I want to pick a percentage of each component's range until we get a volume that will fit in the vial (ie, it figures out that 84% of the max for each will fit, but knows never to drop below the min).
View 13 Replies
View Related
Jun 29, 2009
I have been trying to do an if statement but need to choose between 10 options:
In Column AA I could have any one of the following 10 text entries - In column AB I need to return 8, 4, 2, 1 or 0.5 depending on which is in AA (this column AA cannot be sorted nor are people willing to cut back to 5 options )
"Very High" returns 8
"Very High (>50)" returns 8
"High" returns 4
"High (>20)"returns 4
"Medium" returns 2
"Medium (>10)" returns2
"Low" returns 1
"Low (>5)" returns 1
"Very Low" returns 0.5
"Very Low (<1)" returns 0.5
naturally the following IF statement won't work as I have exceeded the limits
View 2 Replies
View Related
Oct 14, 2007
What i would like to do is record a cell on sheet1 in sheet2 when it reaches a certain number i.e 2. The cell in sheet1 changes all the time but sheet2 must not refresh. Is this possible with a formula?
View 9 Replies
View Related
Nov 19, 2007
I'm trying to calculate when a waiting time falls outside and exceptable time period. This is based on two colums of data. The first column contains a number from 1 to 5 and the second a time period. (see attached example) in column "C" I need a formula that looks at the value in "A" and if it equals (1) and the time period in "B" is > 00:02 it should show "EXCEEDED". If "A" does contain a (1) then the code stops otherwise it does another loop of "A" looking for a (2) and a time in "B" of >00:10 etc.
View 3 Replies
View Related
Apr 8, 2014
Which exceeds the level of nesting allowed.
My formula is as follows:
=IF($J$55=C73,O73,IF($J$56=C73,O73,IF($J$57=C73,O73,IF($J$58=C73,O73,IF($J$59=C73,O73,
IF($J$60=C73,O73,IF($J$61=C73,O73, IF($J$62=C73,O73,IF($J$63=C73,O73,IF($J$64=C73,O73,""))))))))))
I am on Excel 2010.
View 3 Replies
View Related
Nov 19, 2008
i really frustrated to my excel file already . every time when i open it and it will appear a error box '' some text formatting may have changed in this file because the maximum number of font was exceeded'' .
View 9 Replies
View Related
Nov 14, 2013
I would like to highlight (conditional format) the "response required by" (Row A) cells / dates if the "current date" (cell $B$2) is exceeded AND if the "response provided By" (Row B) remains BLANK:
Current Date
=Today()
Response Required By
Response Provided By
29-10-2013
23-10-2013
[Code] .......
View 6 Replies
View Related
Feb 4, 2010
I got the following table.
http://img163.imageshack.us/img163/2972/oldnf.jpg
What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?
http://img687.imageshack.us/img687/5720/newcg.jpg
http://img168.imageshack.us/img168/413/new1zk.jpg
View 2 Replies
View Related
Jun 20, 2014
I'm using this formula (below) to add the hrs between 2 time periods (4:30 pm in cell I37 and 8:00 pm in I38, then the formula is in I39) and works almost every time even when the second time goes into the am hours, except in certain instances such as: 4:30 pm to 11pm show as 30.5? But sometimes it shows the correct number, 6.5
=(IF(I37>I38,I38+1-I37,I38-I37))*24
View 1 Replies
View Related
Oct 7, 2008
I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use today’s date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date.
I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days. I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically.
I have been playing around with this formula:.....
All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month. Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc.......
View 5 Replies
View Related
Feb 10, 2010
I'm trying to calculate the total hours worked for two given periods over a shift , which can span two consecutive days ie. start 15:45 and finish at 00:15 the next day. Hours worked between 6am and 6pm are paid at standard rate, whilst hours worked between 6pm and 6am attract penalty rates. Hours are cacluated in 24hr time
I have attached a copy of the timesheet that we use so you can see exactly what I'm trying to achieve, and included most of the shifts that we have.
View 14 Replies
View Related
Nov 23, 2011
When using the Trend function, I can get the value for individual future periods. I then can get a total for the year by summing each individual future period.
What I don't know how to do is sum them all using one formula. For instance, If I know the values of periods 1-5, how would I write a formula that would sum the future values (in one cell) of periods 6-12 -- without having to sum each future period individually?
View 3 Replies
View Related
Aug 5, 2013
I have a list of email addresses with the periods stripped before the .com, .net, etc.
How to insert the period to go from example@mailcom to example@mail.com? Obviously the email addresses are of different lengths, but it's always 3 characters after the inserted period.
View 3 Replies
View Related
Dec 29, 2006
I am having trouble writing a Macro to delete multiple periods at the end of each cell. Each cell looks simiar to this: Blah Blah Blah................... I am using the InStr function to find the first period but it returns a 0 everytime. Here is the code I used to find the period. I want it to delete everything after the first period and store the value back in the same cell. Right now it just stores blanks.
Sub PeriodRemover()
Dim NewContract As String, Contract As String
Application.Calculation = xlManual
i = 2
Sheets("Product Listing by Contract").Select
Contract = Range("A" & i).Value
Do Until Contract = Empty
NewContract = Left(Contract, InStr(Contract, "."))
Range("A" & i).Value = NewContract
i = i + 1
Contract = Range("A" & i).Value
Loop
Application.Calculation = xlAutomatic
End Sub
View 4 Replies
View Related
