A Dashboard on Temperature and Rainfall

In this post, I have tried to make an excel based dashboard taking data from past questions asked in Finals of 2017 Modeloff, Section 4 – Data Diving. The question gave temperature and rainfall data of 18 states of the USA for the year 2010. The task was to bring the data to life, show pattern and information hiding in it.

A map was also given and asked to use it optionally or we could use Excel’s newer map tools. However, I have not used the map or any other map tools for the dashboard.

The time given in test was 60 min, but I have taken way more time making it.

What interested me to make a dashboard on data was because a lot of Bigdata analysis is done on temperature and rainfall. I thought it would a learning for me as well to make such a dashboard in excel.

The dashboard gives a standalone and relative position of the city selected in terms of highest/lowest/average temperature and rainfall.

 

2018-06-21_1037

There are two drop downs, one for a city other for a month. The three tables give yearly, monthly, and overall data. The last table giving overall data also highlights selected city and maximum/minimum data.

2018-06-21_1035_001

Two line and bar combination charts are given which talks about the temperature and rainfall for the year and month in detail. With chart at the top, one can find hottest months in the year and most/least rainy months. The chart at the bottom can help you to analyze the specific month.

2018-06-21_1037_001

Two scatter plots are also given to put the data of city for the month you choose in perspective. Here you can find cities which more or less hotter/rainy than the city and month of your choice. Please take care of the axis name while you read the charts. The red dot is your city.

2018-06-21_1038

link to Dashboard

Hope you find the the dashboard useful. Any comments on further improvements are welcome.

 

 

Advertisements

Comparing two sets of values – Graphically

Given with two sets of data like below, and asked to compare them graphically, what charts will come to your mind.

2018-04-19_1014_1

2018-04-19_1015_2

2018-04-19_1015_3

Today, I will show you to compare two sets of values using scatter plot chart. something like this

2018-04-19_1017_4

the data points above the (x=y) slanted dotted line show increase in value vs first value and vice-versa for points below the line. Nifty right! It visually shows at once the points have higher values than previous ones.

Just select the data >> goto insert chart >> select scatter plot chart, all your data points are plotted. Now for an x=y slanted dotted line, the plot below as another series

0                0          or lower than your lowest data point

100            100     or higher than your highest data point

select the chart first, goto chart tools >> design >> select data

2018-04-19_1031_5

like this

2018-04-19_1034_6

select the red dots, right click and add the trendline.

format the dots to become invisible, format the line to a different color and dotted.

format the x and y axis to numbers hide the highest number of slanted (x=y) line.

In my case, I have axis formatted from 10 to 90.

2018-04-19_1017_4

Bingo!!!

Make chart say more with XY chart labeler add-in and other formulas, look like below

2018-04-19_1223_7

 

Data Analysis by Date

Recently, I was given sales data to be analyzed by date. It was suspected that more sales is done towards the end of month to meet the month end target. Essentially they wanted to know the sales pattern in months across the years to be divided between dates 1-20, last 3 days and remaining days. Different ending date of months made the problem difficult.

The function EOMONTH was there for rescue. It returns the last day of the month that is the indicated number of months before or after start date. It is extensively used in financial world.

I have created dummy data to show the analysis. First I created helper columns to breakdown the problem. Columns were created for first date of the month, 20th date, 3 days before last date and last date.

 

2017-06-08_1536

By giving -1 as months after start date and then adding 1 to it gives me first date of the month. The last date of the month was EOMONTH function with 0 month after start date. Finding 20th date of the month and 3 days before the last date is matter of simple adding and subtracting number.

2017-06-08_1532

After making helper columns using simple IF function did the job for me. Then I collated the functions in helper column to make a single cell formula. It did the job for me. Later I used pivot table for further analysis.

Thanks!