# Exponential Weight Volatility Average

Jul 25, 2006
to write a function that will exponentially weight a series of returns by a certain decay factor.

I have excel spreadsheets that can do this over several columns, like this

series of returns... rtns squared

2.50% =2.50%*2.50%

3.50% =3.50%*3.50%

5.00% =5.00%*5.00%

1.01% =1.01%*1.01%

0.75% =0.75%*0.75%

4.01% =4.01%*4.01%

-2.78% =-2.78%*-2.78%

View 5 Replies
ADVERTISEMENT
Feb 14, 2013

A

B

C

D

E

9

et Boxes

Gross Weight

Ice Weight

Box Weight

Net Weight

10

Box 1

60

5.5

4.5

50

d10=IF(B10>=1,4.5,0)

[Code] .......

View 1 Replies
View Related
Nov 21, 2008

I have some aged data, as follows

325days - $225

20days - $2,725

16days - $27,250

11days - $8,175

Now, if I just take the average number of days ((365 + 20 + 16 + 11) /4) I get 93.0days

However, I want to high value days to carry more weight, than the lower value days.

I gave each set of data a weighted score by dividing the value by the total value, i.e.

$225 / $38,375 = 0.006

$2725 / $38,375 = 0.071

$27,250 / $38375 = 0.710

$8175 / $38375 = 0.213

View 9 Replies
View Related
Dec 13, 2008

Hi there, been using this for a number of years now, but never had to post a question - I guess most things are already answered - but this one is a bit more tricky.

I want to calculate a totals and a weighted average based on months from a data set that has duplicate ID's and variable data (attached example).

I would like to calculate the total tonnes by month and the average grade of those tonnes. Using the 'month ID table' as a reference to the 'Raw Data table'.

Is this possible using if and vlookup functions or will require a higher level of programming knowledge?

View 6 Replies
View Related
Jan 29, 2010

I have a SumProduct formula that processes ranges of integers, and should deliver the integer answer of 504.

Instead, it delivers the answer of 503.9999999999990000000.

Is that what is meant by "volatility"?

Most of the expressions in the formula are already preceded by a SIGN function, and I was under the impression that one benefit of the SIGN function was to guard against volatility.

Anyway, here's the formula. I don't expect anyone to examine it in any great detail, but perhaps you might share some tips or tricks to prevent the volatility of the result:

View 10 Replies
View Related
Jul 23, 2008

I have an LSE spreadsheet with a list of 150 stocks. I need to find the easiest way of calculating the volatility of each stock using either standard deviation or garman-klass. Is there way of coding it so i can probable pull through data from the internet nd run simultaneous calculation for each of the stocks?

View 9 Replies
View Related
Oct 10, 2012

Im trying to enter a formula for volatility in my excel 2010 spread sheet

my hig low close is listed verticaly in A,B,C

The formula for D1 is supposedly “={LN((A1)/(B1))}^2”

The formula for E1 is supposedly =SQRT(SUM(D1:D10)/10)*SQRT(252)*100

I put this in D1 and it is giving me an error

View 3 Replies
View Related
Apr 25, 2008

I am working with a program that creates numbers as follows:

Instead of -2.74E-6, the program writes -2.74-6. The E is left off to save space.

When I import this data, Excel automatically "does the math" so the cell reads: -8.74 instead of -2.74E-6.

View 9 Replies
View Related
Feb 27, 2014

I have the following data

A1 - 65.23

A2 - 1923.52

A3 - 945.18

A4 - 182.54

In column B

B1 =exp(A1)

B4 =exp(A4)

both are fine, however

B2 =exp(A2) - it is showing #NUM

B3 =exp(A3) - it is showing #NUM

Same case for all larger numbers

View 1 Replies
View Related
Jun 18, 2008

I have inherited a series of data relating to a change in a specification over a period of time and a number of cycles.

See attached.

There is already a chart which shows the data and has an exponential line.

I want to find the value of Cycles where the Average Flash exponential from the chart line is 0.131.

FYI this is to plot deterioration in a piece of tooling, 0.131 being the accepted warning level. If you feel there is a better statistical model to use for that application then I'm all ears!

View 9 Replies
View Related
Dec 12, 2013

I have a frequency table (attached) that I need to be able to interpolate values between the known values on something other than a linear basis (e.g. exponential or logarithmic).

View 5 Replies
View Related
Apr 16, 2014

interpolating numbers in excel for an exponential function. I want to know how to be able to curve a curve in 4 different ways essentially the 2 that make up the two halves of a peak and the 2 that make up the two halves of a trough. I've tried using the LINEST function to create this interpolation and it's good to get 1 of the 4 curves I'm interested in. how to do the other 3? I'm also interested in knowing if these functions "whip the tail up" as in almost like how the tail end of a "sin" function flips back up. here it is.

[URL]

For this example of the exponential function, it creates the curve in one particular direction. I was wondering how I would do it to have it curve in another direction, for example I was testing the same function, but going downwards, and also having the tail curve back up (by hump I mean to simulate a gaussian distribution with the tails) how would I do that. seems like I now have part 1 of 4 aspects of the curve. here's the attached file to show what I mean. I want to get the hump going up and then sort of reflected along the y-axis and then similar features if I were to reflect the curves on the x axis. is it possible with the functions in excel? Linest test.xlsx

View 14 Replies
View Related
Jan 28, 2014

In excel if it changes number to exponential format it would default to, for example if number is 12345 -> 1.2345e4, 0.012345 ->1.2345e-2

I was wondering it is possible to which a function to change this default format ie for example 0.012345 -> 12.345e-3

I basically want this kind of formatting as its would display information in terms of milli, micro, pico (or the opposite side kilo, mega, giga and so on).

View 7 Replies
View Related
Dec 16, 2008

I have some short code that imports data and account numbers that is "|" delimited. The problem I am having is that during the import, excel converts some of the account numbers to exponential numbers.

e.g. it converts 542690E24 to 5.4269E+29.

Is there a way to avoid this from occuring during the import? I tried to format the destination cells as '000000000' (all accounts are 9 digits) but it didn't work.

View 6 Replies
View Related
Dec 5, 2009

I've been working on a ss that requires me to create a function to determine a value from a exponential curve where the area of the curve is variable . The terminal points, 0,0 and 100,100 remain constant and the curve is symmetrical at the 45degree angle. How can I derive a function knowing only these things.

Here is an image to crudely illustrate what i mean [url]

The area can be 10, 20, 35% it doesn't matter, I need a way to determine the function from just the area!

View 14 Replies
View Related
Dec 6, 2013

I am trying to find the exponential equation from a set of data in excel. The problem is that the graph does not follow the scatter plotted points on the graph. How would you remedy this?

View 1 Replies
View Related
Dec 26, 2009

I have a bit of code that is grabbing only the first 9 positions of an alpha-numeric string. The problem I am running into is that when the alpha-numberic value contains an "E", the code turns it into an exponential number in error.

For example, I have the following in column G: 01877E107000. When I run the code, it turns into an alpha-numeric value. The result should be 01877E107 (in column K). I tried using the text function within the code.

View 3 Replies
View Related
May 1, 2007

I was wondering if anyone could tell me how to do away with the "Exponential" appearance of numbers (in Excel) when they are formatted as text. I am working with National Stock Numbers and there are no required computations based on them being formatted as a number ... AND ... one of my constraints is that they are required to be formatted as text when imported into an Access database ...

View 5 Replies
View Related
Jun 9, 2008

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies
View Related
May 31, 2008

I use hex2dec frequently and input with a hex 'E' such as 05e8 is misinterpreted as exponential notation. I can use =hex2dec("05e8") to get the correct value, but when the hex number is in another cell, I get the exponent assumption/ conversion (to 5 x 10e8), or a # NUM error if it is in quotes. This seems like a blatant problem but I've found no mention of it in searches or FAQs.

View 3 Replies
View Related
Aug 31, 2007

I am trying to create a weight chart, which works in stones, pounds and ounces. My first query is that each time I enter the weight in for each day, I need it to deduct 1ib 4oz from the weight I enter. At present I have seperate columns for stones, pounds and ounces. However, if I try to do a simple lbs minus 4 it doesn't work, because sometimes the pounds are less than four - so instead of taking the stone column down by one and then deducting the remaining pounds, it just puts the pounds columns to -3 or similar.

Also, I would like my table to work out weight lost over a period of time - so, I have my starting weight at the top and then I want my most recent weight to be taken from the starting weight, leaving weight lost - I can't get this to work either. Is there a way to make it so that the calculation uses the most recent weight entry and does so automatically?

If anyone has any solutions to these queries, I would be very grateful...as I have to caculate this for a number of people and it would be much quicker if it did it all automatically upon entering the most recent weight.

View 9 Replies
View Related
Mar 17, 2009

I have attached a sheet that contains a pretty simple formula that helps me figure out the dimensional weight of a shipment. It is just a simple vlookup that returns the proper rate (Column H) depending on DIM Weight and Zone. It works great.

The issue I'm running into is with various exceptions that I need to put into the formula. The criterias that I need to put into the rate formula (Column H) are:

1-If the one of the Length (Column A), Width (Column B), and Height (Column C) is greater than 60 I need to add 7.50 to the the returned lookup value in Column H.

2-The second longest side the Length (Column A), Width (Column B), and Height (Column C) is greater than 30 I need to add 7.50 to the the returned lookup value in Column H.

3-If the grith (2x Column C + 2x Column B) is greater than 130 I need to add 45 to the the returned lookup value in Column H.

I'm working with a few IF formulas but I can't seem to get all of them to work at the same time. Does anyone have any thoughts?

View 8 Replies
View Related
Mar 18, 2009

calculate dimensional weight (below).

View 3 Replies
View Related
Mar 16, 2008

I have 1 spreadsheet in which I need to calculate the % weight of some numbers. Columns A to G have data I do not need for the calculation.

Column H has the numbers of whose % weight I need to calculate. The problem is that the no. of rows containing the numbers can change from week to week and I am trying to automate the calculation of the % weights.

So I want try to use column G (as a reference) (like go at the bottom of it so I can know how many rows of numbers there are) and then calculate the % weights.

View 9 Replies
View Related
Sep 15, 2014

I have two columns. One with the major weight(pounds and one with minor with ounces. The first column would have a 1 the second 8 for 1 lb 8 ounces which I need to combine to say 1.5 Every way I do it it is coming up with 10.5.

View 5 Replies
View Related
Aug 2, 2008

How do I track my total weight loss in excel? Here is an example of what I am trying to do.

8/1/2008 228lbs

8/2/2008 225lbs

8/3/2008 223lbs

Total 5lbs

I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?

View 6 Replies
View Related
Oct 18, 2009

I cut alphabet letters out of MDF. I need to calculate the total weight of any letter.

I have a program that will give me the area of any shape. In the picture below I have started with the letter A. B7 is the over all area. D7 is the small triangle. G7 is the total area minus the triangle D7. H7 is the thickness and I7 is the total volume.

The weight of my MDF I have set at is 850kg cubic meter. Thats set in A3.

I need the total weight of the letter in J7 to be in grams.

The idea is, I put in the areas and set the thickness and it gives me the total weight.

View 9 Replies
View Related
Apr 15, 2008

I'm trying to make a points calculator and tracker for a diet.

I have the forumla from wikipedia and it calculates the point now I want to record the date, food, meal & Points on a second sheet.

I enter that data in

E1 =Date

E2 = Point (calculated)

E3 = Meal (Breakfast, Lunch, Dinner Etc)

E4 Food (text)

E6 Calories (number)

E7 Saturated Fat (number)

I want to add a button that records this data to another work sheet and clears the inputs I have made.

The following Date should be carried across

E1, E2, E3 & E4 into A2, B2, C2 & D2 (or the next available row)

The following Cells should be cleared (E1, E3, E4, E6 & E7)

View 9 Replies
View Related
Nov 4, 2008

I have a Weight Watcher spreadsheet. I enter the Calories, Fat and Fiber and I get the # of points for that food. Then I keep put those points into the lower part of the spreadsheet where I track what I eat, and Excel does my simple math for me. What I want to do is keep a list of my foods, and their point value in my spreadsheet. I would like to be able to put the food name in Cell A1, the WW points in BI, and then be able to click a button with a macro that takes those two pieces of data and add them to bottom of my food list. I don't have a food list yet, I would assume I can just put it on sheet 2. I then want to be able to use lists in my cells to choose the food in the future. I need to be reminded how to make "dynamic named ranges. Today, I just need to know how to write the macro to copy and paste those two cells in the first open row at the bottom of my food list.

View 9 Replies
View Related
Apr 22, 2010

I am using the following few lines of code:

Code:

Range("A3:L" & ActiveCell.Offset(1, 0).Row).Select

With Selection

.Borders(xlEdgeLeft).Weight = xlMedium

.Borders(xlEdgeTop).Weight = xlMedium

.Borders(xlEdgeBottom).Weight = xlMedium

.Borders(xlEdgeRight).Weight = xlMedium

.Borders(xlInsideVertical).Weight = xlThin

.Borders(xlInsideHorizontal).Weight = xlThin

End With

However am receiving an error: "Unable to set the Weight Property of the Border Class". The odd thing is, I only receive the error on the following 2 lines:

Code:

.Borders(xlEdgeBottom).Weight = xlMedium

'and

.Borders(xlInsideHorizontal).Weight = xlThin

The sheet is not protected, events are disabled, and the range that gets selected changes dynamically based on the number of rows of data in the table, however in this example cells A3:L15 are selected.

View 9 Replies
View Related