## Moving Average with Excel and Shell Script

In the example below we show you how to calculate simple moving average using Excel and a shell script. As more data is added to the file, both the shell script and the Excel formula will automatically use the specified number of the latest data points to calculate the moving average. The sample input file (data.txt) contains two tab-separated columns with dates and corresponding values, as shown below.

2011-08-01 12.3 2011-08-02 16.2 2011-08-03 13.5 2011-08-04 17.5 2011-08-05 12.0 2011-08-06 15.1 2011-08-07 17.2 2011-08-08 19.2 2011-08-09 13.3 2011-08-10 13.2 2011-08-11 16.7 2011-08-12 17.4 2011-08-13 11.5 2011-08-14 14.6 2011-08-15 15.6 2011-08-16 16.8

The following shell script will calculate simple moving average for the past seven data points.

i=7 ; sum=0 ; mavg=0 ; tail -$i data.txt | awk '{print $2}' | while read line do sum=$(echo "scale=1;${sum}+${line}"|bc -l) echo $sum > /tmp/sum done mavg=$(echo "scale=1;`cat /tmp/sum`/${i}"|bc -l) echo $mavg rm /tmp/sum

In this script variable “i” represents the number of data points and the “scale” varible represents the number of decimal places.

To make the same calculation in Excel, we need to import the data file into a spreadsheet as a tab-separated file. In our example column “A” will contain the dates and column “B” will contain the corresponding data values, as shown below.

The formula we use to calculate simple moving average for the past seven days goes something like this:

=AVERAGE(OFFSET($B$1,COUNT($B$1:$B$100)-7,0,7,1))

Where “B” is the table column containing data values; “$B$1” is the beginning of the column; $B$100 is an arbitrary end of the column (you can put $B$1000000 and it will still work fine); “7” is the number of data points used to calculate the moving average; and “1” is the step between the data points.

I need to calculate the time constant (or maybe the time decay I’m not sure) and the driving frequency, but all I have is a set of data points of voltage and time for the second when I turned on the emf source and the graph responded with transient behavior.

Thanks.

Thanks, but what is this equation??

I have a set of n points (x,y). I do not have what type of equation they fit into – wheher a parabola etc…

I want to find a curve that passes through all the data points (interpolation?). Kindly suggest the algorithm / sample code for the same.

Why is it necessary to square the deviation of each data point from the regression line to compute the method?

I have some data points on pieces of paper and need to use the distance formula to find the distance between them. But they are not on a plane just plain paper. Is there a way maybe by scanning the image to have excel graph the points on a scatter plot in order to do so. Please explain!!!! Asap!