Autocorrelation Function |
Autocorrelation Function - Excel |
|
any suggestions? ive calculated the daily returns but am stumped on what to do next. ive been messing with the CORREL function in excel but i havent been getting very far.
any comments would be appreciated!
Similar Topics
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the x-intercept. Any help would be greatly appreciated.
I have 2 colombs of data.
AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.
At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1
The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.
How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...
ANy suggestions??
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
Here is the example.
Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- Product XXXXX Actual/Forecast : 174 1674 574 574 524 524 524 524 524 728 728 728 New Purchases : 0 0 0 0 1002 0 0 0 0 0 0 0 Goods In Transit: 0 1506 0 0 0 0 0 0 0 0 0 0 Other Movements : 0 0 0 0 0 0 0 0 0 0 0 0 Closing Stock : 1270 1102 528 -46 432 -92 -616 -1140 -1664 -2392 -3120 -3848 Week Stock in Hand ? ? ? ? ? ? ? ? ? ? ? ?
So, in Month of Jan2011 the Closing stock is 1270, need a formula to calculate how many weeks will this stock cover look at future forecast.
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Im unable to put any pics up as my work pc has java disable so photobucket ect won't work but has anyone else come across this problem and how did you rectify it? A search macro perhaps?
The following can be used to simulate the same, and avoid having to achieve the same with VBA's IsDate function:
STEPS:
1- Format the column (ex A) as text
2- Formula to check for valid dates:
=ISERROR(DATEVALUE(A1))
... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...
... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty
Any help is appreciated.
Alanda
format the text cells (name, etc) to have text entered as uppercase
automatically although the user might use title or lower case.
UPPER function cannot make cell look at itself and perform the function
Excel 2003
a set of given numjbers. For example, 1, 3 and 8 would generate 138, 183,
318, 381, 813, 831 and so on...
On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.
I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.
Please can someone help? Thanks!
Many Thanks
Karamazov
date range that was provided, and to return Early, Late or Ok accordingly.
First post on the forum, have only recently joined and im hoping that someone can help me before my head explodes!
I am trying to prepare a simple Material Quantity List for some Steel materials.
The materials are all in specific sizes.
What i want to do is, as i input the length, width and height of the structure in the spreadsheet, the Material Quantity List will generate the quantity of each piece of material i will need.
Does anybody know of a formula for this. I have been trying to use the IF function and getting absolutely nowhere, all suggestions will be highly appreciated!!
I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet. Since that's a horrible explanation, let me try explaining it this way:
Sheet 1
Column A has a long list of code type 1s
Column E has a long list of code type 2s
Sheet 2
Cell C2 has code 1
Cell E2 needs code 2
I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:
=INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1"!E:E,0),5)
But all I get is no result. What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine.
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!