Exercises

Exercise

Puffin length

Puffin Download a workbook with data on the length of puffins. It looks like this:

Start

("Total obs" means "Total observations.")

Most puffins are between 24 cm and 34 cm in length. Puffins smaller than 24 cm or larger than 34 cm are outliers, possibly mutant puffins with superpowers. For example, the infamous super-villain The Dart was a short puffin. She sank 18 ships in the Atlantic before being taken out by Seal Team 3.

Seal Team 3

Seal Team 3

Write a VBA program that computes statistics. Here's the output:

Results

Write your program so that it adjusts if the number of rows changes. So your code should work if there are 28 observations, 59 observations, or 1,322 observations.

Assume that all of the data is valid, that is, all lengths are numeric, and greater than zero.

Normal coding standards apply.

Upload your workbook.

Exercise

Milk for goat soap

Cthulhu's mother and sister sent it some goat soap, from Australia. It's made with goat milk. The soap, that is, not the continent. The continent is made of rocks, dirt, and beer.

Soap

Cthulhu likes the soap, and wants to make its own. However, it decides only to use milk from phlegmatic, kookie goats. It wants to know how much milk is available.

Cthulhu's minion, Pandush, is sent to gather the necessary data from Cthulhu's goaty friends. Write a program to figure out how much milk is available.

Here's some of the data Pandush gathers.

Sample data

There are four fields for each goat:

  • Name.
  • Milk produced in gallons per day. Should be a number from 1 to 4. Anything else is invalid.
  • Phlegmatism. Should be a number from 1 to 10. Anything else is invalid.
  • Kookosity. Should be a number from 1 to 10. Anything else is invalid.

Unfortunately, Pandush is still on Dr Tooth Loosener's Blue Crystals of Fun. He might have made some mistakes when typing in the data.

Download the start workbook with the data. It looks like this:

Start

Cthulhu will use milk from goats whose phlegmatism and kookosity scores are both seven or greater.

Your program should output:

  • The number of records, valid and invalid.
  • The number of valid records, that is, the records that have valid data, whether or not they meet the phlegmatism and kookosity criteria.
  • The number of records with invalid data.
  • The number of valid records that meet the phlegmatism and kookosity criteria.
  • The total milk from valid records that meet the phlegmatism and kookosity criteria (G14).

Cthulhu also wants one more count: the number of valid records that meet the phlegmatism and kookosity criteria, and are particularly kookie (have kookosity of 9 or more). Why does Cthulhu want to know this? It won't say.

Your validation code should use case-wise deletion. If you don't remember what that is, check the lesson Filtering: invalid data. Or ask Pandush, if he's compos mentis.

Here's the output your code should produce.

Output

Use a Range object, and write the code so that it will work with no changes when new goat data is added. The usual coding standards apply.

Upload your work.

Exercise

Receivables

Cthulhu does business with companies in the Midwest, selling them goating supplies. Most companies pay their bills on time, but some accounts are delinquent. Write an Excel VBA program that analyzes the data.

Cthulhu has given you a starting worksheet, with data on companies with delinquent accounts. Only delinquent accounts are in the file. The data is like this:

Data

The first field is the name of the company. The second is the state the company is in. The third is the balance owed.

There might be errors in the data. Exclude records with errors from the analysis. Here are the rules:

  • Name: cannot be blank.
  • State: must be one of IA, IL, IN, MI, or OH. Allow for case differences, and extra spaces at the start and end. So " iA " is OK.
  • Balance: a number greater than zero.

Your worksheet should start like this:

Start

Here's what the output should be:

Output

The output shows the number of valid and invalid records, the total balances from each state, and the grand total. The totals only include data from valid records.

Cthulhu also wants to know the name of the company with the highest balance, and what that balance is. Why does it want to know? Best not to ask.

Goat

Wouldn't want to be them. Just sayin.

Upload your workbook. The usual standards apply.

Exercise

Your data analysis

Find a data set about something you're interested in. Sports, movies, games, food... whatever you're into. Put the data into a worksheet, and write a program to do some analysis.

  • At least 100 records.
  • At least three fields, at least one of which is numeric.
  • A Run button.
  • Compute totals and counts.
  • Validate the data. Output number of valid and invalid records. If there is no invalid data, add some errors, to show that data validation works.
  • Show stats for the data set overall, and for a subset. Like how the receivables exercise broke data down by state, or the coders vs noncoders in the filtering lesson.
  • Find the minimum and maximum of something.

Upload your workbook. The usual coding standards apply.