2008 for Mac. Version 12.3.0.
I want to calculate the moving average value of the last x data points in a time series (column), without using or discarding all the blank cells in that column.
What I do now in Excel is make a new counter column to keep the original order for later. Then add another column which I populate with a 1 (a dummy variable) if there is a value, or 0 if no value. Then I sort the entire section, moving the 1's to the top, along with all the other data. Next I calculate the avg values (say avg of last 5) in a new column. Then overwrite that with the "paste special" "values" in those cells. Then re-sort by time. Then, for blank cells in the re-sorted column, I use the value from the cell above, if blank.
That is not all that hard, but there must be a command in Excel that uses the prior x number of values, to do the calculation, which ignores the blanks in between. Then I could do all of the above in one step. Do you happen to know such a function or formula?
I want to calculate the moving average value of the last x data points in a time series (column), without using or discarding all the blank cells in that column.
What I do now in Excel is make a new counter column to keep the original order for later. Then add another column which I populate with a 1 (a dummy variable) if there is a value, or 0 if no value. Then I sort the entire section, moving the 1's to the top, along with all the other data. Next I calculate the avg values (say avg of last 5) in a new column. Then overwrite that with the "paste special" "values" in those cells. Then re-sort by time. Then, for blank cells in the re-sorted column, I use the value from the cell above, if blank.
That is not all that hard, but there must be a command in Excel that uses the prior x number of values, to do the calculation, which ignores the blanks in between. Then I could do all of the above in one step. Do you happen to know such a function or formula?
