Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Moving averages in Excel
I have a need to calculate a 10-interval vs x-interval moving average.
Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#2
|
|||
|
|||
This assumes that the values are filled in with no blanks in-between like
Assume that A2 is the first cell with a numeric value =AVERAGE(OFFSET($A$2,COUNTA($A$2:$A$10000)-1,,-10,)) will average the 10 last values, so for 13 it would be -13 so the best way is probably something like =AVERAGE(OFFSET($A$2,COUNTA($A$2:$A$10000)-1,,-E2,)) where E2 will hold the number of values you want to average regarding the counta part, make sure it is bigger than the table ever will be and make sure all the cells below the last value in the same column are empty - Regards, Peo Sjoblom (No private emails please) "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#3
|
|||
|
|||
Hi!
Assume your values to average are in column A, starting in A1. Enter the interval you want to use in a cell, say, E1. E1 = 10 =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Copy down until you get #DIV/0! errors meaning the data has been exhausted. Depending on the interval size, the last average may not be a full interval. Biff "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#4
|
|||
|
|||
Ooops!
Correction: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Should be: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*E$1,,E$1)) Biff "Biff" wrote in message ... Hi! Assume your values to average are in column A, starting in A1. Enter the interval you want to use in a cell, say, E1. E1 = 10 =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Copy down until you get #DIV/0! errors meaning the data has been exhausted. Depending on the interval size, the last average may not be a full interval. Biff "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#5
|
|||
|
|||
Great help, thanks!
Now, what if I'd like to use some exponential or weighted averaging? That is, in exponential/weighted averaging the most recent value is of great weight than an older value. In the '10-day temperature' example, yesterday's temperature of 90-degrees is better and weighted more than the temperature of 58-degrees of ten days ago. How do I do the exponential/weighted average? Do I have to create my own formula for my 'own' average methodology (non-standard)? Thanks "Biff" wrote: Ooops! Correction: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Should be: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*E$1,,E$1)) Biff "Biff" wrote in message ... Hi! Assume your values to average are in column A, starting in A1. Enter the interval you want to use in a cell, say, E1. E1 = 10 =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Copy down until you get #DIV/0! errors meaning the data has been exhausted. Depending on the interval size, the last average may not be a full interval. Biff "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#6
|
|||
|
|||
Hi!
Do I have to create my own formula for my 'own' average methodology (non-standard)? Well, that would fall into the category of statistics which I really don't know much about! Coming up with a "roll your own" weighting formula and applying it against the data should be easy enough. Can you give an example of what the criteria would be? I understand latest has greater weight, but how do you want to weight that against the oldest? Biff "Tom" wrote in message ... Great help, thanks! Now, what if I'd like to use some exponential or weighted averaging? That is, in exponential/weighted averaging the most recent value is of great weight than an older value. In the '10-day temperature' example, yesterday's temperature of 90-degrees is better and weighted more than the temperature of 58-degrees of ten days ago. How do I do the exponential/weighted average? Do I have to create my own formula for my 'own' average methodology (non-standard)? Thanks "Biff" wrote: Ooops! Correction: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Should be: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*E$1,,E$1)) Biff "Biff" wrote in message ... Hi! Assume your values to average are in column A, starting in A1. Enter the interval you want to use in a cell, say, E1. E1 = 10 =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Copy down until you get #DIV/0! errors meaning the data has been exhausted. Depending on the interval size, the last average may not be a full interval. Biff "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#7
|
|||
|
|||
Biff,
Thanks AGAIN! One easy way of weighting is to approach a 10-interval set of data as follows [each interval is the same difference from the previous or next]: Latest: (1.0) * value(Latest) Latest-1: (1.0-.1) * value(Latest-1) Latest-2: (1.0-.2) * value(Latest-2) ..... Latest-9: (1.0-.9) * value(Latest-9) You can see that the weighting is based off the most recent measurement times the value at the time of THAT measurement [value(Latest-i)]. Hence, any weighting would have to take into account A) the number of measurements in a set of intervals [# of intervals] and B) the values during each interval [value(Latest-i)]. There are a lot of different ways to obtain a weighting factor, (1.0-i) in the example above, so any method of calculating the weighting factor should keep this in mind. That is, you could some up with logarithmic weighting factor which puts more weight on the first few intervals rather than the assigning each weighting factor the same DIFFERENCE from the previous or next weighting factor like the above example. Does this help!?!? TIA "Biff" wrote: Hi! Do I have to create my own formula for my 'own' average methodology (non-standard)? Well, that would fall into the category of statistics which I really don't know much about! Coming up with a "roll your own" weighting formula and applying it against the data should be easy enough. Can you give an example of what the criteria would be? I understand latest has greater weight, but how do you want to weight that against the oldest? Biff "Tom" wrote in message ... Great help, thanks! Now, what if I'd like to use some exponential or weighted averaging? That is, in exponential/weighted averaging the most recent value is of great weight than an older value. In the '10-day temperature' example, yesterday's temperature of 90-degrees is better and weighted more than the temperature of 58-degrees of ten days ago. How do I do the exponential/weighted average? Do I have to create my own formula for my 'own' average methodology (non-standard)? Thanks "Biff" wrote: Ooops! Correction: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Should be: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*E$1,,E$1)) Biff "Biff" wrote in message ... Hi! Assume your values to average are in column A, starting in A1. Enter the interval you want to use in a cell, say, E1. E1 = 10 =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Copy down until you get #DIV/0! errors meaning the data has been exhausted. Depending on the interval size, the last average may not be a full interval. Biff "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#8
|
|||
|
|||
Hi!
Let me see if I can come up with something. Biff "Tom" wrote in message ... Biff, Thanks AGAIN! One easy way of weighting is to approach a 10-interval set of data as follows [each interval is the same difference from the previous or next]: Latest: (1.0) * value(Latest) Latest-1: (1.0-.1) * value(Latest-1) Latest-2: (1.0-.2) * value(Latest-2) .... Latest-9: (1.0-.9) * value(Latest-9) You can see that the weighting is based off the most recent measurement times the value at the time of THAT measurement [value(Latest-i)]. Hence, any weighting would have to take into account A) the number of measurements in a set of intervals [# of intervals] and B) the values during each interval [value(Latest-i)]. There are a lot of different ways to obtain a weighting factor, (1.0-i) in the example above, so any method of calculating the weighting factor should keep this in mind. That is, you could some up with logarithmic weighting factor which puts more weight on the first few intervals rather than the assigning each weighting factor the same DIFFERENCE from the previous or next weighting factor like the above example. Does this help!?!? TIA "Biff" wrote: Hi! Do I have to create my own formula for my 'own' average methodology (non-standard)? Well, that would fall into the category of statistics which I really don't know much about! Coming up with a "roll your own" weighting formula and applying it against the data should be easy enough. Can you give an example of what the criteria would be? I understand latest has greater weight, but how do you want to weight that against the oldest? Biff "Tom" wrote in message ... Great help, thanks! Now, what if I'd like to use some exponential or weighted averaging? That is, in exponential/weighted averaging the most recent value is of great weight than an older value. In the '10-day temperature' example, yesterday's temperature of 90-degrees is better and weighted more than the temperature of 58-degrees of ten days ago. How do I do the exponential/weighted average? Do I have to create my own formula for my 'own' average methodology (non-standard)? Thanks "Biff" wrote: Ooops! Correction: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Should be: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*E$1,,E$1)) Biff "Biff" wrote in message ... Hi! Assume your values to average are in column A, starting in A1. Enter the interval you want to use in a cell, say, E1. E1 = 10 =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Copy down until you get #DIV/0! errors meaning the data has been exhausted. Depending on the interval size, the last average may not be a full interval. Biff "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
#9
|
|||
|
|||
Ok, let's see if I understand:
Here's a one interval (interval = 10) set of values in the range B1:B10. The dates are in column A ascending so B1 is the oldest and B10 is the latest. 66 64 52 55 61 53 49 49 52 55 Weighted as per your example: =B1*(1.0-0.9) =B2*(1.0-0.8) =B3*(1.0-0.7) =B4*(1.0-0.6) =B5*(1.0-0.5) =B6*(1.0-0.4) =B7*(1.0-0.3) =B8*(1.0-0.2) =B9*(1.0-0.1) =B10*1.0 So, what you want is: =AVERAGE { B1*(1.0-0.9) B2*(1.0-0.8) B3*(1.0-0.7) B4*(1.0-0.6) B5*(1.0-0.5) B6*(1.0-0.4) B7*(1.0-0.3) B8*(1.0-0.2) B9*(1.0-0.1) B10*1.0 } If that's correct, entered as an array with the key combo of CTRL,SHIFT,ENTER: =AVERAGE(INDEX(OFFSET(B1,(ROW(1:1)-1)*J1,,J1),,1)*{0.1;0.2;0.3;0.4;0.5;0.6;0.7;0.8;0. 9;1}) Where J1 = interval size = 10 OR, use a defined name for {0.1;0.2;0.3;0.4;0.5;0.6;0.7;0.8;0.9;1}, named Interval: =AVERAGE(INDEX(OFFSET(B1,(ROW(1:1)-1)*J1,,J1),,1)*Interval) I don't see how you could use that weighting method on an interval of 13. On the 11th element you end up with: X*(1.0-1.0) = 0 Let me know if I'm getting this! Biff "Biff" wrote in message ... Hi! Let me see if I can come up with something. Biff "Tom" wrote in message ... Biff, Thanks AGAIN! One easy way of weighting is to approach a 10-interval set of data as follows [each interval is the same difference from the previous or next]: Latest: (1.0) * value(Latest) Latest-1: (1.0-.1) * value(Latest-1) Latest-2: (1.0-.2) * value(Latest-2) .... Latest-9: (1.0-.9) * value(Latest-9) You can see that the weighting is based off the most recent measurement times the value at the time of THAT measurement [value(Latest-i)]. Hence, any weighting would have to take into account A) the number of measurements in a set of intervals [# of intervals] and B) the values during each interval [value(Latest-i)]. There are a lot of different ways to obtain a weighting factor, (1.0-i) in the example above, so any method of calculating the weighting factor should keep this in mind. That is, you could some up with logarithmic weighting factor which puts more weight on the first few intervals rather than the assigning each weighting factor the same DIFFERENCE from the previous or next weighting factor like the above example. Does this help!?!? TIA "Biff" wrote: Hi! Do I have to create my own formula for my 'own' average methodology (non-standard)? Well, that would fall into the category of statistics which I really don't know much about! Coming up with a "roll your own" weighting formula and applying it against the data should be easy enough. Can you give an example of what the criteria would be? I understand latest has greater weight, but how do you want to weight that against the oldest? Biff "Tom" wrote in message ... Great help, thanks! Now, what if I'd like to use some exponential or weighted averaging? That is, in exponential/weighted averaging the most recent value is of great weight than an older value. In the '10-day temperature' example, yesterday's temperature of 90-degrees is better and weighted more than the temperature of 58-degrees of ten days ago. How do I do the exponential/weighted average? Do I have to create my own formula for my 'own' average methodology (non-standard)? Thanks "Biff" wrote: Ooops! Correction: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Should be: =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*E$1,,E$1)) Biff "Biff" wrote in message ... Hi! Assume your values to average are in column A, starting in A1. Enter the interval you want to use in a cell, say, E1. E1 = 10 =AVERAGE(OFFSET(A$1,(ROW(1:1)-1)*10,,E$1)) Copy down until you get #DIV/0! errors meaning the data has been exhausted. Depending on the interval size, the last average may not be a full interval. Biff "Tom" wrote in message ... I have a need to calculate a 10-interval vs x-interval moving average. Without totaling the last '10' measurements and dividing by 10 to get an average and then comparing it to, say, '13' by totaling the last 13 and dividing by 13, how can I make the calculations vary by the '# of intervals'? For example, if I want to average the high temperatures from Jan 1-Dec 31, I have 365 intervals. If I want to compare the 10 day average vs a 13 day average I can establish the formula pretty easy for the SPECIFIC number of days. I want to vary the # days, in this example, so I can see if I can spot trends in the data. So how do I do the GENERAL case of x-intervals? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Are there keyboard shortcuts for moving between sheets in Excel? | Excel Discussion (Misc queries) | |||
Slight problem automating Excel in a service | Setting up and Configuration of Excel | |||
Moving blocks of data in excel | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |