Most times we want to go beyond just static data/plot to something more visual/dynamic in excel.
This Class covers the basics of data/plot animation in Ms Excel. After this exercise u should b able to apply knowledge gain in developing more robust visualization.

Audience: Data Analyst, All Engineering and Related Fields, Financial Analyst, Programmers etc
Prerequisite: Basic Excel
Tools: Ms Excel 2010 (Other versions work as well)

EXERCISE 1
Consider a simple function
t_n+1=cos(t_n)*sin(t_n)
to be animated on a chart for range of value for t=0 to 100

SOLUTION
Excel has three Calculation Options namely
-Automatic (Default, it allows excel to perform calculation each time a data is refreshed or changed)
-Automatic except for data tables (Allow automatic except for Data Tables)
-Manual (Data are not calculated by default. U have to use the F9 key for calculations to be perform in ur sheet)

For this class we ll be using the automatic but with some options change.

Click on File, Options, Formula
Under calculation option make sure automatic is selected, then make sure the iteration calculation is checked.
Set the following: Max Iteration: 1, Max Change: 0.0001. This allows us to iterate data on the sheet.

Good next we set up our data.

On sheet1, enter t, cos(t)sin(t) in cell A1,B1 respectively. These are just headers for the columns A and B that will hold our data.
Now we need to initialize t using a circular reference. A circular reference allows a cell to reference itself during calculation. Ds trick excel into iteration of data.

Select cell A2 and type
=IF(A2<100,A2+1,0),
press enter. U will hav a default value of 1
D statement above tells excel to check A2 value, if less than 100, the value should be increment by 1 else set A2 to 0. Not dt if u keep pressing F9 (Calculate) d value in A2 increases till 100, then reset to 0 again.

Next click on A3 and type
=A2+1-100 and enter
Ds serves as a buffer to ur data, since we dont want our data to exceed 100. Its the handler dt maintain other values within d desired range.

U should hav entered ds in ur work now


And hav ds result


Next we need to populate other data point.
Select cell A4 and type
=A3+1
press enter and select d cell again, then drag the copy handle (d black dot at d lower right edge of d selected cell). Drag it down till u reach say lik no 102 then drop. This will populate the t data wit and increment/decrements of 1 upto d last value.

Select cell B2 and type
=cos(A2)*sin(A2)
press enter, select it again and double click on the copy handle (d black dot at d lower right edge of d selected cell) to fill other corresponding data. (U can use drag too as done earlier).

Ur work should look like ds now (Dont worry if u hav different data for now)



Now hold down the F9 key and see the data iterates.

Next we need to plot our data on chart

Select all ur data from A1:B102 (Trust u kno how to do such selection)
Next Click on Insert, under Chart select Scatter and pick the first one.

A scatter plot of ur data will be shown on a Chart. U can resize ur Chart so to see ur plot better. U should hav ds



Now hold down F9 key and see ur data and plot animate like running a dynamic simulation.
Dts allow u to see flow and movement of data point over time.

U can set ur axis using Format Axis to fit well (x=0 to 100, y = -1 to 1) is recommended.



QED

PRACTICE WORK

A current analysis carried out to model the relationship between the fall in oil price and decrease in oil production in the Country shows that the Oil price fall can be approximated with a periodic function t = sin(t)+sin(sqrt(2)*t) and the Oil drop was approximated using the fourier-like function as
t=3*sin(t)/t where t is within the range 0 to 100.

Set up the data as done in the exercise and generate an animated simulation BAR CHART PLOTS of the oil price movement and rise/oil drop over t.

SOLUTION TO PROBLEM BELOW



THE EXCEL FILE I CREATED FOR DS TUTORIAL IS ATTACHED BELOW FOR YOUR PRACTICE.

HOPE YOU GAIN SOMETHING, SO GO AND APPLY IT


Pan African Institute
www.pan-african.com