More 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:

  • Marks outliers in yellow.
  • Computes statistics.

Here’s part of the output:

Results

Round the average to one decimal place.

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 worksheet. If you want, include your name in the file name, e.g., lenny-kravitz-puffin-length.xlsm.

Attachments

puffin-length-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Xoop panel candidates
Elections are coming up on Xoop, a planet about 58 light years from Earth. Pl’inth, a political journalist, wants to form a small panel of average citizens, and get their opinions on the issues. She has a list of volunteers, but most of them are not suitable. Write a VBA program to go through the list, and show potential candidates.

Download this workbook with data on Xoop volunteers. Here’s part of it:

Start

The first column is the volunteer’s name. The second is the length of the volunteer’s primary tongue, in etels (an etel is about a meter). The third column is the number of oths the xoop has. An oth is a combination leg and mouth.

Pl’inth wants to find xoops who’s tongue is at least 1.8 but no more than 2.3 etels long, and has at least 4 but no more than 7 oths. Here’s what part of your worksheet should look like after the code runs.

Results

The candidate xoops are listed, with their tongue and oth data. The statistics are just for the candidates. So 2.13 is the average tongue length for the 11 candidates who meet Pl’inth’s criteria.

Round the averages to two decimal places.

Write your program so that it adjusts if the number of rows changes. If some volunteers drop out, or others are added, your program should analyze the new data, just by clicking the Run button.

Assume that all of the data is valid and accurate.

Normal coding standards apply.

Upload your worksheet. If you want, include your name in the file name, e.g., lenny-kravitz-xoops.xlsm.

Attachments

xoops_start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Selfie stick insects stats
You start a company called Autonomous Selfie Sticks that makes a new kind of selfie stick. They’re surgically enhancing praying mantises, AKA stick insects.

Stick insect

Photo by Bernard Dupont

Your company:

  • Inserts computer chips into the insects’ brains, giving them semi-sentience.
  • Mounts tiny digit cameras on their heads.

An autonomous selfie stick rides around on its owner’s shoulder, watching the owner all the time. When the embedded computer detects a situation with a high photobility index, the insect automatically extends itself and takes a photo. Photobility is the attractiveness of a photo on various dimensions, like cuteness, and clarity. Autonomous selfie sticks get the very best photos of customers, as they go about their lives.

You have been in business for a few years, and have four models: the original GK-100 and JM-555, and the more recent GK-200 and JM-556. Customers who upgraded from the GK-100 to the GK-200 are reporting that some of the photos don’t look as good as they did before. You suspect that there may be a problem with the GK-200’s computer, but you aren’t sure.

Each time an autonomous selfie stick takes a photo, it uploads the image to one of your servers, along with metadata: the location where the photo was taken, and its photobility. Location is reported as latitude and longitude.

You decide to write an Excel VBA program to compute statistics on the photobility of images from GK-100s and GK-200s. You download metadata about 150 recent photos, and put it in a worksheet. It starts like this:

Data

Download the workbook. Add code to:

  • Compute the missing statistics.
  • Color code the GK-100s and GK-200s in the input data, like this:

Color coded

The usual coding standards apply.

Submit your workbook.

Attachments

selfie_sticks_start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Exercises and grades
Download this worksheet. It looks like this:

Start

The data is for a course like the one you’re taking now. It shows the number of exercises each student completed, and their total scores on exams.

Some of the data is missing. The cells are empty. Other than that, you can assume all of the data is valid.

Write a program that shows the mean exam scores for people who did 30 or more exercises, and the mean exam scores for people who did fewer than 30 exercises. Include counts are well. Highlight lines that have data missing. Don’t include those lines in the means or counts.

Make sure that the program would still work if the amount of data changed. That is, don’t assume that the amount of data will always be the same.

Here’s some output:

Output

Round the means to one decimal place. (There’s a page for that in Tips and Tricks.)

The usual coding standards apply.

Upload your worksheet.

Attachments

exercises-and-grades-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Dangerous yoga poses
The Society for Extreme Yoga (SEY) has gathered data about extreme yoga poses from around the world. For each pose, they have recorded:

  • The name of the pose, e.g., the rightward hog.
  • The percentage of deaths each time the pose is used, e.g., 0.01%.
  • The percentage of serious injuries each time the pose is used, e.g., 0.01%.

So, if you do the rightward hog, there is one chance in 10,000 that you will die, and one chance in 10,000 that you will be injured (only injuries that require hospitalization count). That makes rightward hog a safe pose. Some are more dangerous. For example, if you do the downward mouse, there is more than one chance in 1,000 that you will die or be seriously injured. Since thousands of extreme yogists do the downward mouse and other dangerous poses every day, there are hundreds of deaths every year.

Write a VBA program that identifies dangerous poses, and shows the most and least dangerous poses, along with other data. The worksheet starts like this:

Start

The pose data starts in row 2, with one line for each pose. Your program can’t rely on a particular number of poses, as new poses are being discovered.

You can download the starting workbook.

After you run your program, you should see something like this:

Done

  • Write the code so that it works no matter how many poses there are. The last pose has a blank row after it.
  • Highlight dangerous poses in yellow. The danger rating of a pose is death% * 2 + injury%. A pose is dangerous if the rating is 0.12% or more. If a pose is not dangerous, then it is considered safe.
  • Show the total number of poses.
  • Show the number of safe poses, the name of the safest pose, and its danger rating. If there is a tie in danger ratings, it doesn’t matter which pose you show.
  • Do the same for dangerous poses.
  • You can assume all of the data is valid.
  • You don’t need to use subs for this program, if you prefer not to.

Upload your completed workbook.

Attachments

yoga-poses-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Clown catapult
You’ve kidnapped clowns to use as ammunition for your clownapult.

Clownapult

Write a program that will scan through clown data, and identify clowns that meet the user’s criteria.

Here’s a sample:

Output

The user types in criteria for weight and shoe size, and clicks Run. The program highlights clowns that meet the criteria by setting the background color of their id and name cells to yellow. Clowns that do not meet the criteria are set to white. Clowns must meet all criteria to be selected.

Assume that the user makes no mistakes when typing in the data. Assume there are no errors in the clown data.

The program also shows some statistics. The overall clown count is the total number of clowns in the data set. You don’t need to round the averages.

Here’s another example:

Output

A clown that was marked in yellow for one run, might be marked in white the next time. So, you need to explicitly set the cell color for each clown to yellow or white.

If no clowns meet the criteria, you won’t able to calculate the average. In that case, show messages instead:

No data

Don’t start from scratch. Download and use the starting workbook that has been created for you.

The usual coding standards apply.

Hint: Declare variables for totaling as Longs, rather than Integers. The starting workbook has those declarations for you.

Upload your solution.

Attachments

clownapult-solution-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)