Exercises

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

("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

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

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.

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.

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.

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:

Cthulhu will use milk from goats who meet the following criteria: 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, for extra special Jenna-approved goats: the number of valid records that meet the phlegmatism and kookosity criteria, and they 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.

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.

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.

It's Zane's birthday! His goaty friends are planning a big party, with lots of botanicals. They've gone out over the island, with three little plastic baggies each. They've collected Zane's fave botanicals:

• Wacky weed (WW)
• Crazy cloves (CC)
• Odd oregano (OO)

Write a program to work out how much they've collected, and who has collected the most and least. You can download a starting workbook. It looks like this to start:

Enrique collected 15.8 grams of WW, 26.5 grams of CC, and 18.1 grams of OO. His total pick was the sum of the three.

Compute the totals and averages for all three types. Also work out the goat with the highest and lowest picks. Show their names, and their pick weight.

Also output the record counts shown.

Validate the data before processing it. Each weight should be a number from 0 to 5 kilos. (We use the metric system, because we're civilized.) As usual, if any field in a record is invalid, the entire record is invalid.

Make sure your program will still work as the amount of data changes. Do NOT type the number of rows into your code.

Here's some output.

Upload your solution here, not to Moodle. The usual standards apply.

The goatathalon has three events: running, swimming, and pogoing. The winning goat is the one with the lowest total time.

Download this workbook with the data and an MT output area. It looks like this to start:

You can see the data for each goat: name, and times for running, swimming, and pogoing, in minutes. Each time should be a number that is not negative.

Write a program that computes average times for each event, and the average total, for valid records. It should also show the names and times for the winning goat (the one with the lowest total).

Also show the number of valid and invalid records, and the total number of records.

Here's what I got:

Upload your solution to this site, not to Moodle. The usual coding standards apply.

Help Cthulhu work out which athletic shoes goats like best. Download this starting workbook. Add your own code.

It starts like this:

Each data record has three fields:

• Goat name. Must not be empty. Must not contain just a question mark (?). That's how Cthulhu pollsters show the name is missing.
• Fave brand. Must be one of three: Abibaaas, Remock, or Skreecherz. Other values are invalid. It's OK if the names are upper- or lowercase, or have extra spaces at the beginning and/or end.
• Number of quads. Must be numeric, zero or more.

Output the counts and averages, like this:

Write your code so that it automatically adjusts to the number of rows, as the data changes, without any changes to the code. The usual coding standards apply.

Submit your solution to this website as usual. Do not submit to Moodle.