Statistical Analysis with Excel-Chapter5
Before moving forward to learn about a new chart type, it is good to know where it can be used. I hope as Analysts we all are aware of the concept of Linear Regression. In fact, I remember learning this concept first time in high school.
Linear regression is a statistical technique to determine the relationship between one variable x and another variable y. I will write in detail about it in future chapters.
The graph we are learning is a Scatter plot and represents each individual as a point. As we move forward I will take a step further with new datasets. These datasets can also be used in the predictive modeling concept of Machine learning. You must be thinking, why use these advanced datasets now in Excel, when you work in advanced Analytics most of your time is spent in understanding and cleaning the data rather than modeling. So let us become experts in understanding data.
This dataset has a total of 7columns /fields that represent important characteristics that are considered when purchasing a house/pricing the house and 1 one column is the price of the house.
The price of the house can be y variable and all the 7 columns can be x variables or inputs.
I will consider the y variable as price and the x variable as distance to the nearest MRT station then see their linear relationship with the scatter plot.
Download the csv file and save on your desktop.
Data-Get data-From file-From Text/csv-choose file you stored on desktop.
To graph:
Insert-charts-Scatter-(all the columns gets graphed on the plot)-right click the chart-select data-only choose X axis data(distance to the nearset MRT station) and Y axis data (house price of unit area).I choose only 50 records /rows to see clearly.
One of the most important thing to note is that the dataset did not have description of columns nor the units on Kaggle .I would not consider this dataset to do my Analytics .
Still we could do see from the plot that the price decreases with the increase in distance to MRT station.Practically to conclude this we further do Regresion analysis.
Rememebr as Engineers we always have many tools to be confident of our analysis and say how much confident we are about it……
Dont stop here>>>
Types of scatter plots:
Scatter with smooth Lines and markers.
Bubble
3–D bubble
A bubble chart is a way to visualize three dimensions a two dimensional chart.Each data point appears as a circle or bubble.The size of the bubble represents the third dimension and x and y axes represents the other two dimensions.
Keeping the x and y same as before I took third dimension as house age and set the scale to 40 for bubbles.
Let us move forward to understanding one more important plots used by Data enthusiasts working in Financial organizations.
Stock chart:
For many of you this type may be new,so let us understand how it is plotted.
Each data point in the stock chart is a box with line extending upward and downward.The upper and lower bounds of the box represents the opening and closing prices for a particular date.Important to note that sometimes opening price can be higher than closing price so How do you know which is which?If the box is empty the opening price is the lower bound .If the box is filled ,the opening price is the upper bound..
I want to use some historical stock data from
This is the stock data for Starbucks.Download the data as csv file and save on desktop.Use this data to plot this graph.I downloaded the data from 15 Feb 2022,14 March 2022.
After downloading and saving you can either copy the data in the sheet you are doing analysis /Data-Get data-from file-from text/csv-choose file .
Insert-charts-Recommended charts-All charts-Stock.
The data should be organized before choosing the chart as the stock chart types are based on stock data.
Stock data has Opening price ,Closing price,Low price,High price and Volume.
Stock chart types:
High-Low-Close
Open-High -low-Close
Volume-High-Low-Close
Volume-Open-High-Low-Close
I arranged the columnsin the respective order of Volume-Open-High_Low-Close.
You can arrange the columns differently and try the other types.
