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)!
My worksheet takes a time feed in the format '00:01:05' and I want to trigger an event when it goes to negative (eg, -00:01:05). But I have no idea how to handle it.
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:
I get sheets, sometimes 10 records, sometimes 1,000 records. And there is a time column. It's formatted as: hh:mm:ssam (09:12:36am). Now what I need to do is find a way to highlight any record that has a large gap between itself and the record before it. This gap amount can be variable, but for the explanation lets say, I need to highlight any record that has a gap of 5 minutes greater than the time before it.
I've tried a formula I found elsewhere:
=F3-F2> 1/24/60*5
But it just returns "#VALUE!".
I'm completely stumped, and since about last week, 1 of my daily responsibilites will be analysing anywhere from 10 - 50 sheets a day.
The guy before me would open the sheet and manually find it by literally looking from record to record. This was this 1 guy's full time job. From what I'm reading around there are ways to have excel do this for me with a simple formula and conditional formatting.
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?
I'm currently working on that requires me to compare and update between two different workbooks, "MyData" and "Daily".
As the names imply the first is an ongoing database the second "Daily" is upto date information that arrives each morning. In both workbooks there is a column that contains unique data/serial number.
I have written some code that compares these numbers, if they are the same the new upto date information overwrites the original information (replaces entire row), if the serial number is new it adds it as a new entry in the last row.
Well so far so good, actually I have only managed to do this on the same worksheet, but it does work.
As there is a lot of data I'm looking for the fastest way to "look" for this information should I compare between the two open workbooks line by line, should I copy all of the data from the "Daily" to a worksheet in "MyData" and work from there.
I am writing a code in VBA using getpivotdata function. It is working fine untill the value does not exist.
Dim pt As PivotTable Sheets("Pivot2").Select Set pt = ActiveSheet.PivotTables(1) Cells(2,1).Value = pt.GetPivotData("ID", "GCB", "1", "Gender", "F", "9 box Rating_2012", NBArr(k)).Value 'the code is looping through the values in an array called NBArr
I have tried to handle this as follows:
If IsError(pt.GetPivotData("ID", "GCB", GCB1, "Gender", Gen1, "9 box Rating_2012", NBArr(k)).Value) = True Then Cells(2,1).Value= "0" ElseIf IsError(pt.GetPivotData("ID", "GCB", GCB1, "Gender", Gen1, "9 box Rating_2012", NBArr(k)).Value) = False Then Cells(2,1).Value = pt.GetPivotData("ID", "GCB", GCB1, "Gender", Gen1, "9 box Rating_2012", NBArr(k)).Value End If
However, even this is not working (in case when the function finds no value).
I have a excel document that is very heavy with Data Validation List Boxes. Below is just one example. Error handling is what I am trying to figure out. Description: I have 3 List Boxes. One list box data is based on data selected in the previous list box.
1st List Box uses this formula: =Series 2nd List Box uses this formula: =INDIRECT($C$2) 3rd List Box uses this formula: =INDIRECT($C$3)
It all works! HOWEVER.... If user decides to go back and change selection in first or second list box, and forgets to change the data in the second or third box, then data will be incorrect. How do I solve this problem? Is there anyway to write something so it would give them an error message and instructing them to correct the data?
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?
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?
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?
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".
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.
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?
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.
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
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?
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.
I'm having a problem with a list that I've created. The list is in cell A1, the base data for the list is in the range B1:B50. The problem is that data in this range is dynamic, i.e. it has formulas and depending on the result of these formulae the cells in the range either have a value or the cell is left blank. The problem this causes is that the list ends up having gaps in it because it uses blank cells as well. And this is despite me specifcally ticking " Ignore Blank " in the Data Validation menu where I'm creating the list.
I have around 2368 rows for in each column and I have around 8 columns and what I need to do is to remove any gaps. I do not know how to attach picture here, but I can explaining it in words.
A1: 0.9 A2: A3: A4: A5: -0.09 A6: A7: 0.4
Is there a way to eliminate those gaps (A2, A3, A4, A6...) in one go?
I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.
Ext P$ Rank Should be 2,128.34 1 1
[Code]...
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
I'm wanting to do is drag a formula down and it drop to the next cell rather than the same row number I'm on. For example I'm trying to concatenate a list of phrases whilst changing the main word. Here's an example of the excel sheet
Base Terms Phrase Result car red van blue bus red blue
There is meant to be a space after the second red and blue enabling me to make (in order), red car, blue car, car red, car blue
How can I make it so I've done the relevant concatenate formulas for A2 with the B column and simply drag it down and Excel will switch from A2 to A3 and so on when I've dragged out the 4 formulas?
I have a file that contains addresses in column C. I need to find any gaps in addresses.
Ex: 211 Corbin Dr 213 Corbin Drive 214 Corbin Drive 123 Apple Drive 124 Apple Dr 124 Apple Dr 127 Apple Drive
I want to identify that there is a gap between 211 Corbin and 213 Corbin and 124 and 127 Apple Drive.
Currently the address including house number are in column c. However, I split the house number into a separate column and the street address in yet another column. There are also duplicates which I have identified by using conditional formatting to highlight the duplicates.
