Excel LARGE and FLOOR Functions for Shark Week

It’s Shark Week on the Discovery Channel, so here are a couple of handy Excel functions you can use in case of a shark attack. Of course, if you stay home and watch television, you should be safe. The infamous land shark rarely attacks.

That Shark is Large

Sharks are pretty big, with the 11 different shark types in this list ranging from 2 to 5 metres long.

Suddenly, you’re being attacked by the second longest type of shark. How long is it?

To find out, you can use the Excel LARGE function. The LARGE function takes two arguments:

• the array or range of numbers
• the number’s position in that range, with 1 as the Largest number

LARGE Function Example

In this example, the range of cells is B2:B12, and we want to find the 2nd-largest number, so the second argument is 2.

=LARGE(B2:B12,2)

To make the formula more flexible, if you type the position number in cell D1, you can refer to that cell in the LARGE function, as the second argument.

=LARGE(B2:B12,D1)

Sharks Eat Off the Floor

Yes, some sharks eat fish, and other snacks, that they find on the ocean floor. Unfortunately, a few sharks have also dined on people, including about 1000 in the USA, over the past 300 years.

From a list of shark attacks per state, you could use the FLOOR function to round down the average number of attacks, to a specific multiple.

For example, you could round the average attacks (in cell E4) to a multiple of 10, with this formula:

=FLOOR(E4,10)

So, if you’re going to be attacked by a shark, you should make sure it happens in Georgia, where your odds of survival look pretty good!

You’re Gonna Need a Bigger Boat

And what would Shark Week be without a clip from the ultimate shark movie, Jaws.

If you saw Jaws in the 70s, you probably don’t need me to warn you — don’t go in the water! If you do go, take a really big boat.

_________