Section 8.3. General Math Functions | Excel 2010: The Missing Manual

8.3. General Math Functions

Excel's Math & Trig category contains functions that go a step beyond the basic mathematical operators (like +, -, /, *, and ^) to help you figure out things like absolute values and greatest (and least) common denominators.

Note: Poker fan alert: the COMBIN( ) and PERMUT( ) functions may help you keep your game sharp even when you're away from the table.

This section covers 14 of the most commonly used functions in this category.

8.3.1. PRODUCT( ), FACT( ), POWER( ), and SQRT( ): Products, Factorials, Powers, and Square Roots

These four functions actually offer the same services provided by many of the simple operators covered in the last chapter, but, in some cases, they come with a few twists that may save you time.

The PRODUCT( ) function takes up to 30 arguments, multiplies them together, and gives the result. For example, the following formula multiplies 2*3*3, and arrives at 18:

 =PRODUCT(2,3,3)

One interesting characteristic of the PRODUCT( ) function is that in addition to accepting individual cell references, it also supports cell ranges . This means you can use PRODUCT( ) to multiply all the numbers in a range in the same way you use SUM( ) to add numbers . Here's an example:

 =PRODUCT(A2:A12)

You can use the FACT( ) function to calculate a factorial , which is a sequence of numbers (starting at 1), each of which is multiplied together to arrive at the result. For example, 5! (pronounced "five factorial") translates into 5*4*3*2*1, which works out to 120. Rather than typing in all these numbers and their accompanying multiplication symbols, you can use the following convenient formula:

 =FACT(5)

Factorials are used in many areas of math, including probability theory and calculus. On the other hand, you aren't likely to find factorials cropping up in financial statements or expense reports .

POWER( ) is a straightforward function used for working with exponents. For example, if you want to calculate 4 ^3, you can use one of two approaches. You can use the exponentiation operator:

 =4^3

or you can opt for the POWER( ) function:

 =POWER(4,3)

The POWER( ) function is useful if you frequently use cube rootsor any other exponential expressions whose exponent is a fractionbecause it lets you write clearer formulas. For example, imagine you want to find the cube root of 4, which, on paper, would be written as 4 ^1/3 . Here's the operator-based expression for that:

 =4^(1/3)

With the POWER( ) function, you don't need to use the parentheses (and, more important, you have no chance of getting the wrong answer because you forgot to use them):

 =POWER(4,1/3)

If you simply want to calculate the square root of a number, you can use the POWER( ) function, or you can summon the SQRT( ) function, which only requires a single argument. Here's a sample formula that gives you 3:

 =SQRT(9)

8.3.2. QUOTIENT( ) and MOD( ): Higher Division

QUOTIENT( ) and MOD( ) (see Figure 8-6) are handy for fancy division problems.

QUOTIENT( ) performs integer division, which means it ignores the remainder, and just gives you the whole number result. For example, consider the following formula:

 =QUOTIENT(7,2)

This formula performs the calculation 7/2, and gives you a result of 3. Excel discards the remainder of 1. If you performed this division using the division operator, you'd end up with the fractional value 3.5. Sure, you could truncate the remainder portion using a function like INT( ), but the QUOTIENT( ) function saves you the trouble.

The MOD( ) function gives you the remainder (also known as the modulus ) that's left over when you divide a number. For instance, the following formula gives you 1, which is what remains after dividing 7 by 2 using integer division:

 =MOD(7,2)

8.3.3. ABS( ) and SIGN( ): Absolute Value and Determining a Number's Sign

ABS( ) is a straightforward function that gives you the absolute value of a number. The absolute value is the number stripped of any negative sign. Thus, the absolute value of -3 is 3. Here's how that function would look:

 =ABS(-3)

The ABS( ) function doesn't change a positive number. For example, the absolute value of 8 is just 8. Absolute value calculations work equally well with whole numbers or fractional values. Often, ABS( ) is used to make sure that a number isn't negative. For example, Excel's financial functions (Chapter 9) sometimes use negative numbers when you don't expect itlike when you calculate the monthly payment on a loanto represent the fact that money is leaving your hands. However, to avoid confusing the person reading the spreadsheet, you can use ABS( ) to make sure that all the information you display is positive, which tends to be what most people understand best.

A related , but less useful function is SIGN( ), which lets you quickly determine whether a number is positive or negative. SIGN( ) gives you 1 if the number is positive, 0 if the number is 0, and -1 if the number is negative (as is the case below):

 =SIGN(-3)

SIGN( ) isn't used too often, because it's usually just as easy to use a condition like A1<0 to check if a number is negative.

8.3.4. RAND( ) and RANDBETWEEN( ): Generating Random Numbers

The RAND( ) function gives you a random fractional number that's less than 1, but greater than or equal to 0 (like 0.778526443457398).

 =RAND( )

You can use this function, as shown in Figure 8-7, to calculate values in any range. For example, if you want a random value between 0 and 50, multiply the RAND( ) result by 50, as in this formula:

 =RAND( )*50

Note: Technically, random numbers from a computer aren't really random. Instead, they're either generated from an algorithm or drawn from a list of pseudo-random numbers that the computer has stored in its hardware. When generating a random number, most programs look at the current millisecond value of your computer's clock to decide where to start in this list. Despite these limitations, however, Excel's random numbers will still suffice for most spreadsheetsjust don't count on using them for advanced statistical models or secret-sauce encryption tools.

Converting a RAND( )-generated number into an integer is pretty easy: just use it in conjunction with the INT( ) function. For example, here's how to get a random whole number from 1 to 6:

 =INT(RAND( )*6)+1

For added efficiency, the Analysis ToolPak (Section 7.2.4) can save you a step with its RANDBETWEEN( ) function. This function takes two arguments, indicating the minimum and maximum between which you want your randomly selected value to fall. For example, consider the following:

 =RANDBETWEEN(1,6)

This formula generates a random number from 1 to 6 (crucial if your boss is onto your noontime Craps game).

The RAND( ) and RANDBETWEEN( ) functions are volatile functions , meaning their results change every time you recalculate the worksheet. This behavior can be quite distracting, as actions like inputting new data, editing a cell, or even undoing an action can trigger recalculationat which point any random values in your worksheet will change. If you want to use the RAND( ) or RANDBETWEEN( ) function to generate a set of random numbers, and then keep these numbers fixed, you'll need to do a little more work. Here's the deal:

Create one or more random numbers using the RAND( ) or RANDBETWEEN( ) function.

For example, you might want to fill a column using RANDBETWEEN(1,6) to simulate the outcome of rolling a die multiple times.

Figure 8-7. Top : One neat trick is to use the RAND( ) function to perform a random sort , which is helpful, say, if you need help deciding which of your 12 sisters to call. To do this, begin by adding a new column next to the column listing their names . Insert random values in each of the new cells. Then, select cells A1 through B2. Middle: Choose Data Sort from the menu to show the Sort dialog box. Choose to sort using the column (Column B) with the random value and click OK.
Bottom : A randomly ordered list of names.

Select the cells with the random values, and copy them by selecting Edit Copy (Ctrl+C).

The next step pastes the cell content back into the same locationbut with a twist.
Choose Edit Paste Special. When the Paste Special window appears, select the Value option instead of All, and then click OK.

This step overwrites the formulas and permanently inserts the calculated random numberspermanent, that is, until you decide to change them.

8.3.5. GCD( ) and LCM( ): Greatest and Least Common Denominator

The GCD( ) function returns the greatest common divisor that can be used to divide two different numbers. For example, consider the numbers 12 and 8. Both these numbers share the divisor 4 (12/4 = 3 and 8/4 = 2). Therefore 4 is the greatest common divisor of 8 and 12. The number 12 can also be divided by 6, but it isn't possible to divide 8/6 without generating a fraction.

Here's how you'd arrive at this conclusion using the GCD( ) function:

 =GCD(12,8)

There are a couple of points to consider about GCD( ). If you use fractional values, GCD( ) will simply ignore the fractional part of the number. That means it will calculate GCD(12.5,8.1) in the same way that it calculates GCD(12,8). Also, a common divisor will always be found, as all whole numbers are divisible by 1, even if they don't have any other divisors.

The LCM( ), or least common multiple function, works slightly differently. This function answers the question: Among all the numbers being processed by this function, what's the smallest number that they all can be divided into ? For example, consider again 12 and 8. Both these numbers can be divided evenly into 24, which makes it their lowest common multiple. Here's the formula that gleans this insight:

 =LCM(12,8)

As with GCD( ), LCM( ) discards fractions in the numbers it's evaluating. The function will always find a common multiple, as you can always multiply the arguments together. For example, 12*8 produces a number that's guaranteed to be a multiple of both 12 and 8, although, in this case, it's not the lowest multiple.

Note: Both GCD( ) and LCD( ) are a part of the Analysis ToolPak add-in (Section 7.2.4).

8.3.6. COMBIN( ) and PERMUT( ): Figuring Combinations and Permutations

The COMBIN( ) and PERMUT( ) functions are used in probability theory to count the number of possible outcomes when randomly choosing items from a set. COMBIN( ) and PERMUT( ) are short for combinations and permutations , which are mathematical terms for the number of distinct combinations you can make from a set of items. The main difference lies in the way they consider the order of items in a set (more on that in a moment).

One reason you might use COMBIN( ) and PERMUT( ) is to assess the odds in a game of chance. You could use COMBIN( ) to help you figure out, say, the odds of being dealt an ace of spades from a deck of cards. And both these functions are often used when trying to calculate the occurrence of other random events, like winning the lottery or being hit by a falling anvil.

Both COMBIN( ) and PERMUT( ) take two arguments. The first argument is the number of different items in your set. The second argument is the number of items you're randomly selecting each time you "draw" from the set. For example:

 COMBIN(number_in_set, number_chosen)

The key difference between COMBIN( ) and PERMUT( ) is that PERMUT( ) assumes order is important, while COMBIN( ) assumes it isn't. For example, say you want to count how many possible ways there are to draw five cards from a 52-card deck.

You'd use the PERMUT( ) function as shown here:

 =PERMUT(52,5)

PERMUT( ) generates a whopping result of 311,875,200 because it counts every group of five cards as unique, even if the difference is only a matter of card order. Thus, the set 1, 2, 3, 4, 5 is different, for PERMUT( ), than 5, 4, 3, 2, 1.

By contrast, when COMBIN( ) evaluates those same parameters, it returns a value of 2,598,960 .

As you can see, there are far fewer combinations then there are permutations. In fact, you can convert the number of combinations into the number of permutations by multiplying it by 5! (5*4*3*2*1), which is a measure of how many different ways there are to arrange the order of five different items.

To go much further with PERMUT( ) and COMBIN( ), you'll need a refresher course in probability theory. However, you may be interested to know that you can find the probability of an event by dividing the number of satisfactory outcomes (the number of outcomes that meet your criteria) into the number of total outcomes (the total number of possibilities). For example, the chance of your getting a specific hand of cards is expressed like so:

 =1/COMBIN(52,5)

Here, you're only interested in a single outcome (a hand that has every card you want). Thus, there's one satisfactory outcome. You can use the COMBIN( ) function to find the total number of possibilities by counting the different possible draws.

To see your percentage chance of getting the cards you want, you can multiply the result by 100 or use the Percentage number style (Section 4.1.1.5).