Chapter 20

Ten Really Cool Functions

IN THIS CHAPTER

Bullet Converting numbers among base systems

Bullet Converting values from one unit of measure to another

Bullet Finding a common divisor and common multiple

Bullet Generating random numbers with a twist

Bullet Converting to Roman numerals

Bullet Getting a fast factorial

Bullet Finding out the percentage of a year

Bullet Testing the data type

Bullet Finding the length

Bullet Converting the case

The hits just keep on coming! Just when you thought you had all the Excel functions down pat, here I go rocking the boat. Add this mix of useful functions to your plate of Excel goodies, and you will be that much more of an Excel master. Be the envy of all the kids on the block!

Work with Hexadecimal, Octal, Decimal, and Binary Numbers

In certain lines of work, it is desirable or even necessary to work in another base system. Designing computer systems is a good example. The computer chips that run our PCs work with a binary system. Circuits are either on or off. This means that there are just two possible states — and they are often expressed as 0 and 1.

In base 2, or binary, all numbers are expressed with the digit 0 or 1. The number 20 as we know it in decimal is 10100 in binary. The number 99 is 1100011. The binary system is based on powers of 2.

In other words, in base 10 you count up through ten digits in one position before moving one position to the left for the next significant digit. And then the first position cycles back to the beginning digit. To make it simple, you count 0 to 9, add a 1 to the next significant digit, and start the first position over at 0. Therefore, 10 comes after 9.

Binary, octal, and hexadecimal each count up to a different digit before incrementing the next significant digit. That’s why when any larger-base number, such as a base 10 number, is converted to binary, there are more actual digit places. Look at what happens to the number 20. In base 10, 20 is represented in 2 digits. In binary, 20 is represented in 5 digits.

Octal, based on powers of 8, counts up to 8 digits — 0 through 7. The digits 8 and 9 are never used in octal. Hexadecimal, based on powers of 16, counts up to 16 digits, but how? What is left after 9? The letters of the alphabet, that’s what!

Hexadecimal uses these digits: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, and F. The letters A through F represent the decimal values 10 through 15, respectively. If you have ever worked on the colors for a website, you may know that FFFFFF is all white. The web server recognizes colors represented in hexadecimal notation and responds appropriately.

The number 200 in decimal notation becomes C8 in hexadecimal notation. The number 99 in decimal notation becomes 63 in hexadecimal notation.

The point of all this is that there is a group of functions to do all these conversions. These functions take into account all combinations of conversion among binary, octal, decimal, and hexadecimal. These functions are shown in the following table.

Function

What It Does

BIN2DEC

Converts binary to decimal

BIN2HEX

Converts binary to hexadecimal

BIN2OCT

Converts binary to octal

DEC2BIN

Converts decimal to binary

DEC2HEX

Converts decimal to hexadecimal

DEC2OCT

Converts decimal to octal

HEX2BIN

Converts hexadecimal to binary

HEX2DEC

Converts hexadecimal to decimal

HEX2OCT

Converts hexadecimal to octal

OCT2BIN

Converts octal to binary

OCT2DEC

Converts octal to decimal

OCT2HEX

Converts octal to hexadecimal

You can find these functions in the Engineering section of the Insert Function dialog box. Click the Insert Function button on the Formulas tab on the Ribbon.

Convert Units of Measurement

CONVERT is a really great function that Excel provides. Not surprisingly, it converts things. More specifically, it converts measurements. The number of measurements it converts is truly impressive. The function converts feet to inches, meters to feet, Fahrenheit to Celsius, pints to liters, horsepower to watts, and much more. In fact, more than a dozen categories contain dozens of units of measure to convert from and to. The major categories follow:

  • Weight and mass
  • Distance
  • Time
  • Pressure
  • Energy
  • Power
  • Temperature
  • Liquid measure

The function takes three arguments: the value, the “from” unit of measure, and the “to” unit of measure. As an example, here is the function syntax for converting 10 gallons to liters: =CONVERT(10,"gal", "l"). By the way, the answer is 37.85.

Consult the Excel Help system for a full list of available conversions.

Find the Greatest Common Divisor and the Least Common Multiple

A greatest common divisor is the largest integer that divides evenly into each number in a set of numbers. In other words, it divides with no remainder. Take the numbers 5, 10, and 100. The greatest common divisor is 5 because each of the numbers divided by 5 returns another integer (no decimal portion).

The GCD function takes up to 255 values as its arguments. Noninteger values are truncated. By its nature, any returned greatest common divisor must equal or be smaller than the lowest argument value. Often, there is no greatest common divisor other than 1 — which all integers share. The syntax of the GCD function follows:

GCD(number1,number2, …)

The least common multiple is an integer that is the lowest multiple common among a group of integers. For example, the least common multiple of 2, 4, and 6 is 12. The least common multiple of 9, 15, and 48 is 720.

The LCM function takes up to 255 values as its arguments. Noninteger values are truncated. The syntax of the LCM multiple function follows:

LCM(number1,number2, …)

Easily Generate a Random Number

The Excel RAND function returns a number between 0 and 1. And that's it. Usually, you have to massage the returned number into something useful. The typical thing to do is multiply it by some number to get it within a range of values, add the lower limit to that, and finally use INT to turn the whole thing into an integer. The days of drudgery are over!

The RANDBETWEEN function returns a random integer between two values. Two arguments are used: the low end of the range and the high end of the range. Just what you need! For example, =RANDBETWEEN(5, 10) returns a whole number between 5 and 10. Always.

Convert to Roman Numerals

C, V, L, I — I get these mixed up. Is C for 100 or 1,000? What is L for? Whew — I’m glad I don’t have to memorize these anymore.

The ROMAN function takes care of it all. Just throw a number in the normal format you are familiar with, and out comes the equivalent Roman numeral. Easy! The syntax is

=ROMAN(number to convert, optional style)

Factor in a Factorial

If you like multiplication, you will love the FACT function. A factorial, simply put, is the product of multiplying sequential integers. In math notation, 6! (notice the exclamation point) is 1 × 2 × 3 × 4 × 5 × 6, which equals 720. Try it on your calculator, or use an Excel sheet, of course.

The FACT function makes the tedious entry go away, which I think you will like. FACT just takes a number — the number of integers to use for the grand product.

Determine Part of a Year with YEARFRAC

If you need to know what percentage of a year a range of dates is, Excel has the perfect function for you! YEARFRAC returns a percentage of a year. You feed the function a start and end date, and an optional basis for how to count dates (such as a 360-day year, a 365-day year, and so on). The number given back from the function is a percentage — a number less than 1, assuming that the range of dates is less than a full year. An exact one-year range returns 1, and a range longer than a year returns a number larger than 1.

Find the Data TYPE

The content in a cell may be text, a number, a logical value, an error, or an array. The TYPE function tells you which type the content is. When you’re looking at a cell, it’s obvious what the type is. However, if your formulas are using cell references, you may wish to put the TYPE function into the formula before attempting a mathematical operation. This ensures that you can have a valid result returned instead of an error. For example, A4 has 25 and A5 has “Apple.” An attempt to add these results in an error. Instead put the TYPE function into the formula to determine if the calculation should take place. The formula would look like this:

=IF(TYPE(A4)=1&TYPE(A5)=1,A4+A5,"Unable to calculate")

The result in this case is Unable to calculate because you cannot add a number with text.

The TYPE function returns five possible values:

  • 1=number
  • 2=text
  • 4=a logical value (And, Or, and so on)
  • 16=an error
  • 64=an array

Find the LENgth of Your Text

Finding the length of a text string can be very useful in cases where you have many pieces of similar text. For example, if you have a long list of factory part codes, you can test if any are invalid by testing the length. Let’s say the valid format for a factory code is five characters. Running the LEN function in a column next to the factory codes can reveal those that have a length that is not five characters and are therefore invalid.

The LEN function is simple; it just takes one argument — the cell or actual text being referenced. For example, here is the LEN function referencing cell F15:

=LEN(F15)

Just in CASE

Three helpful functions make formatting your text a breeze. These CASE functions take a text string and return as all lowercase, all uppercase, or in proper case (each word starts with an uppercase letter). This is useful when formatting titles and headings.

Assuming this string of text is in cell A5 — “Hello, how are you?”:

LOWER(A5) returns hello, how are you?
UPPER(A5) returns HELLO, HOW ARE YOU?
PROPER(A5) returns Hello, How Are You?