All language subtitles for 08 - Solution Summarize operational data visually

(upbeat music)

- [Instructor] In the previous movie,

I asked you to perform three tasks with the sales data

that is contained in columns A through C.

So we have the day of the month and if we scroll down,

you see that I have 30 days of data and then the number

of customers for a given day and sales for a given day.

So let's start out by calculating a running average,

I'll click in cell D 4, type an equal sign

and the average is the average

of all of the days up to a certain point.

So for example, for day four we would want to find

the average of sales from days one through four,

for day three, one through three.

We do this using a mix of absolute and relative references.

So in D 4, I'll type average

and then I need to define my range of values

and I'll start in cell C 4.

I don't want this cell reference changing,

so I'll press F4 on Windows,

on the Mac it would be command T, then a right parenthesis

and then we'll end this range also at C 4

except I will leave this reference relative

so that when the formula is copied down, it will change.

So as it goes down a row

it will add that cell to its average.

Type a right parenthesis and enter,

and you see that the average is 1359

which is correct because there's only one value.

Now I'll click cell D 4

and then double click the fill handle

at the bottom right corner.

I know I'm in the right place

when my mouse pointer changes to a black crosshair,

double click and scrolling down I see that the average,

the running average that is, changes over time

and at the en, we're at 1319.47.

And after a couple of days, it looks like it's settled down

pretty nicely to about that value.

If you want to see how your sales days are spread out

you can create a histogram which lumps sales data

or different values into bins.

So let's go ahead and do that,

I want to do it on my sales data,

so I will select sales C 4 through C 33.

So I'll click C 4, then press control, shift, down arrow.

And that selects all the way

down to the bottom of the column of data.

And then I will click the insert statistic chart button

in the charts group

and I'll click the first item, which is histogram.

And I will drag that up to the top

and I'll make it a little shorter

so I have room below and wider so all of the numbers appear

and I will edit the chart title so it says Sales,

and let's take a look at our chart.

We have five bins and we can see

that sales in the range of 749 to 1309,

that is $749 to $1,309 are the most common.

And then we had sales between 1309 and 1869

as the next most common, and then smaller

and larger sales days tail off toward the end.

And it appears that we only had one value

between 2429 and 2989, which isn't surprising,

those are very large numbers

especially compared to the average.

Next, we can create an X, Y scatter chart to see

if there's any relationship between the number

of customers and sales for the day.

To do that, I will select sales C 4 and B 4

and then press control, shift, enter.

And then on the insert tab,

I will go to the charts group again,

click insert scatter X, Y or bubble chart,

and then I'll click the first scatter

and drag my chart down.

And I see a very clear relationship between the number

of customers and the amount that was spent in the day.

So I will change the chart title,

Customers and Amount Spent,

just to make sure I know exactly what the chart is showing

and you can see that there is a very close relationship.

So those are three ways to summarize your data

using the techniques that you learned in this chapter.

Calculate a running average, create a histogram,

and then analyze your data using an X, Y scatter chart.