Intermediate Excel: 2014

How to compare Histograms in Excel

Recently someone asked me to assist with a histogram chart. I have to admit, I had not done one for a couple of years and worried a bit. After a brief refresher online however, I was ready to help.

A histogram is done when you're trying to find out the distribution of a variable through a population. Online examples abound but the most common one is grades in a population of students. I'll differ from that tradition. My variable is a medical condition across a population of patients of varying ages. High Blood Pressure or HBP.

Doing a histogram in Excel is not hard. It's a bit tricky, but have no fear, Marco is here...(sorry, could not resist, lol).

A histogram requires a few things:
a) The data array (Patient Ages)
b) The groupings or intervals: The lowest age of the group is 36 and the oldest is 88. So we have to find a way to segregate them into several groupings. Later we will see how to do this.
c) The Bins: The highest number for each interval
d) The Frequency: This is where Excel does it job using Array Formulas. The trick to Array Formulas is knowing that you have to hold down 3 keys after entering the formula in the cell. Again, more of that later. Just know that this is very important.

Since you already have the data, you must define the rest. Below I prepared a graphic of the above rules using the example discussed:

Now that we have the outline, let's dig a little deeper into a real example with a second variable: another medical condition: Diabetes.

This will make things a little interesting because by introducing a second variable, we now have 3 groupings.

1) Those patients with only HBP

2) Those with Diabetes and

3) Those with both conditions.

If the patients had none of the conditions, they would not be in our histogram, so that's not an option.

A word about Excel's Frequency formula. The Formula calls for the Data Array and the Bin Array. While the Bin Array does not change for all 3 groupings in our example, the Data Array does change for each.

In other words, the HBP group formula only needs the patients where HBP = Yes AND Diabetes = No. Similarly, the Diabetes group's data array is the group of patients where HBP=No AND Diabetes = Yes.

Another point to highlight is that if you add up all the groupings, they total 197 patients. This is important as you must account for all the data.

And now for a shocker... A true Histogram cannot have gaps between the columns. In other words, what I have here is not a true Histogram because of the gaps. However, the methodology followed here is that of a Histogram.

While this chart is more of a Bar graph, it allows us to compare and contrast various groupings--something that a Histogram cannot do well by its very nature.

I hope this quick guide helps get you going.

Data Analysis: Example 2

Below is a typical set of data you will be working with. I found this set on the Internet but it will serve our purposes just right.

Our objective in this exercise is to:

clean this data to end up with accurate and unique records.

The first step is to get the data into Excel: Go ahead, copy it and paste it into Excel.

On the first pass, right away we notice that while each record has two bits of data, both are in the same cell:

a) A code and
b) A Name

The next set of problems to discover is that it contains typos, duplicates, etc. This is where you come in. Your job, should you accept it, is to clean up this data set to end up with a nice clean list of unique Codes and Names.

Since I've already completed the exercise, I'll give you a few tips:
> The starter set consists of 531 records.
> I ended up with 86 unique records.
Warning: Results may vary by + or - 5; but no more than that.

> I used the following features:
a) Text to Columns
b) Remove Dups
c) Conditional Formating: Highlight Dups
d) Apply Filters: Sort by Name

> I also used the following formulas:
a) Len(cell)
b) countif(cell)
c) text(cell,"000000")

I will post my approach solution a little later. Best of luck. Oh, and if you have any questions, ask.

NAICS Code

221112 - Fossil Fuel Electric Power Generation

211111 - Crude Petroleum and Natural Gas Extraction

22111 - Electric Power Generation

32512 - Industrial Gas Manufacturing

325120 - Industrial Gas Manufacturing

221112 - Fossil Fuel Electric Power Generation

325120 - Industrial Gas Manufacturing

221119 - Other Electric Power Generation

324110 - Petroleum Refineries

3152 - Cut and Sew Apparel Manufacturing

221119 - Other Electric Power Generation

541711 - Research and Development in Biotechnology

221119 - Other Electric Power Generation

312120 - Breweries

928110 - National Security

922140 - Correctional Institutions

221112 - Fossil Fuel Electric Power Generation

211111 - Crude Petroleum and Natural Gas Extraction

562212 - Solid Waste Landfill

22111 - Electric Power Generation

611310 - Colleges, Universities, and Professional Schools

221119 - Other Electric Power Generation

22111 - Electric Power Generation

221119 - Other Electric Power Generation

324110 - Petroleum Refineries

324199 - All Other Petroleum and Coal Products Manufacturing

3254 - Pharmaceutical and Medicine Manufacturing

22111 - Electric Power Generation

2211 - Electric Power Generation, Transmission and Distribution

221119 - Other Electric Power Generation

22111 - Electric Power Generation

311514 - Dry, Condensed, and Evaporated Dairy Product Manufacturing

3115 - Dairy Product Manufacturing

6113 - Colleges, Universities, and Professional Schools

22111 - Electric Power Generation

611310 - Colleges, Universities, and Professional Schools

331111 - Iron and Steel Mills

221112 - Fossil Fuel Electric Power Generation

221119 - Other Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

32731 - Cement Manufacturing

311421 - Fruit and Vegetable Canning

22111 - Electric Power Generation

332112 - Nonferrous Forging

22111 - Electric Power Generation

2123 - Nonmetallic Mineral Mining and Quarrying

32731 - Cement Manufacturing

22132 - Sewage Treatment Facilities

327993 - Mineral Wool Manufacturing

221112 - Fossil Fuel Electric Power Generation

212312 - Crushed and Broken Limestone Mining and Quarrying

522320 - Financial Transactions Processing, Reserve, and Clearinghouse Activities

237130 - Power and Communication Line and Related Structures Construction

324110 - Petroleum Refineries

211111 - Crude Petroleum and Natural Gas Extraction

221310 - Water Supply and Irrigation Systems

562212 - Solid Waste Landfill

22111 - Electric Power Generation

488119 - Other Airport Operations

22132 - Sewage Treatment Facilities

22111 - Electric Power Generation

221320 - Sewage Treatment Facilities

2213 - Water, Sewage and Other Systems

221330 - Steam and Air-Conditioning Supply

22111 - Electric Power Generation

321113 - Sawmills

311421 - Fruit and Vegetable Canning

324110 - Petroleum Refineries

324199 - All Other Petroleum and Coal Products Manufacturing

324110 - Petroleum Refineries

221112 - Fossil Fuel Electric Power Generation

311221 - Wet Corn Milling

221112 - Fossil Fuel Electric Power Generation

221119 - Other Electric Power Generation

311 - Food Manufacturing

221112 - Fossil Fuel Electric Power Generation

22111 - Electric Power Generation

326140 - Polystyrene Foam Product Manufacturing

311421 - Fruit and Vegetable Canning

32419 - Other Petroleum and Coal Products Manufacturing

221119 - Other Electric Power Generation

212399 - All Other Nonmetallic Mineral Mining

2211 - Electric Power Generation, Transmission and Distribution

221112 - Fossil Fuel Electric Power Generation

325199 - All Other Basic Organic Chemical Manufacturing

221119 - Other Electric Power Generation

22111 - Electric Power Generation

2213 - Water, Sewage and Other Systems

221112 - Fossil Fuel Electric Power Generation

327213 - Glass Container Manufacturing

312130 - Wineries

115114 - Postharvest Crop Activities (except Cotton Ginning)

22132 - Sewage Treatment Facilities

32411 - Petroleum Refineries

221112 - Fossil Fuel Electric Power Generation

22111 - Electric Power Generation

5622 - Waste Treatment and Disposal

221119 - Other Electric Power Generation

562 - Waste Management and Remediation Services

221112 - Fossil Fuel Electric Power Generation

211111 - Crude Petroleum and Natural Gas Extraction

324110 - Petroleum Refineries

221320 - Sewage Treatment Facilities

221112 - Fossil Fuel Electric Power Generation

311615 - Poultry Processing

22111 - Electric Power Generation

221320 - Sewage Treatment Facilities

311 - Food Manufacturing

221119 - Other Electric Power Generation

22111 - Electric Power Generation

221119 - Other Electric Power Generation

3254 - Pharmaceutical and Medicine Manufacturing

325188 - All Other Basic Inorganic Chemical Manufacturing

311230 - Breakfast Cereal Manufacturing

221112 - Fossil Fuel Electric Power Generation

327420 - Gypsum Product Manufacturing

221119 - Other Electric Power Generation

336413 - Other Aircraft Parts and Auxiliary Equipment Manufacturing

32213 - Paperboard Mills

22111 - Electric Power Generation

327211 - Flat Glass Manufacturing

221112 - Fossil Fuel Electric Power Generation

221119 - Other Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

311513 - Cheese Manufacturing

221119 - Other Electric Power Generation

321219 - Reconstituted Wood Product Manufacturing

221112 - Fossil Fuel Electric Power Generation

2211 - Electric Power Generation, Transmission and Distribution

221112 - Fossil Fuel Electric Power Generation

22111 - Electric Power Generation

211112 - Natural Gas Liquid Extraction

311 - Food Manufacturing

221112 - Fossil Fuel Electric Power Generation

221320 - Sewage Treatment Facilities

322121 - Paper (except Newsprint) Mills

311421 - Fruit and Vegetable Canning

541711 - Research and Development in Biotechnology

221112 - Fossil Fuel Electric Power Generation

324110 - Petroleum Refineries

22111 - Electric Power Generation

221119 - Other Electric Power Generation

562212 - Solid Waste Landfill

322121 - Paper (except Newsprint) Mills

334413 - Semiconductor and Related Device Manufacturing

221112 - Fossil Fuel Electric Power Generation

5622 - Waste Treatment and Disposal

22132 - Sewage Treatment Facilities

562212 - Solid Waste Landfill

3115 - Dairy Product Manufacturing

327310 - Cement Manufacturing

311513 - Cheese Manufacturing

311 - Food Manufacturing

211111 - Crude Petroleum and Natural Gas Extraction

221112 - Fossil Fuel Electric Power Generation

336411 - Aircraft Manufacturing

336414 - Guided Missile and Space Vehicle Manufacturing

611310 - Colleges, Universities, and Professional Schools

221112 - Fossil Fuel Electric Power Generation

311 - Food Manufacturing

324110 - Petroleum Refineries

221119 - Other Electric Power Generation

21111 - Oil and Gas Extraction

221119 - Other Electric Power Generation

22111 - Electric Power Generation

928110 - National Security

221112 - Fossil Fuel Electric Power Generation

22111 - Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

2211 - Electric Power Generation, Transmission and Distribution

221112 - Fossil Fuel Electric Power Generation

221119 - Other Electric Power Generation

22111 - Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

312120 - Breweries

327310 - Cement Manufacturing

22111 - Electric Power Generation

221119 - Other Electric Power Generation

22111 - Electric Power Generation

221119 - Other Electric Power Generation

22111 - Electric Power Generation

562212 - Solid Waste Landfill

2213 - Water, Sewage and Other Systems

311 - Food Manufacturing

3114 - Fruit and Vegetable Preserving and Specialty Food Manufacturing

2211 - Electric Power Generation, Transmission and Distribution

221112 - Fossil Fuel Electric Power Generation

928110 - National Security

311611 - Animal (except Poultry) Slaughtering

327310 - Cement Manufacturing

928110 - National Security

327420 - Gypsum Product Manufacturing

33611 - Automobile and Light Duty Motor Vehicle Manufacturing

22111 - Electric Power Generation

221119 - Other Electric Power Generation

22111 - Electric Power Generation

336411 - Aircraft Manufacturing

334419 - Other Electronic Component Manufacturing

562212 - Solid Waste Landfill

22111 - Electric Power Generation

221330 - Steam and Air-Conditioning Supply

221112 - Fossil Fuel Electric Power Generation

311 - Food Manufacturing

311421 - Fruit and Vegetable Canning

311423 - Dried and Dehydrated Food Manufacturing

22111 - Electric Power Generation

3221 - Pulp, Paper, and Paperboard Mills

921190 - Other General Government Support

221320 - Sewage Treatment Facilities

562212 - Solid Waste Landfill

221119 - Other Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

22111 - Electric Power Generation

221119 - Other Electric Power Generation

562212 - Solid Waste Landfill

327213 - Glass Container Manufacturing

562212 - Solid Waste Landfill

221320 - Sewage Treatment Facilities

211111 - Crude Petroleum and Natural Gas Extraction

311421 - Fruit and Vegetable Canning

221119 - Other Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

31142 - Fruit and Vegetable Canning, Pickling, and Drying

32411 - Petroleum Refineries

22111 - Electric Power Generation

221119 - Other Electric Power Generation

2212 - Natural Gas Distribution

221112 - Fossil Fuel Electric Power Generation

2212 - Natural Gas Distribution

221112 - Fossil Fuel Electric Power Generation

2212 - Natural Gas Distribution

327211 - Flat Glass Manufacturing

922140 - Correctional Institutions

325193 - Ethyl Alcohol Manufacturing

211111 - Crude Petroleum and Natural Gas Extraction

211112 - Natural Gas Liquid Extraction

327211 - Flat Glass Manufacturing

32512 - Industrial Gas Manufacturing

541712 - Research and Development in the Physical, Engineering, and Life Sciences (except Biotechnology)

562212 - Solid Waste Landfill

221112 - Fossil Fuel Electric Power Generation

221119 - Other Electric Power Generation

325188 - All Other Basic Inorganic Chemical Manufacturing

221119 - Other Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

221119 - Other Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

22111 - Electric Power Generation

221112 - Fossil Fuel Electric Power Generation

321212 - Softwood Veneer and Plywood Manufacturing

2211 - Electric Power Generation, Transmission and Distribution

221112 - Fossil Fuel Electric Power Generation

611210 - Junior Colleges

327213 - Glass Container Manufacturing

22111 - Electric Power Generation

562212 - Solid Waste Landfill

221112 - Fossil Fuel Electric Power Generation

32411 - Petroleum Refineries

22132 - Sewage Treatment Facilities

221119 - Other Electric Power Generation

311513 - Cheese Manufacturing

22111 - Electric Power Generation

486210 - Pipeline Transportation of Natural Gas

48621 - Pipeline Transportation of Natural Gas

22111 - Electric Power Generation

2123 - Nonmetallic Mineral Mining and Quarrying

21111 - Oil and Gas Extraction

311423 - Dried and Dehydrated Food Manufacturing

32411 - Petroleum Refineries

321113 - Sawmills

22111 - Electric Power Generation

321 - Wood Product Manufacturing

321113 - Sawmills

22111 - Electric Power Generation

321 - Wood Product Manufacturing

21111 - Oil and Gas Extraction

22111 - Electric Power Generation

92 - Public Administration

333611 - Turbine and Turbine Generator Set Units Manufacturing

562212 - Solid Waste Landfill

22132 - Sewage Treatment Facilities

562213 - Solid Waste Combustors and Incinerators

22111 - Electric Power Generation

2211 - Electric Power Generation, Transmission and Distribution

22111 - Electric Power Generation

31131 - Sugar Manufacturing

22111 - Electric Power Generation

311421 - Fruit and Vegetable Canning

221119 - Other Electric Power Generation

2213 - Water, Sewage and Other Systems

221119 - Other Electric Power Generation

22111 - Electric Power Generation

4244 - Grocery and Related Product Merchant Wholesalers

22111 - Electric Power Generation

331111 - Iron and Steel Mills

3314 - Nonferrous Metal (except Aluminum) Production and Processing

32411 - Petroleum Refineries

325188 - All Other Basic Inorganic Chemical Manufacturing

324110 - Petroleum Refineries

322 - Paper Manufacturing

221119 - Other Electric Power Generation

562212 - Solid Waste Landfill

31142 - Fruit and Vegetable Canning, Pickling, and Drying

221119 - Other Electric Power Generation

211 - Oil and Gas Extraction

221112 - Fossil Fuel Electric Power Generation

32731 - Cement Manufacturing

92811 - National Security

2123 - Nonmetallic Mineral Mining and Quarrying

327420 - Gypsum Product Manufacturing

324110 - Petroleum Refineries

31 - Food, Textile,& Leather Manufacturing

221320 - Sewage Treatment Facilities

511110 - Newspaper Publishers

488190 - Other Support Activities for Air Transportation

331511 - Iron Foundries

611310 - Colleges, Universities, and Professional Schools

6113 - Colleges, Universities, and Professional Schools

611310 - Colleges, Universities, and Professional Schools

6113 - Colleges, Universities, and Professional Schools

331221 - Rolled Steel Shape Manufacturing

32411 - Petroleum Refineries

2211 - Electric Power Generation, Transmission and Distribution

211111 - Crude Petroleum and Natural Gas Extraction

331492 - Secondary Smelting, Refining, and Alloying of Nonferrous Metal (except Copper and Aluminum)

22111 - Electric Power Generation

562212 - Solid Waste Landfill

339999 - All Other Miscellaneous Manufacturing

22111 - Electric Power Generation

2211 - Electric Power Generation, Transmission and Distribution

221112 - Fossil Fuel Electric Power Generation

311 - Food Manufacturing

22111 - Electric Power Generation

Convert Text to Columns

If you've worked with Excel, chances are that you've seen data in pretty bad shape. You know, data all together with missing elements, etc. See graphic below.

Here the data is all in one cell. You can't really work with this data. You need to segregate this data into its separate elements. It looks like there are some numbers, a region, a last name, a product, a quantity, a price and a total.

To solve this problem, we use the cool Text to Columns feature in Excel. The good news is that it's easy to use. The bad news is that most of the time you need to tweak it just a little to complete the transformation.

Here, I created a quick video on how to do just that. Oh and the sample data is here

Excel Tables: What they are and how fix them

The current Excel Tables (Excel 2007-13) used to be called Excel Lists. It is a particular feature in Excel where it turns a set of related data and secures it in a way that you cannot scramble it. I used to scramble data left and right when I worked without this feature. I hated working with data and later realizing I had left out some rows or columns... Well, with the new Excel Tables, that is a thing of the past.

To create one:
1) Select any cell in a range of data
2) Ctrl+ T
3) Click ok to the dialog box that displays and viola you've got yourself an Excel Table.

Microsoft has an excellent write-up, albeit long and wordy, on this new feature here: Microsoft description of Excel Tables
Here's a link on how to create Excel tables

I'll give you my 2-buck chuck view of them:

Pros

Excel tables are great because they

are prepackaged with table headers, filters, banding and
you can easily add totals and other calcs with one click.

Cons:

Changing elements on the table is not as easy as before however.

Adding rows or columns takes some getting used to.
Referencing data in these tables is still challenging to me as it uses different reference notation from what I'm use to. See my earlier post on references here.

See graphic for the new referencing below. See the red #s 1-3. In #1 all I wanted was to reference E5. But Excel gave me that... Look at #2, Excel wrote some horrible referencing, I know it's row 6, but if I didn't select it you would not know... #3 is a little better to understand...

If you see this type of referencing and are lost... don't worry, most people get lost too. But if you see this, you're dealing with an Excel Table. Read on to learn how to fix it.

What is not obvious, and few people know, is that you can change an Excel Table back to a regular range.

Click inside the Excel Table, in any cell
Proceed to Table Tools Design
Click Convert to Range

And for my visual learners:

Hands-on learning versus Self-taught

When learning to teach I came across the various learning modes: Visual, Audio, Kinesthetic and Mixed. All of us use one form or another or all of the forms at one time or another. But there is another aspect of learning that I've come to appreciate.

There are folks who learn by just reading a book. These folks are called auto-didactic or self-taught. They are conceptual folks who can grasp ideas and infer practical uses from one example. But this is hard to do.

For most of us mere mortals, the best way and easiest way to learn is when faced with a real life problem and we find a way to solve it. Once we learn how to solve that problem, we keep going back to the same solution. These folks are the hands-on learners. There is nothing wrong with this mode, hey, whatever works, right?

Well, the challenge for us educators and trainers then is how to teach something that students need to learn . but haven't come across yet. If the best and most accessible way to learn is by way of real-life examples, can I just sit and wait for students to ask questions as they come across problems? I say no. we can't wait until each student gets to that point. With that teaching style, it would take a long time to get everyone on the same page.

Sure, I could just spout out this great knowledge but if no one is listening or applying, what's the use? Remember the question: If a tree falls in the forest, does it make a sound? I spin it and ask: If a teacher teaches but no one pays attention, is there learning?

I've heard similar complaints from students over and over:

"I can't learn something unless I use it."

or the variant:

"Unless I have a problem, I can't learn that concept"

If you're one of these hands-on learners, then by all means find a way to use it. When faced with having to learn something, the onus is on the learner to put the effort to learn. If you can't learn it unless you use it, find a use for what you just learned. This is part of the learning experience... make the effort!

If you're only willing to learn with hands-on problems, then I'd say your progress is going to be very slow; you're coping--which is fine too. Just know that if you don't make the effort, I ain't got time for ya!

Anatomy of a Formula

There are tons of sites explaining how to use a formula. Even Microsoft has them, even though Excel has excellent Help documentation. So why am I adding a drop to the ocean of information? Well, because I want to; because I'd feel amiss that I left a gap in my communication to you.

If you're a complete babe to Excel you don't even know that =A1+A2 is the formula to add the contents of A1 and A2. For the rest of the world, we've known that and this is no news to 99% of the population on the web. But the population drops to about 80 with

=SUM(A1:A5)

Still, the majority of us knows and understands this function. It's easy to infer that it is adding the contents of cells A1 through A5. It's easier than to write A1+A2+A3+A4+A5. What if you want to add a hundred rows? A1:A100 is way much faster.

Well, formulas consist of a Keyword and their arguments enclosed in parenthesis. Again, most know this. In our example above SUM is the keyword and A1:A5 is its argument. The keyword is what to do and the argument specifies with what. It's like saying BuildTable(Wood) or BuildTable(Concrete) or BuildTable(Concrete&Wood) or BuildTable(Metal).

We've established that Excel offers hundreds of native formulas called Functions and it also offers great documentation on how to use them, in case you don't know how. It does this by way of a Function Wizard. You can find the wizard under the Formula tab. Let's take a look, shall we?

By clicking the Fx button, a window launches with a list of the functions available. Here I've selected ABS function. If I forgot how to use it, below it reminds me what it does and its argument. In this case, it just needs 1 value. If you've used the function and can't remember exactly how you used it, this is a great little reminder. If however, you've never used a function, then for that... for that my friend Microsoft went all out.

In more obscure functions with lots of arguments, Excel offers the final and best assist ever. See the Help on this function link at the bottom? Clicking it opens a new window and goes on to describe it, provide examples.In some instances, it even gives you sample data to copy and paste into your own file to test and play around with.

You don't always use the wizard, oh when you do, it usually saves your hide. Be a friend to your Function Wizard, visit him sometimes. He gets lonely in there sometimes.

Nav

How to compare Histograms in Excel

Data Analysis: Example 2

Convert Text to Columns

Excel Tables: What they are and how fix them

Pros

Cons:

Hands-on learning versus Self-taught

Anatomy of a Formula

=SUM(A1:A5)