Compare Time Series
Aug 21, 2007
I've got two time series (growth rates: what was the forecast for any particular year and what was actual growth rate for that year). I need to compare them to flag those most different. Actually I want to check the accuracy of the forecast. What would be the best function to do it?
View 5 Replies
ADVERTISEMENT
Apr 19, 2009
Vog I am trying to use your code from following post
http://www.mrexcel.com/forum/showthread.php?t=384758
Actual when I use to compare 2 series in same sheet I use following formula in column C (All Series "Number Stored as Text")
HTML If(Iserror(Vlookup(B2,$A$2:$A$40000,1,False)),"Not Found", "Found")
How should I use mentioned post code as per my formula?
View 9 Replies
View Related
Feb 21, 2014
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
View 3 Replies
View Related
Nov 19, 2008
I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:
=((I12/100)*25)/D12
After 1pm I would like the formula to be
=((I12/100)*25)/B12
I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
View 6 Replies
View Related
Jul 24, 2007
I need a way to compare the time of the computer with the one in the cell and bring up a message box if the time matches.
Yet i don't want it to be a one time makro. What i want is to be able to work with the worksheet and that at the same time i want a paralel function to check whether the time in the cell matches. Something like agenda in Outlook.
View 4 Replies
View Related
Mar 26, 2008
I'm trying to calculate and average using a time frame in the day. I want to use a certain number "X" that correlates to a hour/minute in the current time. How do I write a statement to get me the correct "X" value?
example:
8:00 .005
8:01 .006
8:02 .0075
8:03 .0077
8:04 .008
time now is 8:03
How do I get the .0077 in excel?
View 10 Replies
View Related
Jun 1, 2008
I attached the excel file with two time series data next to each other – the price and an indicator data. I have to automatically identify when the two time series DIVERGE from each other. Diverging occurs when the price makes a new high and the indicators doesn’t make a new high (called “bearish divergence”) and also when the price makes a new low and the indicator doesn’t make a new low (called “bullish divergence”). I attached the two pictures I made to demonstrate these two divergencies.
To spot the divergencies one would go through the price data – finding new highs and lows and then compare each consecutive high and low with the corresponding reading on the indicator field to see if it is also making new high or low. There would be a minimum and the maximum of data points to look back – that would ideally be configurable from a separate input cell. The problem is that I do not know how in principle such task should be approached - is it best to make a macro or can this be done through a series of formulas each building upon the previous?
View 6 Replies
View Related
Dec 29, 2013
I have a series of up to 40 or so entries for given dates. These entries are categorised by either "bid" or "offer" and I'm trying to find a way to chart them both using a pivot table. I'm stuck as it seems that a pivot can only count the number of bids and offers on any given day, but it cannot differentiate between them.
Note: that the original list the pivot depends on will constantly be updated so I can't just develop a custom chart.
I've attached a spreadsheet with an example of what I'm playing with. Pivot graph is in "PIVOT" worksheet, data is in "DATA".
pivotland.xlsx
View 6 Replies
View Related
Oct 24, 2007
I am trying to plot two data series in one chart. The first series has monthly corn prices for last 10 years (~130 data points). The second series has bimonthly chicken prices over the last 10 years ( ~72 points). How can I plot them together?
The bi-monthly periods for the second series do not exactly coincide with calender months ( e.g. Aug 19 to Oct 13, 2007). As a result, I can not adjust the points to match with calender months.
View 2 Replies
View Related
Apr 21, 2008
I just joined ozgrid and this is my very first post. I have read the rules but spare me if I make some errors.
My question is related to plotting time series charts using VBA. I have data in the following format.
04/21/08 9:04:47 4.5055
04/21/08 9:04:48 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:50 4.5055
04/21/08 10:05:00 3.5055
04/21/08 10:05:32 3.5055
04/21/08 10:05:40 3.5055
04/21/08 10:05:48 3.5055
04/21/08 10:05:51 3.5055
04/21/08 11:07:59 5.5055
04/21/08 11:09:00 5.5055
04/21/08 11:10:01 5.5055
04/21/08 11:11:02 5.5055
04/21/08 11:13:08 5.5055
04/21/08 11:14:11 5.5055
04/21/08 11:15:16 5.5055
The column on the left is NOW() output and the one on the right is data corresponding to the given date and time. You may interpret the above data as snapshots taken at regular time intervals.
Now what I want to do is make a chart with the above data. Where ever there is a break in the snapshots, represented by a blank line, I want the graph to jump to the next data point much as the way stock prices move from the closing price to the opening price on the next day. I am not able to figure how to do that. Since I want the x-axis to represent time, using scatter charts does not work since the intervals are not proportional to the time then.
View 9 Replies
View Related
Jul 27, 2014
I'm trying to create a spreadsheet for irrigation system program times. Each program has a start time, say 2 a.m. Multiple zones then run for varying times in a cycle. I need to add the run time to the start time to produce the next start time, and continue that through many zones. I can get the first few by simply formatting the cells as ##:## and using =F2+G2 (the start time is in F and the run time is in G) using 20 minute run time as a test. But when it gets to the third zone, my results show 2:60 a.m. and all results are incorrect from there on. Is there any way to do what I need in Excel?
View 2 Replies
View Related
Dec 21, 2006
I have been browsing here off and on, and have found many excellent answers. I use Excel to process data on time series, as an adjunct to consultancy work on statistical analysis of industrial data . Usually the data has irregular gaps, e.g., daily data might have 2-10 day gaps. If I want to take, say, 7-day averages, SKIPPING OVER gaps longer than 2 days(say), is there an easy way to do this (I don't really know VBA,and it is not worth my time to try and write long code for this, which will eventually be done by some professional programmers)!
View 9 Replies
View Related
Aug 9, 2008
i have a time series of data at one hour interval ( from 6:00 hrs to 18:00 hrs ) each day and again from 6:00 to 18:00 hrs next day and so on for several years. in this series many data are missing. i ve to fill in these missing data in such a manner so that the new values becomes the linearly interpolated values in between the data given at any two hours. no rows are allowed to be inserted in between 18:00 hrs of the previous day to 6:00 hrs on the next day. pl see the attatchment.
View 2 Replies
View Related
Mar 1, 2014
I need to calculate the INTERCEPT and SLOPE of following daily stock prices, but 60 days before the announcement days. Its for my dissertation and Its the first time i have to work with that much data. Event study.jpgEvent.xlsx
View 6 Replies
View Related
Apr 24, 2014
I have a chart with 5 different series. Instead of editing each one, say the marker size, is there anyway I can change the marker size for all of the different series at the same time?
View 1 Replies
View Related
Mar 1, 2014
I need to calculate the INTERCEPT and SLOPE of following daily stock prices, but 60 days before the announcement days.
My data is organized like this
60 days before
25/05/07
-
20/03/09
-
23/11/03
[code]....
View 1 Replies
View Related
Jul 6, 2014
I've been trying to get a graph that looks like the image below for a while now and can't seem to get it to appear how I want. I am looking to duplicate it exactly, but the issue I'm having is getting the individual bars to be separate and appropriately spaced.
[URL] .....
View 7 Replies
View Related
May 22, 2014
I'm having some problems with the removal of duplicate values in a time series. The time series consist of stock data for about 10000 firms, but about 3000 of these firms died(delisted) before the end of the sample. The database I used to extract these data, however, does not remove the firm values from the sample, but keeps the last known value of the firm for the remainder of the time. Therefore, I need to remove these duplicate values, but only if they are sequential. Furthermore, normally if you remove duplicate values, excel will delete the cell and shift the data upwards, since it's a time-series I would rather have excel just blanking out the cell instead of removing it.
I've looked at normal solutions, like using the countif functionallity in excel, but this is not feasible because of the size of the worksheet, also, it does remove non-sequential duplicate values as well.
summary:
- Want to remove duplicate values, but only sequential values.( It's possible firm value is the same somewhere in the time period)
- The cells should be blanked out instead of deleted.
View 2 Replies
View Related
Jan 8, 2014
I have A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.
If I were to do it manually 365 times, the macro would look like this:
Copy Row A1:X1
select sheet 2
Paste Special > Transpose (fills A1:A24)
repeat for rows 2-365
Tried using this from a previous post, but it takes A1:A24 then B1:B24 and so on and makes them a single column - so it doesn't work unfortunately.
Sub MakeColumn()
Dim rng As Range
Dim LastRowSrc As Long
Dim LastRowDst As Long
Set rng = Worksheets("Sheet1").Range("A1")
[Code] .....
View 3 Replies
View Related
Aug 12, 2014
The following code gives me error message. There is problem in Format function. How can I write using VBA that "Scantime is less than 08:00"
[Code].......
View 3 Replies
View Related
Mar 30, 2007
I want to compare Now() to a time, such as 10:00 AM, and use the formula in conditional formatting... and am having some trouble determining how to do it.
My latest attempt is
=IF(NOW() > TIMEVALUE("10:00 AM"),"t","f")
which returns "t" no matter what time I insert.
View 5 Replies
View Related
Dec 7, 2009
Excel seems to be miscalculating some formulas and I was hoping someone might have seen this before:
I'm attempting to do math on two cells and return the result in a third cell
Jx = time typed manually
AAx = time that is the result of a lookup function from data in a different worksheet
ABx = the result
Here is the formula that I am putting in ABx: =IF(ISERROR(AA39-J39),"",IF(AA39>=J39,(HOUR(AA39-J39)*60+MINUTE(AA39-J39)),-(HOUR(J39-AA39)*60+MINUTE(J39-AA39))))
J39 contains the time 18:31
and AA39 contains the time 18:30.
When the formula evaluates, the error #NUM is returned. Here are the calculation steps:
IF(FALSE,#N/A,IF(TRUE,HOUR(-.00069444444431)*60........)))
What is happening is excel is evaluating the If(AA39>=J39) as true, even when J39 contains 18:31 and AA39 contains 18:30.
View 6 Replies
View Related
Mar 12, 2014
I get a downloaded report from one of my application. Two columns have date and time data. I need to compare these two columns (subtract) and come up with a value in another column next to it to do some analysis on an ongoing basis.
So I know how to compare date and time in two cells but the problem I have is second set of date / time have other text mixed with it and there is some character that Find/Replace can not find (it looks like the "pipe" | character) and I can't replace or delete that character. Additionally when I click on that cell (B1 in attachment) I don't see this pipe character in the top formula section (fx) but I do see this character in the cell itself.
how I can either get rid of this character or extract just the date / time information from cell B1 to be able to compare cell A1 easily?
View 8 Replies
View Related
Jan 7, 2008
I am trying to get rid of some duff data by running a comparison to a fixed value on a defined range. However, the macro tries to compare the cell value (00:05:00) as a decimal value.
I think I need to use the format function to get round this.
For Each timecheckcell In range("g3:g60")
timecheckcell.Value = Format(MyTime, "h:m:s")
If timecheckcell.Value <> "00:05:00" Then timecheckcell.Offset(0, 1) = ""
Next
View 9 Replies
View Related
Jul 18, 2013
for example i have two work books where i need to compare the times, such as i need to see where does 10:26 am lies and after comparing it on workbook2 we need to return the data in a,b,c blocks infornt of 10:26am, i have tried IF(AND()) but i was not able to rationalise it for huge and random data.
Workbook1:
7/13/2013 10:26
7/13/2013 10:58
7/13/2013 12:06
7/13/2013 12:17
7/13/2013 12:29
7/13/2013 12:29
7/13/2013 12:37
7/13/2013 13:21
7/13/2013 14:24
Workbook2:
9:58:27abcassigned
11:45:09abcreleased
11:49:00ghiassigned
15:14:40ghireleased
15:25:57mnoassigned
Outout should be:
7/13/2013 10:26 a bc
7/13/2013 10:58 a bc
7/13/2013 12:06g hi
7/13/2013 12:17g hi
7/13/2013 12:29g hi
7/13/2013 12:29g h i
7/13/2013 12:37ghi
7/13/2013 13:21ghi
7/13/2013 14:24ghi
View 2 Replies
View Related
Jun 27, 2014
I have two Date range in different cell and I want them two return "TRUE" value even if the start time or end time of another is earlier or later by 1-2 hours:
Example: I want to Compare Data from Cell A to B. Values should return True as it is within 1-2 hour range.
A1
B1
C1
10:00-20:00
08:00-17:00
12:15-21:15
13:45-22:45
View 4 Replies
View Related
Oct 5, 2008
in my spread sheet i am trying to use the if function to compare the current time ( vb code used) to a time i enter to alter another cell.
for example
if A1(current updating time) = A2 ( time i choose) then
B1( some value ) = B2( another value)
the point of this equation is to have B2 update its value from B1 every day so today B2= 3 and B1 =4 then tomorrow B2 would = 4 etc , i just dont know how to do it.
View 9 Replies
View Related
Aug 4, 2014
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
View 1 Replies
View Related
Dec 22, 2011
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
View 4 Replies
View Related
May 31, 2008
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
View 4 Replies
View Related