Welcome to Highline BI348

Class Video Number 15. If you want to download this

workbook, BI348Chapter02start or the finished file, click

on the link below the video. In this video we

have a data set. And we’re actually going

to use this data set for the next couple videos to

create frequency distributions and the appropriate charts. Now in this video we want to

look at a categorical variable, Website, and count

how many transactions for each one of the websites. Now this data set is for the

company Boomerang Incorporated. And this company

sells boomerangs online through four

different websites. Now this is categorical data. So we will simply count using

either the COUNTIFS function or a pivot table. And then we’ll make

a column chart. Now let’s go ahead and click

here and Control down arrow. So this is like

26,000 records here. Control up arrow. Now I want to click

in a single cell. And we’ll do pivot tables

first, put it on a new sheet, and then we’ll see

how to do the COUNTIFS functions on that sheet. Now the keyboard is

Alt, N, V as we’ve seen in the earlier videos. But notice, the default

is New Worksheet. Not only that, but any time

you have a dialogue box, if a button is

highlighted, that means the Enter key will enact it. So it’s really Alt,

N, V, and Enter. It puts it on a new sheet. I’m immediately going to

double click this and call this Cat for categorical, FD

for frequency and distribution, and Column Chart, and Enter. Now here’s our field list. Now I’m going to

actually drag this. I’m going to point to

the top and click– and it’s kind of hard to

drag– and drag it over here. Resize this. So ready? We’re going to drag

Website to rows. And then Website down to Values. And look at that. Now I don’t like Row label. So Design, Report

Layout, Tabular. Click right here. And we’ll type

Frequency and Enter. Now we want to drag

Websites a second time, because what we’d also like

to do is do percent frequency. Here we can clearly

see that Amazon is where we sell most

of our boomerangs, then our own base website,

ColoradoBoomerangs.com, and then some at eBay, and

some at GelBoomerangs.com. So there’s our

frequency, our count. The total, of course, tells

us how many transactions we have in that data set. Hopefully we don’t

have any empty cells. Actually when we

Control down arrow, we would have bumped in

one of those empty cells, if there was an empty cell. But now we want to do percent

frequency, which is just a fancy way of saying, take each

one of these as the numerator and then divide it on top

of the denominator total. That’ll give us the percentage

of the column total. So guess what? Right click. And oh, the ever so

powerful Show Values As, and we want Percent

of Column Total. And just like that we have

Frequency and Percent Frequency and Enter. Now I want to do the same

calculation with formulas. And in general, the pivot table

is much faster and easier. The only time we want to

switch over to formulas is if the data is

going to change it and we want it to instantly

update without having to use right click, Refresh. Now right click, Refresh,

doesn’t seem like much. And it’s not. But sometimes we need everything

to update automatically, so that’s where

formulas come in. Now one thing about

formulas is that you do not have a feature that

will automatically create a unique list. Well, if you don’t already

have the pivot table, here’s a great trick. You can go up to Data

and Advanced Filter. Now Advanced Filter we’re going

to copy to another location. But notice, with all of

our Data Analysis features, we didn’t have a single cell

selected inside our data set. But no problem,

the Collapse button will let us go to

a different sheet. I’m going to click

on Sales Data. Click in the actual

field name, and then do Control Shift down arrow. And what we want is this

awesome little checkbox, Unique Records Notice it

jumps us back to the sheet. I also need to tell it

where I want that to go. And when I click OK. Boom. There it is. A unique list using

Advanced Filter. Now I’m going to

type Frequency, tab, and Percent Frequency, tab. Highlight the two columns. Click and drag. Right click. And I’m going to grab

that Format Painter to copy just the formatting,

and then select those two cells. And boop, highlight

these two cells, add some formatting

for formulas. And now we want to

click in the cell. And we’re going to use COUNTIFS. Equals COUNTIFS. And guess what? The criteria range is

on a different sheet. But no problem. We’re going to do

a sheet reference. Well, if the range is

on a different sheet, how do we get there? We simply click on the sheet. Oh, look at this. I’m way down at the bottom. I’m going to use the

keyboard Control Home just to jump to the top, A1. And then I can get in the top

cell, Control Shift down arrow. And F4 to lock it. Now you could see

the formula– here’s the screen tip right here

formula– evolving up in the formula bar. Now there’s a crazy

thing that happens when I comma and go back to our sheet. It puts in the

actual sheet name, even though I’m on the sheet. And watch this. Even if you were to delete

this, once you go over to a new sheet,

you’re permanently in sheet reference mode. So we don’t really need

that. so I’m simply going to highlight and delete. And so now there’s our formula. Close parentheses,

Control Enter. Double click and send it down. Now we’re in a slightly

different order than over here. The pivot table unique

list alphabetized it. The Advanced Filter

Unique Records Only gives us the first ones

it runs into, but no problem. If I want it the

same, right click. Notice this is the field names

at the top, records and rows. So I can use the Sort feature. Notice I’m using right click. And I’m going to sort A to

Z. And so there we have it. Now we can simply come below,

Alt Equals to add it up. And Enter. Total. Control Enter,

shift arrow, arrow. And I’m going to use Control

B. And then add some formatting for our formulas. And now we can calculate

our percent frequency. Equals relative cell

reference, one to my left. I use the left arrow. Divided by left arrow,

Control down arrow, F4. Control Enter. And click and drag it down. Now I can click below and

Alt equals Control Enter. Now I can highlight and

add a percentage number formatting with two

decimals showing. Now we have our frequency

percent frequency with either a pivot

table or formulas. Now let’s talk about

making a chart. This is categorical data. So we have Insert. And in the Charts group

for categorical data we have three choices,

Pie, Column, or Bar. Now in recent years, the

trends in data analytics is to use column

charts over pie charts. Not only that,

but research shows that humans can

understand differences between column heights better

than they can pie pieces. So I’m going to click on

the column and use 2D. Oh, wait a second. Look at that. It’s thinking I want

the whole table. I’m going to have to highlight. I only want the labels at the

top and the actual categorical data and count. Now I can go up to column

and use 2D, not 3D. 3D tends to be chart junk. Now one thing about long labels

when you have a column chart, is they don’t fit so well. So that’s where the

bar chart comes in. I’m going to go to

Change Chart Type. And then over here click on Bar. And click OK. Much better. Now we can click on the columns. Control 1. And in 2013 and later

we have this task pane. You can adjust the gap width,

but you do not want to do that. Zero gap width is reserved for

continuous quantitative data. But you can change the gap

width a little if you’d like. I’m going to go up to the

Paint Bucket and Fill. You can change it to

whatever you want. I’m going to vary

colors by point. Now I can close the Task pane. And I actually can

come over here. And in the green

plus, I love this. Data labels. It used to be so much

harder, in earlier versions, to add data labels. That’s looking good there. But now we have chart junk. I want to click down on the

horizontal axis and hit Delete. Now I’d like to come up

and click on the chart title and just type

my chart title, Boomerang Inc 2013 Sales

Frequency by Website. Notice when I type, the

chart label didn’t update. But as soon as I

hit Enter, boom. There we go. Now here’s a crazy thing. I want to change the font. And if I Control 1, it’s

going to open this over here. But there’s no font. And there’s no font over here. You actually have to right

click and point to Font or use the keyboard, Control

Shift F, to get to Font. Or actually just go up to

Home and then Font Size. I’m going to bring

it down to 12. Now one last thing. We want to go over to the tab

called Discrete Quantitative data. And here we have a

frequency distribution. This is a discrete

random variable. There’s gaps between

these numbers. This is counting. Zero people came in

in a one-minute period to our fast food restaurant

at a busy lunchtime. one person, two people,

three people, four people. You could see the biggest

frequency– from our sample data– 266 times

four people came in during a one-minute period. Well, how do we visually

articulate this data? Well, this discrete

random variable goes along the horizontal axis. And, of course, the

heights of the column represent the frequency. But notice, there’s

gap widths here to visually indicate

that no numbers can fit between 3 and 4. There’s a gap. Back here on our Cat FD,

the length of these bars articulates the

frequency or the count. And then the labels

are off to the side. So in this video we talked

about building frequency distributions and percent

frequency distributions for categorical data with

formulas and with pivot tables. And then we saw an

appropriate chart. Now in next video we’ll talk

about frequency distributions and the appropriate charts for

continuous quantitative data. All right. We’ll see you next video.

Thanks for the videos and the enthusiasm you put in your work.

How do you do the automatic formating for formulas at 5:07?

Thanks again 🙂

How I can select invoices with foreign currency and converted in US dollars with out affecting de rest of the invoices en US dollars.

I love your videos

Thanks

Mike you are awesome! best ever!