Tip:
Highlight text to annotate it
X
In this lecture we'll take a look at how to use Excel to visualize our data so that
our data could be easily and stood.
Let's take a look at
these six columns of numerical data I can tell you that this data
is about stuff the height of a thousand
randomly selected adults organized into
ranges according to a
lower and a upper value so that
range is generated according to the lower and upper value
and then the middle point of that range
is used as a label so that as we can use the label for 'politing'
later on and then we cut the number of
adults from the a thousand randomly selected
people, we cut the number of them
if their heights is within a particular
range. And finally we normalize the data
so that these experiments can be applied to a more
general group of public. No matter how hard you staring at the six columns
of numerical data it's pretty hard for anyone to really
find out any useful information
out of it, however if you're turning this into a
graph seems to become immediately
more obvious. For example we can find out
roughly most people are within that hight
range so somewhere around 165
and 197,
so you can do rough
guesses and you could also plot this into scatter plots
and pie chart and
and some fancy other kinds of plots.
So, what we are trying to do here trying to introduce you
to charts. So charts together is
what we might call graphs and later you see
Mat Lab produce figures
and 'mathematical' use plotting
as well so all these are presenting
our data from a borrowing numerical format
transforming them into a graphical representation.
So the charts could summarize
numerical data, often where you have
a large amount of data it's pretty hard to make sense out of it.
That's why we need numerical data
gets transformed into graphical representation
for people to digest.
So spreadsheets such as excel
allows us to actually carry out
this kind of transformation and will look at
how to use Excel to do that in a minute through some simple t?????
and the chance could be embedded
in the same spreadsheets like what we've seen before
or reside out a separate sheet
all on its own, and you could have different types of charts like
the one that we've seen, we had a histogram
we could also have pie charts and
bar charts and even 3D
graphs. So in order to
examine in this unit will have ways of producing
charts and graphs and figures. Let's take a look at this
simple equation hopefully this is familiar to
everyone, I will try to copy and paste that
into your empty spreadsheets.
So the formula
here or the equation here requires
some constants and some
independent variables so the constants
is u & a so u is
the initial velocity of any
objects and then the other one
is the acceleration and the independent
varible is the 'type' T, so essentially we are trying to workout
the relationship between displacements
with regards to to time, ok imagine that we have a superfrog
so we have a frog,
sorry not very good at
drawing, but we have a super frog jumping
or leaping with a vertical velocity.
So imagine that we could give a random number
so the initial velocity is four
metres per second. Let's move on to
the excel spreadsheets.
So open up the Excel spreadsheet.
Before we do that let's copy
that formula across so that we know what we are doing.
Alright so we'll copy it somewhere
on this spreadsheet
and then we save
some spots to add to our 'constant'
to first have to label them so that we know
what we are doing so first one would
be the initial velocity
*Types it in* So as you can see that this is not
wide enough so we can double click on that
so that we can have um...
a width feet exactly
what we need so we have initial velocity
and that initial velocity for the time being make it 4
then at the same time we also want to
put in some
unit, so a metre per second
and then second constant would be the acceleration
and that acceleration for the time being we make it the
gravitational acceleration
9.8 and then the unit
will be metre
per square second.
Ok, so we got our initial
constant setting up and then we can
out a series of
time and then the
height or the displacement.
So let's make it height.
Let's start time from 0
and then with a ???? of
one second.
Remember what I said before that you can
highlight. You can select
tool cells, sorry I don't have a...
You can select this too (I don't have a proper mouse so that's why it's a bit
lagging). So I have
0 and then 0.1 seconds
excel automatically works out the increment and if I drag this handle down
and then it gives me a series of time.
So let's go all the way unto perhaps
2 seconds.
Ok, so we'll 'fill' series
so that allows me to have a
series of time and then we can enter the formula
to calculate the height so the
displacement is UT
plus half AT square
so U is
the initial velocity and because we want it to be fixed
absolutely to that initial velocity
that we need to put a dollar sign before the roll label and dollars sign before the column
label and the shop cart in the windows system it's just 'width'
a 4, so if you press the function key
'at 4' you fully automatically get a absolute
reference. So
T is the time, sorry
so, I need to
B1, and then I press F4
to turn it into an absolute reference
and then we times T, and then we
plus
.5 (half) times...
times 88 is
the acceleration, again we want it to be absolute.
4
then times T
raised by the power of two.
So we got the formula in
and we took care of using actual reference
so that when we copy it down or across it always
referring to the initial velocity and the acceleration constants
no matter where you are copying this formula. Ok so,
easy way of copying the formula when you already have the first column
setting up is turn this 'column'
Double-click on the bottom right corner
and you copy the entire formula all the way across
to where
your first column ends.
Ok, so now we have the height
and also the time that we could select them
so that both of them to draw some
interesting graph.
So...
As you can see me that this how something
not exactly correct because
our height fills up
but our acceleration is positive so the weight
should the pointing down so that's why we need...
we need minus sign somewhere
so this would be -9.8
so this superfrog can't be leaping higher and higher
that's against physics so
exchange this to the correct minus -9.8
and you can see the superfrog...
somewhere changed to negative
values and that's again not very correct but
let's plot it first and then
we'll try to you improve this formula
a little bit so
thats insert -> chart,
so what do we want? We want a scattered plot.
Ok, so you can see through this picture
or through this chart
that our superfrog actually want
and a 'grant' that's not correct
really so that's why we all have to be change only initial formula
a little bit.
Ok, so the actual height we want
is something that doesn't go through...
goes through the ground, so if we do
each statement, so equal to
if
*there's something wrong with my keyboard I can't really... get the
shift key working properly* so if
a test, so if that is
less than zero
and then
we want zero to be displayed
so that don't go underground
otherwise we keep the same value.
Ok, so again we copy this
across and that if you're holding on to this
that we know that we are using this
first purple boxed
column and also the blue boxed column
so if we move this, if we select these and move these
across select these 'carefully' move these across then
we manage to get our figure
changed to another column.
Once we've created the chart, now we
should really add more information to it.
This first thing you might notice is that we don't really have a very
meaningful series legend so we need to change the
legend. The way to change it there are multiple ways of
changing it and the easiest way to change is to select the curve
and you can see there's a function display its
under the formula bar. So the function
now takes in four
arguments and the first argument is empty so we could actually
try out and then put in some information in there.
So let's put in height.
I remember that the first argument should be a
string and in Excel and we use double ???ts
to indicate a string, so once we put in this
the height as a string as the first argument and we hit return you can see
height is used as the legend
as well as the title. the other thing
that we should take note on is the chart
tools. Within the chart tools you can see that
we have a lot of options we can
switch, draw a column and select data, we could change the
chart layouts and we could even change the colour of our curves.
So lets change the colour, that's not really
essential, what's more essential is with any chart we should label
our axis, we should give a title to the x-axis and also the title to y-axis.
Double click on that, single click will bring up the
formating box.
I'm double clicking into it so I can change the name
or the title of the axis, so the title axis is
time 'for' x-axis and then we're changing
double click into the y-axis
and then we give it a title "Height".
Ok once we've done that you might think okay
we should really have a more meaningful
title for the entire chart. So if we just put in
height vs time and that's really not giving us
anymore information but if we put in something a little
bit imaginative for example
descriptive as well "A superfrog
leaping vertically
at an
initial velocity
of 4m/s".
Ok so this is giving more information
to the audience what this graph is actually about so this
makes a much more informative
graph.
Ok, so adding to that
if you want to add more
????? series so sometimes you want to plot
2 curves of the same graph,
what do we do? We can go, "Select data".
So as you can see that the first data is already
the first series of data
which is the height
with no going throgh the ground
already is plotted, so if you want to add a new series we
could click on "add" and then we provide a name so we could provide
this name and then we select the x-axis
(the x axis basically the same its the
time), and then the y
values is the middle column.
Ok so we have 2 'has/halves?'
and they're not very descriptive that we can change it later
but you can see from the graph we already have
two curves and two lines inside the graph
and we had the proper legend so we could change these
to something else
not so correct.
So that would change our legend
automatically as well.
Ok, it's too 'risky' to example with a ???? ?? ??? how to
create a chart and how to modify your chart so that your chart can give
out more information including changing
the x-axis title and the y-axis title
as well as your legend and how to add an
extra series of data
inside your
chart. So
what I'm trying to show you now is that you can
do more modifications that won't really affect your charts
either so if I select this and then
I try to insert... I
try to insert a new column.
So lets insert a new column by shifting all
the cells to the right and what I'm trying to do is trying to
put a horizontal
distance inside this column so imagine that our superfrog
jumping or leaping with
the angle so
it has a horizontal velocity
as well so we say x
velocity and that this
velocity has intital
value. Lets make it ?????
and metre per second
the unit. Ok, so
we can enter a formula in there so that
formula would be the initial velocity times
the time. So we have to make this
absolute
and then then we times the time which
is at a 7, okay? So now we can
select and then double click
to copy the formula down. Right so,
what I'm trying to show you is quite easy without affecting your
your existing graph, it's quite easy to modify your data
'in the' cell and you can then makes your
spreadsheets much more meaningful
and also colourful as well,
we can highlight this constant area
with a different background colour maybe
just so that people know that this is
a area that they shouldn't really touch
and we also can change the
title's of your headings with
a different background colour if you want to. You can also do
all kinds interesting formating
*dual audio track*
*dual audio* histogram from the data that we looked at at the
beginning of this lecture. This
histogram is very good at displaying frequencies and
comparing frequencies of categorical data.
So by categorical ?????? they're
actually called cardinal
values in this slide so
categorical data that can be
classified and put into groups or categories.
so in this case we have the height
with each range is a category so we have a lot of categories
for this height data of adults,
and we can compare them by
plotting dif- several series...
several series all at once along side
each other. And
the x-axis is often non-continuous
and the term that we often use in computer science is the
the x-axis needs to be discrete
because they're representing different categories.
So the vertical axis represents the frequency
and then the x-axis represents the
categories. And then we also have
line graphs. Line graphs are very useful we try to
display data that changes in time.
All changes in continuous domain, so the x-axis
in this case is a continuous
line and then the y-axis
display the changes of value so certain things
in this example its the average temperature.
So when we try to display
data that's representing trends or
changes in time, and some
modelling data that has continuous
independent variables then
line graphs makes more sense. And we also have
pie charts that you might have seen in marketing
and in commerce kind of presentation
a lot. So the pie chart is best at displaying
relationship between parts
and whole, so if you have something that's
representing a percentage of something
then the chart, the pie chart
makes more sense. So, in this case we
categorized our whole
sample population into
tiny and short probably 'fat'
and freq, this 4 categories.
so again it's useful for displaying
categorical data, so we display them into 4 categories and
and then we work out the percentage
of each category and
gets displayed that, in a pie chart.
And then we also
sometimes want to find out relationships
or correlations between two
dimensional of data so when we
plot the data we'll want to see whether this
relationship between the project marks
the final exam marks so in this case
we actually used scatter plots and then we
trying to find out the trends by feeding a
line to the scatter plots so
this is very useful when you try to you find out current relations between
two variables. So one variable is
the x-axis and the other variable is the y-axis.
We have 3-demensional
or 3 variables, you have 3-dimensional data
then the bubble plot
makes more sense so the
size bubble indicating the third dimension.
So we might have some bubble plots
previously in the very first
example, and then you also have these fancy surface plots
that's 3D and you can make its very colourful and
very appealing but by looking at
the best thing that it can represent is
to visualize the optima.
So the highest and the lowest points
and perhaps also points of stability.
Other than that a surface plot tends to complicate
things and it makes the graph
very hard to digest, so
unless its... there's a genuine 'need' I would suggest you to
steer yourself away from these type of too
complex too fancy plots cause
the main purpose is to get your message across,
you want to use charts effectively
to deliver the meaning of your data.
So really, even though we have other 'radio'...
radar charts and stock charts and you certainly can have a look at them
but don't forget the critical points is
they need to think about when your data are trying to visualise,
so what's the meaning of your data?
And there's a very useful
online article about it
how to choose the best chart for your data.
I just want to have a quick show of that 'article'
A very useful part of this article is
summarized the purpose of presenting data
using charts into four separate
categories of all separate types.
So the first one is that you might be thinking of showing some relationships
and sometimes you might be comparing different sets of data,
different sets of variables and...
and sometimes you might use
a composition to display
relationship between parts & whole, and finally
you might want to show the distribution of some one-dimensional ortwo-dimensional
data sets. So, this makes
you're thinking a lot more clearer because
once you have a data representation task
you might... you can always ask yourself what...
what exactly you want to do whether you want to do a
comparison and whether you want to do some distribution or whether you
want to
show parts and whole relationships.
So, this
diagram gives you a guide of how to
select the correct chart title, the most suitable chart type, it is not
right or wrong selections but is only
the better selections, the more effective
way of communicating so let's
take a look at this particular diagram.
Within this diagram you start from the very middle
point, the very center. So this
center ask yourself "what would you like to show?",
so do you want to show some comparison as we've seen before...
sorry not comparison
our composition. Composition is a part and whole relationships.
So if it's static then
a pie chart will be quite suitable.
If it's changing over time then you will have
some line plot to
show the changing time with respect to the continuous
time domain. If you want to show some distribution
and then you can consider whether you'll have
a single variable or you have 2 or
3 variables, so we have two variables for example a scatter plot would be
quite suitable, we have 3 variables then a surface
3-D plot might be more suitable, and if you
want to find out some correlations
between you're variables then
you could go for scatter plots if you have two variables and you can go
for
bubble chart if you have 3 variables
cause the size of the bubble indicating that the
dimension. And then there's also
the task of comparison so if you want to do
comparison of different variables,
again you can separate things
into whether it's time based
and whether you have
many diff categories or...
or fewer categories, so if you have fewer categories
and also a few items and then
a vertical bar charts would be more suitable.
So I would suggest you to really
take this diagram away
and think about the type of data that you have at hand and then
pick the right type of chart
to present your data