Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.


Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.


Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.


Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » Commands & Shells

Moving Average with Excel and Shell Script

Submitted by on August 11, 2011 – 12:41 pm 4 Comments

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
   sum=$(echo "scale=1;${sum}+${line}"|bc -l)
   echo $sum > /tmp/sum
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:


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.

Print Friendly, PDF & Email


  • ericmreitz says:

    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, but what is this equation??

  • easton j says:

    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.

  • Sriram R says:

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

  • apleaforbrandon says:

    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!

Leave a Reply

%d bloggers like this: