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?
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 View RelatedI 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?
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
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.
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.
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 RelatedI 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 Relatedi 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 RelatedI have a series of procedures I am calling from a function that each run fine when executed individually. These procedure are titled: DataValidation, Test1, Test2
The function will not run past Test2- I do not get the msgbox "Stage 2"
Code:
Public ArrayD
Public ArrayD2 As Variant
Public RngD3 As Range
Public Function Test(arg2 As Range)
DataValidation arg2
[Code] ............
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 RelatedI 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 RelatedI 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]....
I have used the MAX function to populate the most recent date in a series.
-EX. the most recent date input into the cells S8, T8, U8 will populate in cell R8. There is a default value of 0-Jan-00 that automatically populates in cell R8 as a result of the MAX Function. Any value that is input into cell S8, T8, U8 will show in cell R8 immediately after being input as a result of the MAX Function.
I need cell R8 to remail blank until the last of cells S8 or T8 or U8 has been input (in no specific order). Once the last cell has been filled; it is only at that time I need cell R8 to show that most recent date input into any of cells S8 or T8 or U8. I can't hide the column, I need cell R8 to remain empty until the last of the three cells- S8, T8, U8 has been filled (the last cell filled will not always contain the largest value or most recent date).
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] .....
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.
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] .....
I would like to make the SUMIF function conditional on recognising 1 word among a series of text within a cell.
For example, SUM all cells containing word PINK, even if there are other words in the cell also.
I cannot get the SUMIF function to pick words out of a cell, it will only SUM up if the whole text matches what is in the cell.
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View 2 Replies View RelatedI am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
View 1 Replies View RelatedI need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
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().
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] .....
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.
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?
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
D: is quantity to be made
G: is number of minutes this will take
J: is the time each order should be finished
Problem
at 12:00 work stops for lunch and resumes again at 12:30
Somehow I need this to be reflected J:
I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:
I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)
How do I write that kind of formula?