# More exercises

Exercise: Puffin length
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:

• Marks outliers in yellow.
• Computes statistics.

Here's part of the output:

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.

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:

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.

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.

Attachments

xoops_start.xlsm

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

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:

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

The usual coding standards apply.

Attachments

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

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.

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

• 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.

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.

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

Here's a sample:

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:

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:

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.

Attachments

clownapult-solution-start.xlsm

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

Exercise: Squid racing
Download the attached Excel workbook with squid racing data. It looks like this:

Each squid participates in up to three races. If a squid did not participate in a race, the cell is marked N/A. For example, Xavier was in races 1 and 2, but not race 3.

The number of squid is given in cell(3, 2).

Compute the mean and count for each race, omitting cases where squid did not race. Here is output for the given data:

You can assume that the data is valid, e.g., no negative score.

Upload you workbook. The usual coding standards apply.

Attachments

squid-racing-start.xlsm

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

Exercise: Pineapple sweetness
Pineapples have become a major crop in Bulgaria over the last 80 years. The Bulgarian agricultural minister, Mr. Tapak Glupak, thinks that pineapple farming is one of the country's growth industries.

Bulgarian pineapple farmer, 1957

Bulgaria wants to become a member of the International Pineapple Standards, Marketing, Trade, Safety, Inspection and Regulation Commission (IPSMTSIRC), also known as PineComm. PineComm regulations state that pineapples should be between 8% and 11% sugar by weight, although some variance is permitted. Minister Glupak is concerned that Bulgarian pineapples might be too sour or sweet to meet these standards.

Preslav is an analyst for the agriculture ministry. Here's his office in Sofia:

Minister Glupak has asked Preslav to report on the sugar content situation. Preslav has collected pineapples throughout Bulgaria, and had them analyzed by the ministry's science lab. They have given him an Excel worksheet with the results.

Bulgaria's biggest pineapple, 2005

So, the first pineapple Preslav collected, from a farm in Montana, weighed 1,426 grams, of which 119 grams was sugar. The second pineapple was from a farm in Sofia. The province, not the city. It weighed 1,361 grams, of which 132 grams was sugar.

• Calculate the sugar % for each pineapple.
• Highlight sugar s that are outside PineComm's specification, that is, less than 8 or more than 11%.
• Show the total number of pineapples analyzed.
• Show the number of pineapples that are outside PineComm's spec.
• Show the % of pineapples that are outside spec.
• Show the average weight of all pineapples.
• Show the average sugar weight of all pineapples.
• Show the average sugar % of all pineapples.

Here's some sample output:

More instructions:

• Make sure that Preslav could easily add more data, and rerun the analysis, without changing the code.
• Assume that all of the data is correct, that is, there are no input errors.
• So you not have to round your output.
• You do not have to use subs, though you can if you want.

Hint: each time through the loop, your code should read data for one pineapple, process it, and output the pineapple's sugar %.

Attachments

pineapples-start.xlsm

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

Exercise: Dog happiness
Download the starter worksheeet. It has data about dog happiness, collected from happiness-recording collars. For each dog, there's the dog's name, its happiness level, and phone number:

Happiness values should be between 1 to 10. Sometimes the collars malfunction, because of excess drool. The data will always be numeric, but sometimes might be out of range.

Write a program that runs when the user clicks the Run button. It shows the average happiness and dog count, but only for valid data. That is, invalid happiness values are not included in the average and count. Invalid values are highlighted.

The program also shows the happiest and saddest dogs. It shows their name, happiness, and phone number. Again, invalid data is excluded.

Here's the output:

Important! The program should work no matter how many dogs there are. Assume the data is contiguous, that is, if there is a blank row, that's the end of the data.

Attachments

dog-happiness-start.xlsm

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

Ballet is the deadliest of the arts. For audiences. More people die of boredom at ballet than even opera. Though it's close.

Two ballets are particularly deadly: Swoon Lake, and Griselle. In some countries, they are as welcome as sarin gas.

Download a start spreadsheet with data on a few years of performances of these two ballets. Here's what it looks like:

Write a program that works out which ballet is the deadliest, based on average deaths at performances. Here's the output:

There are two parts to the output. First, there's overall data for all ballets. Overall deaths, overall number of performances, and average deaths per performance.

Second, there's data for the deadliest ballet, whichever one that is. There's the name of the ballet, total deaths for that ballet, number of performances, and average deaths per performance for that ballet.

Important! Write the code so that it works no matter how much data there is. 50 records, 103, 212, it will still work.

You can assume that all of the data is valid. Upper/lowercase for ballet names is not an issue, either. Nor is extra whitespace.

Attachments

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

Exercise: Paula's merch sales
Comedic genius Paula Poundstone will do a 20-minute set at your funeral, for a reasonable (though somewhat random) fee. Plus expenses. Like a heavy-lift helicopter for her luggage.

Paula will do a generic set, or, for an extra fee, will personalize her material. For example:

"Bob's widow, Gail, seems distraught. I don't know why. She'll be able to spend more time with Fabio, her personal 'trainer.'" (Air quotes included at no extra charge. Paula is always classy.)

Paula sells merch at every funeral. Tri-poly blend t-shirts, balloon models of the departed… much else.

Paula thinks that merch sales might be higher at personalized funerals, than at generic ones. Her henchman Adam, who's at every funeral, scoffs at the idea. Write a program to figure out who's right.

Add you own code. When the Run button is clicked, you should see this:

Some notes:

• Exclude funerals from Florida (FL) and Texas (TX). Those states are too weird.
• Put a line though the rows for Florida and Texas. Something like: Cells(?,?).Font.Strikethrough = True
• Assume there are no data errors, although…
• Bonnie Burns crinkled some of the data. Just a little. The state codes could be upper- or lowercase, or a mix. There may be extra spaces at the start or end of the codes, too, like "mI ". The same for the personalized column, e.g., " y".
• Round the averages, like this: Str(Round(someVariableHere))

Note: this task has not been authorized by Paula, MaxiFun, or anyone else, although her Christmas puppy likes it. AFAIK, Paula does not do funerals. It's a good idea, though.

Attachments

paulas-merch-start.xlsm

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