Discuss the following:
1. Calculate the descriptive statistics for all three data series (in Data tab), using the Descriptive Statistics tool in Excel (Data > Data Anlaysis > Descriptive Statistics)
2. Calculate the mean (=average), standard deviation (=stdev), min, and max for these three series using Excel functions, to compare with the output
3. Calculate the correlation matrix for these three series using the Correlation tool (Data > Data Anlysis > Correlation). Which two series are most highly correlated?
Plotting
1. Plot Microsoft and Apple as a line plot.
2. Add a secondary axis for one of these series, so you can better see the dynamics of each (right-click on data in plot > Format Data Series > Series Options > Secondary Axis).
3. These data are ploted with time moving backward as the X-axis increases. Reorder these data to plot time as an increasing variable. Create a column to rank order these (or number the days). Sort entire data array (highlight data, with headings > Data > Sort > Sort By > Rank) according to Days/Rank so time travels forward in the data. Re-plot the sorted data.
Random Variables
1. Generate 200 Uniform(0,1) random variables (=rand()).
2. Convert these to Uniform(5,15) random variables (multiply by 10, add 5).
3. Build a frequency table for the U(5,15) random variables. Use Bin increments running from 5 to 15, with unit spacing (i.e. Bins 5, 6, 7, ..., 15). Use the frequency function (an array funciton) to count the number of random variables in each bin. Highlight the entire frequency array you wan to fill in, press =, type frequency, and fill in the function. When it's filled in, press Ctrl + Shift + Enter to populate the entire array.
4. Find the total number of observations (it should be 200).
5. Calculate the probability for each bin (Frequency/Total), and plot this using a bar graph.
6. Calculate the CDF (cumulative distribution function) by accumulating probabiilty from the smallest bin to the largest. Plot the CDF using a bar graph.
Random Variables
1. Repeat all the steps from Random Variables and Histograms (previous tab), but this time for a Normal(50, 10) random variable. Choose appropriate bin increments to display the distribution.
To generate a Normal(mu,sigma) random variable, use
=mu + normsinv(rand())*sigma
Attachment:- Plotting.rar