Excel Cookbook

Chapter 4. Math and Engineering

Math operations are a crucial part of many spreadsheets, and Excel includes an extensive set of functions that saves you the trouble of performing cumbersome manual calculations.

This chapter guides you through Excel’s math and engineering formulas, taking you beyond the +, -, *, /, and ^ operators. Recipes include ways of rounding numbers; working with sums, multiples, and divisors; using trigonometry; calculating permutations and combinations; solving problems with matrices; and working with complex numbers.

4.1 Generating Numbers

Problem

You want to generate numbers at random or in a sequence.

Solution

To generate a random decimal number greater than or equal to 0 and less than 1, use the RAND function by typing =RAND(). To return a random number greater than or equal to a and less than b, you can tweak this formula to become =RAND()*(b-a)+a. Typing =RAND()*100, for example, returns a random decimal number greater than or equal to 0 and less than 100.

To generate a random integer between a and b inclusive, use the RANDBETWEEN function. Generally, use the formula =RANDBETWEEN(a, b), so typing =RANDBETWEEN(1, 10) returns a random integer between 1 and 10, inclusive.

To generate a dynamic array (see Recipe 3.4) of random numbers, use the formula =RANDARRAY(rows, columns, min, max, integer), where rows (optional) is the number of rows (the default is 1), columns (optional) is the number of columns (the default is 1), min (optional) is the lowest number it’s possible to return (the default is 0), max (optional) is the highest (the default is 1), and integer specifies whether you want to return integer or decimal numbers—use TRUE for integers and FALSE (the default) for decimals. So typing =RANDARRAY(6) returns six rows of random decimal numbers between 0 and 1 (inclusive), while typing =RANDARRAY(5, , 1, 10, TRUE) returns five rows of random integers between 1 and 10 (inclusive).

To generate a dynamic array of sequential numbers, you use the formula =SEQUENCE​(rows, columns, start, step), where rows (optional) is the number of rows (the default is 1), columns (optional) is the number of columns (the default is 1), start (optional) is the first number in the sequence (the default is 1), and step (optional) is the number you want to increment subsequent numbers by (the default is 1). So typing =SEQUENCE(6) returns six rows containing the numbers 1 through 6, and typing =SEQUENCE(5, , 0, 0.2) returns five rows containing the numbers 0, 0.2, 0.4, 0.6, and 0.8.

Warning

The RANDARRAY and SEQUENCE functions are available only in Excel 2021 and Excel 365.

Discussion

This recipe offers several methods for generating numbers, depending on your version of Excel. You can use them in a wide variety of situations, such as generating text characters (see Recipes 5.3 and 5.4).

See Also

See Recipe 9.9 for other ways of generating random numbers.

4.2 Converting Text or a Boolean to a Number

Problem

You have a value stored as text or a TRUE/FALSE value and want to convert it to a number.

Solution

If a numeric value is stored as text, you can convert it to a number using =text*1, =text+0, or =—text. If cell A1 contains the formula =TEXT(123, "00000"), which converts the number 123 to the text 00123 (see Recipe 5.17), typing =A1*1, =A1+0, or =—A1 converts the text back to a number, returning 123.

You can convert a Boolean TRUE/FALSE value to a number using a similar technique. So if A1:A5 contains TRUE/FALSE values, you can return their numeric values using the formulas =A1:A5*1, =A1:A5+0, or =—A1:A5. See Recipe 7.6 for an example of this technique.

Discussion

Math functions such as SUM include only numeric values in their calculations, which can lead to unexpected results. If cell A1 contains the formula ="123", for example, which evaluates to the text 123 instead of a number, the formula =SUM(A1) returns 0. This recipe offers a convenient solution to this type of problem.

See Also

You can use the ISNUMBER and ISTEXT functions to check whether a value is stored as a number or text; see Recipe 7.7.

4.3 Getting a Number’s Sign and Absolute Value

Problem

You have a number and want to know whether it is positive or negative and its absolute value.

Solution

The SIGN function lets you determine a number’s sign using the formula =SIGN​(num⁠ber). The function returns 1 if number is positive, –1 if it’s negative, and 0 if it’s zero, so typing =SIGN(-10), for example, returns –1.

To find a number’s absolute value—the number without its sign—use the formula =ABS(number). For example, if cell A1 contains the number –10, typing =ABS(A1) returns 10.

Discussion

This recipe offers a quick way of determining whether a number is positive, negative, or zero and getting its absolute value. They’re used, for example, in Recipe 4.10.

4.4 Counting, Summing, and Averaging Cell Values

Problem

You have a range of cells that contain numbers and want to count how many there are and calculate their sum and average.

Solution

Suppose A2:A5 lists the numbers 1 to 4, B2:B5 lists the numbers 11 to 14, C2:C5 lists the numbers 21 to 24, and you want to count how many cells contain numbers, and calculate their sum and average.

To count the cells that contain numbers, you can use the COUNT function. Generally, you use the formula =COUNT(range), so typing =COUNT(A2:C5) returns 12. You can also pass multiple ranges to COUNT; typing =COUNT(A2:A5, C2:C5), for example, counts the numbers in A2:A5 and C2:C5, returning 8 (see Figure 4-1).

Figure 4-1. Using COUNT, SUM, and AVERAGE

Tip

You can also use the COUNTA function to count the cells that contain any value (not just numbers) and the COUNTBLANK function to count how many cells are empty.

The SUM and AVERAGE functions work the same way as COUNT except that they return the sum and average of numeric values, so typing =SUM(A2:A5) returns 10, and typing =AVERAGE(A2:A5) returns 2.5.

Discussion

This recipe uses core math functions—COUNT, SUM, and AVERAGE—to count the numbers in one or more ranges and calculate their sum and average.

Note that if you pass two intersecting ranges to the COUNT, SUM, or AVERAGE functions, they include any numbers in the intersection twice—once for each range. For example, if the range A2:C5 contains numbers, the formula =COUNT(A2:A5, A5:C5) returns 7 instead of 6 because it includes A5 twice. To work around this problem, you can use the formula =COUNT(range1, range2)-COUNT(range1 range2), where the second COUNT uses the Space operator (see Recipe 3.1) to count the cells in the intersection.

See Also

See Recipe 8.4 for more information on calculating different types of averages.

4.5 Using Criteria to Count, Sum, and Average

Problem

You have a range of cells containing numbers and want to count how many match specified criteria and calculate their sum and average.

Solution

Suppose A2:A8 lists food items, B2:B8 lists their amounts, and you want to find the count, sum, and average of cells that meet specified conditions.

If there’s a single condition, you can use the COUNTIF, SUMIF, and AVERAGEIF functions as follows (see Figure 4-2):

COUNTIF

COUNTIF counts the values that meet a single condition. Generally, you use the formula =COUNTIF(range, condition), where range is the range of cells you want to apply the condition to, so typing =COUNTIF(B2:B8, ">10") counts how many amounts are greater than 10 and typing =COUNTIF(A2:A8, "Pizza") counts the number of Pizza items.

SUMIF

SUMIF works similarly to COUNTIF except it sums the values that meet the condition. Generally, you use the formula =SUMIF(range, condition, sum_range), where range is the range of cells you want to apply the condition to, and sum_range (optional) is the range of cells you want to sum (if it’s different from range), so typing =SUMIF(A2:A8, "Pizza", B2:B8) returns the sum of any Pizza amounts.

AVERAGEIF

AVERAGEIF works the same as SUMIF, except it calculates the average, so typing =AVERAGEIF(A2:A8, "Pizza", B2:B8) returns the average amount of any Pizza items.

Figure 4-2. Using criteria to count items and calculate the sum and average

If there are multiple conditions, you can use the COUNTIFS, SUMIFS, and AVERAGEIFS functions as follows (see Figure 4-2):

COUNTIFS

COUNTIFS counts the number of cells meeting multiple conditions. Generally, use =COUNTIFS(condition_range1, condition1, condition_range2, condition2, …​), where you want to apply condition1 to condition_range1, condition2 to condition_range2, and so on. To count the number of Pizza items with an amount greater than 10, you’d type =COUNTIFS(A2:A8, "Pizza", B2:B8, ">10").

SUMIFS

SUMIFS works similarly to COUNTIFS, except it calculates the sum. Generally, use =SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2, …​), where sum_range is the range of cells you want to sum, and you want to apply condition1 to condition_range1, condition2 to condition_range2, and so on. For example, =SUMIFS(B2:B8, A2:A8, "Pizza", B2:B8, ">10") returns the total amount of any Pizza items with an amount greater than 10.

AVERAGEIFS

AVERAGEIFS works the same as SUMIFS, except it calculates the average, so typing =AVERAGEIFS(B2:B8, A2:A8, "Pizza", B2:B8, ">10") calculates the average amount of any Pizza items where the amount is greater than 10.

Discussion

This recipe expands on Recipe 4.4 to calculate the count, sum, and average subject to one or more conditions. An alternative approach would be to add these values to a table (see Recipe 2.18) and apply a filter, or use the FILTER function (see Recipe 7.3).

4.6 Adding and Subtracting Squares of Values

Problem

You have a range of numbers and want to perform calculations using the sum of—or difference between—their squared values.

Solution

Suppose cells A2:A6 list the x values 1 to 5, B2:B6 lists the corresponding y values 11 to 15, and you want to perform calculations using the square of their values. Excel includes several functions you can use as follows (see Figure 4-3):

SUMSQ

SUMSQ calculates Σx²—the sum of the squares of the x values. You generally use the formula =SUMSQ(x_values), so typing =SUMSQ(A2:A6) returns 55. You can also pass multiple ranges to the SUMSQ function, so typing =SUMSQ(A2:A6, B3:B5), for example, sums the squares of the values in A2:A6 and B3:B5, returning 564.

SUMX2PY2

SUMX2PY2 calculates Σ(x²+y²)—the sum of the squares of the x and y values. This takes the form =SUMX2PY2(x_values, y_values), so typing =SUMX2PY2(A2:A6, B2:B6) returns 910.

SUMX2MY2

SUMX2MY2 calculates Σ(x²-y²)—the sum of the differences of the squares between the x and y values. Generally, you use =SUMX2MY2(x_values, y_values), so typing =SUMX2MY2(A2:A6, B2:B6) returns –800.

SUMXMY2

SUMXMY2 calculates Σ(x-y)²—the sum of the squares of differences between the x and y values. Generally, you use =SUMX2MY2(x_values, y_values), so typing =SUMXMY2(A2:A6, B2:B6) returns 500.

Figure 4-3. Adding and subtracting sums of squares

Warning

The SUMX2PY2, SUMX2MY2, and SUMXMY2 functions expect the same number of x values as y values. If this isn’t the case, the functions return the #N/A error value.

Discussion

This recipe offers a flexible way of performing different calculations using square values. Note that if you have two ranges that contain the same number of values, the formulas =SUMSQ(range1, range2) and =SUMX2PY2(range1, range2) return the same value.

4.7 Using Multiplication and Multiples

Problem

You have a range of numbers and want to multiply them, calculate the sum of their products for each row, and find the least or lowest common multiple.

Solution

Suppose cells A2:A4 list the numbers 1, 2, and 6, and B2:B4 list the numbers 4, 8, and 16. You want to find their product, the sum of the products for each row, and the least common multiple.

To multiply all the cells containing numbers, use the PRODUCT function. Generally, you use the formula =PRODUCT(range), so typing =PRODUCT(A2:B4) returns 6144 (see Figure 4-4).

To calculate the sum of the products of two or more corresponding ranges—for example, two columns—use the SUMPRODUCT function. This function takes the form =SUMPRODUCT(range1, range1), where range1 and range2 are the two ranges whose values you want to multiply and then add. To multiply the values for each row and add the results together, for example, you type =SUMPRODUCT(A2:A4, B2:B4), which returns 116.

Figure 4-4. Using PRODUCT, SUMPRODUCT, and LCM

Tip

You can also use the SUMPRODUCT function to perform other operations before summing the values, such as division or subtraction. To do so, replace the comma with the operator you want to use (*, /, +, or -)—for example, =SUMPRODUCT(A2:A4/B2:B4).

To find the least common multiple of a set of integers—the smallest positive integer that’s a multiple of each number—use the LCM function. You generally use the formula =LCM(range), so typing =LCM(A2:B4) returns 48.

Discussion

This recipe offers several functions you can use when multiplying numbers. Note that the SUMPRODUCT function is often used in statistics to calculate weighted averages (see Recipe 8.4 for more information).

4.8 Finding Quotients, Remainders, and Divisors

Problem

You have two numbers and want to find the integer and remainder parts when you divide one number by the other as well as the greatest common divisor for the two numbers.

Solution

Suppose A2 contains the number 26 and A3 contains 6. You want to divide A2 by A3 and split the result into integer and remainder parts.

To find the integer part, use the QUOTIENT function. This takes the form =QUOTIENT(number, divisor), where you want to divide the number argument by the divisor. To find the integer part when you divide A2 by A3, type =QUOTIENT(A2, A3), which returns 4 when A2 is 26 and A3 is 6.

Tip

You can also get this result by typing =TRUNC(A2/A3). See Recipe 4.9 for more information about this function.

To find the remainder, use the MOD function. You generally use the formula =MOD​(num⁠ber, divisor), where you want to divide the number argument by the divisor, so to find the remainder when you divide A2 by A3, you type =MOD(A2,A3), which returns 2 when A2 is 26 and A3 is 6.

Tip

You can also use the MOD function to return the fractional part of a number using the formula =MOD(number, 1).

To find the greatest common divisor—the largest integer that divides the numbers with no remainder—use the GCD function. Generally, you use the formula =GCD​(num⁠bers), so to find the greatest common divisor for the numbers in cells A2:A3, you type =GCD(A2:A3), which returns 2 when A2 is 26 and A3 is 6.

Discussion

This recipe shows you how to find quotients, remainders, and the greatest common denominator. It also introduces the MOD function, which has many applications—for example, Recipe 6.8.

4.9 Rounding to Decimal Places and Integers

Problem

You have a number and want to round it to a specified number of decimal places or an integer.

Solution

Excel includes various rounding functions you can use, depending on whether you want to round positive and negative numbers up, down, or to the nearest number. Figure 4-5 summarizes the behavior of these functions.

To round to the nearest integer or decimal number, use the ROUND function. You generally use the formula =ROUND(number, num_digits), where num_digits specifies the number of decimal places you want to round the number argument to. If num_digits is positive, it specifies the number of decimal places; if it’s zero, the function rounds to the nearest integer; and if it’s negative, the function rounds to the left of the decimal point. So typing =ROUND(5.6, 0) rounds 5.6 to the nearest integer (returning 6), and typing =ROUND(-5.673,2) rounds –5.673 to 2 decimal places (returning –5.67).

Figure 4-5. How different functions round positive and negative numbers

To round positive and negative numbers away from zero, use the ROUNDUP function. This function works similarly to ROUND, except it rounds positive numbers up and negative numbers away from zero; behind the scenes, it rounds up the number’s absolute value, increasing its magnitude, and then applies its sign. So typing =ROUNDUP(5.321, 2) rounds 5.321 up to 5.33, and typing =ROUNDUP(-5.3, 0) rounds –5.3 to –6.

To round positive numbers away from zero and negative numbers toward it, use the CEILING.MATH function. You generally use the formula =CEILING.MATH(number, multiple), where number is the number you want to round and multiple (optional) specifies a multiple you want to round numbers to—it defaults to 1 for positive numbers and –1 for negative. For example, typing =CEILING.MATH(5.321, 0.1) rounds 5.321 up to 5.4, and =CEILING.MATH(-5.3) rounds –5.3 to –5.

To round positive and negative numbers toward zero, use the ROUNDDOWN function. This function works similarly to the ROUND and ROUNDUP functions, except it rounds positive numbers down and negative numbers toward zero; behind the scenes, it rounds down the number’s absolute value, decreasing its magnitude, then applies its sign. So typing =ROUNDDOWN(5.32, 1) rounds 5.32 down to 5.3, and typing =ROUNDDOWN(-5.3, 0) rounds –5.3 to –5.

Tip

If you want to round a number to an integer, you can use the TRUNC function instead of ROUNDDOWN. TRUNC works the same way as ROUNDDOWN and uses the same arguments, but its num_digits argument is optional and defaults to zero. So typing =TRUNC(5.3) rounds 5.3 down to 5, and typing =TRUNC(-5.3) rounds –5.3 to –5.

To round positive numbers toward zero and negative numbers away from it, use the FLOOR.MATH function, which works similarly to CEILING.MATH. You generally use the formula =FLOOR.MATH(number, multiple), where number is the number you want to round and multiple (optional) specifies a multiple you want to round numbers to—it defaults to 1 for positive numbers and –1 for negative. So typing =FLOOR.MATH(5.321, 0.1) rounds 5.321 down to 5.3, and typing =FLOOR.MATH​(-5.3) rounds –5.3 to –6.

Tip

If you want to round a number to an integer, you can use the INT function instead of FLOOR.MATH. This function accepts a single argument—the number you want to round to an integer—so typing =INT(5.3) rounds 5.3 down to 5, and typing =INT(-5.3) rounds –5.3 to –6.

Discussion

As you can see, Excel includes several functions that round numbers in slightly different ways; while the ROUND function rounds values to the nearest number, all the others round positive or negative numbers toward or away from zero. See Figure 4-5 for a comparison of function rounding behavior.

4.10 Rounding to Significant Figures and Multiples

Problem

You have a number and want to round it to a multiple of another number or a specified number of significant figures.

Solution

The ROUND, ROUNDUP, ROUNDDOWN, and TRUNC functions (see Recipe 4.9) let you round a number to a multiple that’s a power of 10. Typing =ROUND(234.5, -1), for example, rounds 234.5 to the nearest multiple of 10, returning 230, and typing =ROUND(234.5, -2) rounds it to the nearest multiple of 100, returning 200.

You can also use these functions to round a number to a specified number of significant figures. You generally use the formula =ROUND(number, significant_figures-LEN(TRUNC(ABS(number)))), where you want to round number to the specified number of significant_figures. So if cell A1 contains the number 23456.789, you’d round it to two significant figures by typing =ROUND(A1, 2-LEN(TRUNC(ABS(A1)))), returning 23000.

To round a number to a multiple that’s not a power of 10, use the MROUND, CEILING.MATH, and FLOOR.MATH functions.

The MROUND function rounds a number to the nearest multiple and takes the form =MROUND(number, multiple). To round 23456 to the nearest multiple of 5, you’d type =MROUND(23456, 5), which returns 23455, and to round –23456 to the nearest multiple of –5, you’d type =MROUND(-23456, -5), which returns –23455.

Warning

The MROUND function’s number and multiple arguments must have the same sign, or it returns a #NUM! error value. If you need to round positive and negative numbers to the nearest multiple, use the formula =MROUND(number, multiple*SIGN(number)) instead.

To round a positive number up to the next nearest multiple, use the CEILING.MATH function in the formula =CEILING.MATH(number, multiple, mode), where mode (optional) specifies how you want to round number if it’s negative; omit mode it to round a negative number toward zero, and use 1 to round it away from zero. So typing =CEILING.MATH(-234, 5) rounds –234 to the nearest multiple of five toward zero and returns –230, and typing =CEILING.MATH(-234, 5, 1) rounds it to the nearest multiple away from zero and returns –235.

To round a positive number down to the next nearest multiple, use the FLOOR.MATH function in the formula =FLOOR.MATH(number, multiple, mode); in this case, you omit the optional mode argument to round a negative number away from zero and use –1 to round it toward zero. So typing =FLOOR.MATH(-234, 5) rounds –234 to the nearest multiple of five away from zero and returns –235, and typing =FLOOR.MATH(-234, 5, -1) rounds it to the nearest multiple toward zero and returns –230.

Finally, to round a number to the nearest even or odd number away from zero, use the EVEN and ODD functions. Typing =EVEN(2.3), for example, returns 4, while typing =ODD(2.3) returns 3.

Discussion

This recipe builds on Recipe 4.9 to round a number to a specified number of significant figures or a multiple of another number.

4.11 Using Powers, Exponents, Square Roots, and Logarithms

Problem

You have a number and want to raise it to a power or find its square root.

Solution

You can use the ^ operator or the POWER function to raise a number to a power. Typing =2^4 or =POWER(2,4), for example, calculates 2⁴ and returns 16.

You can also use the POWER function with ranges in the formula =POWER(numbers, powers). If A2:A5 contains the numbers 1 to 4, for example, typing =POWER(A2:A5, 2) in Excel 2021 or Excel 365 returns a dynamic array (see Recipe 3.4) containing each number squared, and typing =POWER(A2:A5, A2:A5) calculates x^x for each value: 1¹, 2², and so on (see Figure 4-6).

Figure 4-6. Using the POWER function

To find the square root of a number, use the SQRT function. Typing =SQRT(4), for example, returns 2, and typing =SQRT(A2:A5) in Excel 2021 or Excel 365 returns a dynamic array containing the square root of each number in A2:A5.

To raise e to the power of a number, use the formula =EXP(number). So typing =EXP(5) calculates e⁵, returning 148.4132.

The inverse of EXP is the LN function, which returns a number’s natural logarithm. You generally use the formula =LN(number), which returns the power you need to raise e to so that it equals number. So typing =LN(3) returns 1.0986—since e^1.0986 is approximately 3—and typing =LN(EXP(5)) returns 5.

To find the base-10 logarithm of a number, use the LOG10 function. In general, use the formula =LOG10(number), which returns the power you need to raise 10 to so that it equals number. So typing =LOG10(1000) returns 3 because 10³ equals 1000.

To find the logarithm of a number for a base you specify, use the LOG function. This function takes the form =LOG(number,base) and returns the power you need to raise base to so that it equals number. So typing =LOG(16,2) returns 4 because 2⁴ equals 16.

Discussion

This recipe details several functions you can use to work with powers, exponents, and logarithms, including finding a number’s square root.

4.12 Summing a Power Series

Problem

You have a power series (for example, 5x³+4x²+3x+2) and want to know its result for a value of x.

Solution

The SERIESSUM function returns the sum of a power series. It takes the form =SERIESSUM(x, n, m, a), which represents the equation a₁xⁿ + a₂x^(n+m) + a₃x^(n+2m) + a₄x^(n+3m), and so on. The x argument is the value of x, n is the first power of x in the series, m is the step size used to increase n by for each successive power of x, and a is an array of the coefficients used to multiply x. You can calculate 5x³ + 4x² + 3x + 2, where x is 2, for example, by typing the formula =SERIESSUM(2, 3, -1, {5,4,3,2}), which returns 64.

Discussion

This recipe offers a way of calculating the sum of a power series. Instead of typing the formula using the * and ^ operators, you can use the SERIESSUM function to get the same result.

4.13 Using Factorials, Permutations, and Combinations

Problem

You want to find a number’s factorial, or the possible number of combinations and permutations when you select r items from n.

Solution

To find a number n’s factorial—the number of ways of arranging n items, or the product of all positive integers—use the FACT function. You generally use the formula =FACT(n), which returns n!—the product of all positive integers from 1 to n— so typing =FACT(5) returns 120.

Note

Excel also has a FACTDOUBLE function, which returns a number’s double factorial, or n!!—the product of all the integers from 1 to n that have the same parity (odd or even) as n.

To find the number of ways to arrange n items, where some are duplicates, use the MULTINOMIAL function. Suppose, for example, that you want to find the number of ways of arranging the letters in the word Mississippi, which has only four unique letters. Since there is one letter M, two Ps, and four each of I and S, you can calculate the number of arrangements by typing =MULTINOMIAL(1, 2, 4, 4), which returns 34,650. Generally, you use the formula =MULTINOMIAL(item_counts), where item_counts specifies how many there are of each item.

To find the number of permutations when you select r items from n—the number of ways of arranging the items when the order matters—and can select each item only once, use the PERMUT function. Generally, you use the formula =PERMUT(n, r), so if, for example, you wanted to know how many portfolios you could create by choosing 2 stocks from 20 where you want one stock to have 60% of the allocation and the other stock to have the remaining 40%, you’d type =PERMUT(20, 2), returning 380.

To find the number of combinations—where the order doesn’t matter—when you select r items from n and can select each item only once, use the COMBIN function. This function takes the form =COMBIN(n, r), so if, for example, you wanted to know how many portfolios you could create by choosing 2 equally weighted stocks from 20, you’d type =COMBIN(20, 2), returning 190.

Note

The PERMUT and COMBIN functions assume you can select each item only once. If you can select each item more than once, use the PERMUTATIONA and COMBINA functions instead. To find the number of possible five-digit passwords that use numbers from zero to nine, you’d type =PERMUTATIONA(10, 5), returning 100000.

Discussion

This recipe offers an overview of Excel’s combinatorics functions, which involve counting the number of possible arrangements. These functions are helpful in areas such as probability and form the basis of the binomial distribution (see Recipe 8.14).

4.14 Using Trigonometry

Problem

You have a trigonometry problem and want to know what functions are available.

Solution

Excel includes the following trigonometry functions:

PI

This returns the constant π (pi) to 15 digits.

RADIANS and DEGREES

RADIANS converts an angle from degrees to radians, and DEGREES converts an angle from radians to degrees.

SIN, COS, and TAN

These return the sine, cosine, and tangent of an angle specified in radians. To calculate the sine of π/2 radians, you type =SIN(PI()/2), which returns 1.

CSC, SEC, and COT

These return the cosecant, secant, and cotangent of an angle specified in radians. To calculate the secant of π/3 radians, type =SEC(PI()/3), which returns 2.

ASIN, ACOS, ATAN, and ATAN2

The ASIN, ACOS, and ATAN functions calculate a number’s arcsine, arccosine, and arctangent and return an angle in radians; the ATAN2 function returns the arctangent of specified x and y coordinates.

SINH, COSH, TANH, CSCH, SECH, COTH, ASINH, ACOSH, and ATANH

These functions return the hyperbolic sine, cosine, tangent, and so on.

Discussion

As you can see, Excel offers a wealth of functions you can use to solve trigonometry problems. Most of them accept or return an angle in radians, so if needed, use the RADIANS and DEGREES functions to convert between radians and degrees.

4.15 Working with Matrices

Problem

You want to solve a problem in Excel using matrices but don’t know how.

Solution

Suppose three customers decide to buy different amounts of two products. You know each product’s unit price and weight and want to calculate the total price and weight for each customer. B2:C4 lists each customer’s quantities, and F2:G3 lists the unit price and weight.

You can solve this problem by treating the quantities bought and the unit prices and weights as two matrices and multiplying them. You do this using the MMULT function, which multiplies two matrices and returns a dynamic array. In this example, typing =MMULT(B2:C4, F2:G3) returns a dynamic array that calculates each customer’s total price and weight (see Figure 4-7).

Figure 4-7. Using the MMULT function to multiply two matrices

Suppose you know each product’s unit price, weight, and totals for each customer. In this case, you can calculate the quantities bought by each customer by multiplying the matrix of customer totals by the inverse of the unit price and weight matrix. You find the inverse of a matrix (if one exists) using the MINVERSE function, so if B2:C4 lists the customer totals and F2:G3 lists the unit price and weight, typing =MMULT(B2:C4, MINVERSE(F2:G3)) returns a matrix of the customer purchases (see Figure 4-8).

Figure 4-8. Using the MMULT and MINVERSE functions

Other matrix functions include the following:

MUNIT

This function returns the unit matrix for a specified dimension. For example, the formula =MUNIT(3) returns a unit matrix with three rows and columns.

MDETERM

This returns the determinant of a matrix.

TRANSPOSE

This transposes a range of cells that converts the rows to columns and the columns to rows (see Recipe 7.4).

Warning

MMULT, MINVERSE, MUNIT, and TRANSPOSE return dynamic arrays (see Recipe 3.4). If you’re using a version of Excel earlier than Excel 2021 or Excel 365, select the entire output range before entering the formula, and then press Ctrl+Shift+Enter/Return.

Discussion

This recipe offers a handy overview of using Excel’s matrix functions. You can use them, for example, to solve systems of linear equations, as in the examples used in this recipe.

4.16 Converting Between Number Systems

Problem

You have a number and want to convert it to another system (for example, from decimal to binary, octal, or hexadecimal).

Solution

Use the DEC2BIN, DEC2OCT, DEC2HEX, BIN2DEC, BIN2OCT, BIN2HEX, OCT2DEC, OCT2BIN, OCT2HEX, HEX2DEC, HEX2BIN, and HEX2OCT functions to convert numbers from one system to another. To convert the decimal number 9 to binary, for example, you type =DEC2BIN(9), which returns 1001. Similarly, to convert the hexadecimal number A2 to decimal, you type =HEX2DEC("A2"), which returns 162.

Discussion

This recipe shows converting numbers between decimal, binary, octal, and hexadecimal numbers; this is useful if, for example, you want to perform bitwise operations on a binary bit field (see Recipe 4.17).

4.17 Performing Bitwise Operations

Problem

You have a binary bit field and want to perform bitwise operations.

Solution

Suppose you have a set of decimal readings from a salt truck’s controller system, where each number represents the current state of its four sensors as a binary bit field. The first bit corresponds to the pressure valve sensor, the second to the spreader, the third to the salt feed, and the fourth to the beacon. Each sensor’s bit is set to 1 if it’s on and 0 if it’s off, so if the pressure valve and spreader are on but the salt feed and beacon are off, the controller records the value 0011 in binary, or the decimal number 3.

You can apply the BITAND function to the controller’s readings to determine whether a sensor is on or off. This function takes the form =BITAND(number1, number2), where number1 and number2 are decimals, and returns the bitwise AND of its arguments as a decimal. If the controller records the value 6, for example, you can find out whether the pressure valve—the first sensor—is on by typing =BITAND(6, 1)>0, which returns FALSE. Similarly, you can find out whether the spreader—the second sensor—is on by typing =BITAND(6, BIN2DEC(10))>0, which returns TRUE. Finally, if cells A2:A6 list a set of readings from the controller and you’re using Excel 2021 or Excel 365, you can return a dynamic array (see Recipe 3.4) showing the state of each sensor by typing =BITAND(A2:A6, BIN2DEC({1,10,100,1000}))>0 (see Figure 4-9).

Figure 4-9. Using the BITAND function with a binary bit field

Other bitwise functions include the following:

BITOR and BITXOR

These return the bitwise OR and XOR (eXclusive-OR) of their arguments.

BITLSHIFT and BITRSHIFT

These return a number shifted left or right by a specified number of bits.

Discussion

Bitwise operators let you perform operations at a bit level and work with the binary representation of a number instead of the number’s value. They’re helpful when working with structures such as bit fields, as in the example used by this recipe.

See Also

For more information about converting between decimal and binary number systems, see Recipe 4.16.

4.18 Working with Complex Numbers

Problem

You want to work with complex numbers in Excel and want to know what functions are available.

Solution

A complex number is one in the form a + bi, where a and b are real numbers, and i is the square root of –1.

To enter a complex number into a cell, you can type it directly or pass real and imaginary coefficients to the COMPLEX function. To enter the imaginary number 6+2i in cell A1, for example, you can either type 6+2i or the formula =COMPLEX(6, 2).

Tip

Excel also supports using j for complex numbers instead of i. Type either 6+2j or the formula =COMPLEX(6, 2, "j").

To retrieve a complex number’s real and imaginary coefficients, use the IMREAL and IMAGINARY functions. For example, if cell A1 contains the complex number 6 + 2i, typing =IMREAL(A1) returns 6, and typing =IMAGINARY(A1) returns 2.

To find a complex number’s conjugate, argument, and absolute value, use the IMCONJUGATE, IMARGUMENT, and IMABS functions. So if cell A1 contains 6+2i, for example, typing =IMCONJUGATE(A1) returns 6-2i.

To perform arithmetic operations with complex numbers, use the IMSUM function to sum them, IMSUB to subtract one from another, IMPRODUCT to calculate the product, and IMDIV to divide one by another. For example, typing =IMSUM("6+2i", "5-4i") returns 11-2i, and typing =IMPRODUCT("6+2i", "5-4i") returns 38-14i.

Warning

Ensure you consistently use i or j to denote a complex number, not both. Typing =IMSUM("6+2j", "5-4j"), for example, returns 11-2j, but typing =IMSUM("6+2i", "5-4j") returns the #VALUE! error value.

Excel also includes complex number versions of many other functions, each prefixed with IM. For example, use IMSQRT to find the square root of a complex number, IMLN to find the natural logarithm, IMSIN to find the sine, and so on.

Discussion

This recipe offers an overview of Excel’s most important complex number functions. These can be used, for example, with the output from the Analysis ToolPak’s Fourier Analysis tool (see Recipe 9.19).