Chapter 10
Miscellaneous Calculations
In This Chapter
- Converting between measurement units
- Formulas that demonstrate various ways to round numbers
- Formulas for calculating the various parts of a right triangle
- Calculations for area, surface, circumference, and volume
- Matrix functions to solve simultaneous equations
- Useful formulas for working with normal distributions
This chapter contains reference information that may be useful to you at some point. Consider it a cheat sheet to help you remember the stuff you may have learned but have long since forgotten.
Unit Conversions
You know the distance from New York to London in miles, but your European office needs the numbers in kilometers. What’s the conversion factor?
Excel’s CONVERT function can convert between a variety of measurements in the following categories:
- Area
- Distance
- Energy
- Force
- Information
- Magnetism
- Power
- Pressure
- Speed
- Temperature
- Time
- Volume (or liquid measure)
- Weight and mass
The CONVERT function requires three arguments: the value that you want to convert, the from-unit, and the to-unit. For example, if cell A1 contains a distance expressed in miles, use this formula to convert miles to kilometers:
=CONVERT(A1,"mi","km")
The second and third arguments are unit abbreviations, which are listed in the Excel Help system. Some of the abbreviations are commonly used, but others aren’t. And, of course, you must use the exact abbreviation. Furthermore, the unit abbreviations are case sensitive, so the following formula returns an error:
=CONVERT(A1,"Mi","km")
The CONVERT function is even more versatile than it seems. When using metric units, you can apply a multiplier. In fact, the first example we presented uses a multiplier. The actual unit abbreviation for the third argument is m for meters. We added the kilo-multipler—k—to express the result in kilometers.
Sometimes you need to use a bit of creativity. For example, if you need to convert 100 km/hour into miles/sec, the formula requires two uses of the CONVERT function:
=CONVERT(100,"km","mi")/CONVERT(1,"hr","sec")
Figure 10.1 shows a conversion table for area measurements. The CONVERT argument codes are in column B and duplicated in row 3. Cell A1 contains a value. The formula in cell C4, which was copied down and across, is
Figure 10.1 A table that lists all the area units supported by the CONVERT function.
=CONVERT($A$1,$B4,C$3)
The table shows, for example, that 1 square foot is equal to 144 square inches. It also shows that one square light year can hold a lot of acres.
By the way, this formula is also a good example of when to use an absolute reference and mixed references in a formula.
Figure 10.2 shows part of a table that lists all the conversion units supported by the CONVERT function. The table can be sorted and filtered and indicates which of the units support the metric prefixes.
Figure 10.2 A table that lists all the units supported by the CONVERT function.
If you can’t find a particular unit that works with the CONVERT function, perhaps Excel has another function that will do the job. Table 10.1 lists some other functions that convert between measurement units.
Table 10.1 Other Conversion Functions
| Function | Description |
| ARABIC* | Converts an Arabic number to decimal |
| BASE* | Converts a decimal number to a specified base |
| BIN2DEC | Converts a binary number to decimal |
| BIN2OCT | Converts a binary number to octal |
| DEC2BIN | Converts a decimal number to binary |
| DEC2HEX | Converts a decimal number to hexadecimal |
| DEC2OCT | Converts a decimal number to octal |
| DEGREES | Converts an angle (in radians) to degrees |
| HEX2BIN | Converts a hexadecimal number to binary |
| HEX2DEC | Converts a hexadecimal number to decimal |
| HEX2OCT | Converts a hexadecimal number to octal |
| OCT2BIN | Converts an octal number to binary |
| OCT2DEC | Converts an octal number to decimal |
| OCT2HEX | Converts an octal number to hexadecimal |
| RADIANS | Converts an angle (in degrees) to radians |
* Function available in Excel 2013 and higher versions.
Rounding Numbers
Excel provides quite a few functions that round values in various ways. Table 10.2 summarizes these functions.
Table 10.2 Excel Rounding Functions
| Function | Description |
| CEILING.MATH | Rounds a number up to the nearest specified multiple |
| DOLLARDE | Converts a dollar price expressed as a fraction into a decimal number |
| DOLLARFR | Converts a dollar price expressed as a decimal into a fractional number |
| EVEN | Rounds a number up (away from zero) to the nearest even integer |
| FLOOR.MATH | Rounds a number down to the nearest specified multiple |
| INT | Rounds a number down to make it an integer |
| MROUND | Rounds a number to a specified multiple |
| ODD | Rounds a number up (away from zero) to the nearest odd integer |
| ROUND | Rounds a number to a specified number of digits |
| ROUNDDOWN | Rounds a number down (toward zero) to a specified number of digits |
| ROUNDUP | Rounds a number up (away from zero) to a specified number of digits |
| TRUNC | Truncates a number to a specified number of significant digits |
The following sections provide examples of formulas that use various types of rounding.
Basic rounding formulas
The ROUND function is useful for basic rounding to a specified number of digits. You specify the number of digits in the second argument for the ROUND function. For example, the formula that follows returns 123.4. (The value is rounded to one decimal place.)
=ROUND(123.37,1)
If the second argument for the ROUND function is zero, the value is rounded to the nearest integer. The formula that follows, for example, returns 123.00:
=ROUND(123.37,0)
The second argument for the ROUND function can also be negative. In such a case, the number is rounded to the left of the decimal point. The following formula, for example, returns 120.00:
=ROUND(123.37,-1)
The ROUND function rounds either up or down. But how does it handle a number such as 12.5, rounded to no decimal places? You’ll find that the ROUND function rounds such numbers away from zero. The formula that follows, for instance, returns 13.0:
=ROUND(12.5,0)
The next formula returns –13.00. (The rounding occurs away from zero.)
=ROUND(-12.5,0)
To force rounding to occur in a particular direction, use the ROUNDUP or ROUNDDOWN functions. The following formula, for example, returns 12.0. The value rounds down:
=ROUNDDOWN(12.5,0)
The formula that follows returns 13.0. The value rounds up to the nearest whole value:
=ROUNDUP(12.43,0)
Rounding to the nearest multiple
The MROUND function is useful for rounding values to the nearest multiple. For example, you can use this function to round a number to the nearest 5. The following formula returns 135:
=MROUND(133,5)
The second argument for MROUND can be a fractional number. For example, this formula rounds the value in cell A1 to the nearest one-eighth:
=MROUND(A1,1/8)
Rounding currency values
Often, you need to round currency values. For example, you may need to round a dollar amount to the nearest penny. A calculated price may be something like $45.78923. In such a case, you’ll want to round the calculated price to the nearest penny. This may sound simple, but there are actually three ways to round such a value:
- Round it up to the nearest penny.
- Round it down to the nearest penny.
- Round it to the nearest penny. (The rounding may be up or down.)
The following formula assumes that a dollar-and-cents value is in cell A1. The formula rounds the value to the nearest penny. For example, if cell A1 contains $12.421, the formula returns $12.42:
=ROUND(A1,2)
If you need to round the value up to the nearest penny, use the CEILING function. The following formula rounds the value in cell A1 up to the nearest penny. For example, if cell A1 contains $12.421, the formula returns $12.43:
=CEILING(A1,0.01)
To round a dollar value down, use the FLOOR function. The following formula, for example, rounds the dollar value in cell A1 down to the nearest penny. If cell A1 contains $12.421, the formula returns $12.42:
=FLOOR(A1,0.01)
To round a dollar value up to the nearest nickel, use this formula:
=CEILING(A1,0.05)
You’ve probably noticed that many retail prices end in $0.99. If you have an even-dollar price and you want it to end in $0.99, just subtract .01 from the price. Some higher-ticket items are always priced to end with $9.99. To round a price to the nearest $9.99, first round it to the nearest $10.00 and then subtract a penny. If cell A1 contains a price, use a formula like this to convert it to a price that ends in $9.99:
=(ROUND(A1/10,0)*10)-0.01
For example, if cell A1 contains $345.78, the formula returns $349.99.
A simpler approach uses the MROUND function:
=MROUND(A1,10)-0.01
Working with fractional dollars
The DOLLARFR and DOLLARDE functions are useful when working with fractional dollar values, as in stock market quotes.
Consider the value $9.25. You can express the decimal part as a fractional value ($9 1/4, $9 2/8, $9 4/16, and so on). The DOLLARFR function takes two arguments: the dollar amount and the denominator for the fractional part. The following formula, for example, returns 9.1 (and is interpreted as “nine and one-quarter”):
=DOLLARFR(9.25,4)
This formula returns 9.2 (interpreted as “nine and two-eighths”):
=DOLLARFR(9.25,8)
The DOLLARDE function converts a dollar value expressed as a fraction to a decimal amount. It also uses a second argument to specify the denominator of the fractional part. The following formula, for example, returns 9.25:
=DOLLARDE(9.1,4)
Using the INT and TRUNC functions
On the surface, the INT and TRUNC functions seem similar. Both convert a value to an integer. The TRUNC function simply removes the fractional part of a number. The INT function rounds a number down to the nearest integer, based on the value of the fractional part of the number.
In practice, INT and TRUNC return different results only when using negative numbers. For example, the following formula returns –14.0:
=TRUNC(-14.2)
The next formula returns –15.0 because –14.3 is rounded down to the next lower integer:
=INT(-14.2)
The TRUNC function takes an additional (optional) argument that’s useful for truncating decimal values. For example, the formula that follows returns 54.33 (the value truncated to two decimal places):
=TRUNC(54.3333333,2)
Rounding to an even or odd integer
The ODD and EVEN functions are provided when you need to round a number up to the nearest odd or even integer. These functions take a single argument and return an integer value. The EVEN function rounds its argument up to the nearest even integer. The ODD function rounds its argument up to the nearest odd integer. Table 10.3 shows some examples of these functions.
Table 10.3 Results Using the EVEN and ODD Functions
| Number | EVEN Functiwon | ODD Function |
| –3.6 | –4 | –5 |
| –3.0 | –4 | –3 |
| –2.4 | –4 | –3 |
| –1.8 | –2 | –3 |
| –1.2 | –2 | –3 |
| –0.6 | –2 | –1 |
| 0.0 | 0 | 1 |
| 0.6 | 2 | 1 |
| 1.2 | 2 | 3 |
| 1.8 | 2 | 3 |
| 2.4 | 4 | 3 |
| 3.0 | 4 | 3 |
| 3.6 | 4 | 5 |
Rounding to n significant digits
In some cases, you may need to round a value to a particular number of significant digits. For example, you might want to express the value 1,432,187 in terms of two significant digits: that is, as 1,400,000. The value 9,187,877 expressed in terms of three significant digits is 9,180,000.
If the value is a positive number with no decimal places, the following formula does the job. This formula rounds the number in cell A1 to two significant digits. To round to a different number of significant digits, replace the 2 in this formula with a different number:
=ROUNDDOWN(A1,2-LEN(A1))
For nonintegers and negative numbers, the solution gets a bit trickier. The formula that follows provides a more general solution that rounds the value in cell A1 to the number of significant digits specified in cell A2. This formula works for positive and negative integers and nonintegers:
=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))
For example, if cell A1 contains 1.27845 and cell A2 contains 3, the formula returns 1.28000 (the value, rounded to three significant digits).
Solving Right Triangles
A right triangle has six components: three sides and three angles. Figure 10.3 shows a right triangle with its various parts labeled. Angles are labeled A, B, and C; sides are labeled Hypotenuse, Base, and Height. Angle C is always 90 degrees (or PI/2 radians). If you know any two of these components (excluding Angle C, which is always known), you can use formulas to solve for the others.
Figure 10.3 A right triangle’s components.
The Pythagorean theorem states that
Height^2 + Base^2 = Hypotenuse^2
Therefore, if you know two sides of a right triangle, you can calculate the remaining side. The formula to calculate a right triangle’s height (given the length of the hypotenuse and base) is as follows:
=SQRT((hypotenuse^2) - (base^2))
The formula to calculate a right triangle’s base (given the length of the hypotenuse and height) is as follows:
=SQRT((hypotenuse^2) - (height^2))
The formula to calculate a right triangle’s hypotenuse (given the length of the base and height) is as follows:
=SQRT((height^2)+(base^2))
Other useful trigonometric identities are
SIN(A) = Height/Hypotenuse SIN(B) = Base/Hypotenuse COS(A) = Base/Hypotenuse COS(B) = Height/Hypotenuse TAN(A) = Height/Base SIN(A) = Base/Height
If you know the height and base, you can use the following formula to calculate the angle formed by the hypotenuse and base (angle A):
=ATAN(height/base)
The preceding formula returns radians. To convert to degrees, use this formula:
=DEGREES(ATAN(height/base))
If you know the height and base, you can use the following formula to calculate the angle formed by the hypotenuse and height (angle B):
=PI()/2-ATAN(height/base)
The preceding formula returns radians. To convert to degrees, use this formula:
=90-DEGREES(ATAN(height/base))
Figure 10.4 shows a workbook containing formulas to calculate the various parts of a right triangle.
Figure 10.4 This workbook is useful for working with right triangles.
Area, Surface, Circumference, and Volume Calculations
This section contains formulas for calculating the area, surface, circumference, and volume for common two- and three-dimensional shapes.
Calculating the area and perimeter of a square
To calculate the area of a square, square the length of one side. The following formula calculates the area of a square for a cell named side:
=side^2
To calculate the perimeter of a square, multiply one side by 4. The following formula uses a cell named side to calculate the perimeter of a square:
=side*4
Calculating the area and perimeter of a rectangle
To calculate the area of a rectangle, multiply its height by its base. The following formula returns the area of a rectangle, using cells named height and base:
=height*base
To calculate the perimeter of a rectangle, multiply the height by 2 and then add it to the width multiplied by 2. The following formula returns the perimeter of a rectangle, using cells named height and width:
=(height*2)+(width*2)
Calculating the area and perimeter of a circle
To calculate the area of a circle, multiply the square of the radius by (π). The following formula returns the area of a circle. It assumes that a cell named radius contains the circle’s radius:
=PI()*(radius^2)
The radius of a circle is equal to one-half of the diameter.
To calculate the circumference of a circle, multiply the diameter of the circle by (π). The following formula calculates the circumference of a circle using a cell named diameter:
=diameter*PI()
The diameter of a circle is the radius times 2.
Calculating the area of a trapezoid
To calculate the area of a trapezoid, add the two parallel sides, multiply by the height, and then divide by 2. The following formula calculates the area of a trapezoid, using cells named parallel side 1, parallel side 2, and height:
=((parallel side 1+parallel side 2)*height)/2
Calculating the area of a triangle
To calculate the area of a triangle, multiply the base by the height and then divide by 2. The following formula calculates the area of a triangle, using cells named base and height:
=(base*height)/2
Calculating the surface and volume of a sphere
To calculate the surface of a sphere, multiply the square of the radius by (π) and then multiply by 4. The following formula returns the surface of a sphere, the radius of which is in a cell named radius:
=PI()*(radius^2)*4
To calculate the volume of a sphere, multiply the cube of the radius by 4 times (π) and then divide by 3. The following formula calculates the volume of a sphere. The cell named radius contains the sphere’s radius:
=((radius^3)*(4*PI()))/3
Calculating the surface and volume of a cube
To calculate the surface area of a cube, square one side and multiply by 6. The following formula calculates the surface of a cube using a cell named side, which contains the length of a side of the cube:
=(side^2)*6
To calculate the volume of a cube, raise the length of one side to the third power. The following formula returns the volume of a cube, using a cell named side:
=side^3
Calculating the surface and volume of a rectangular solid
The following formula calculates the surface of a rectangular solid using cells named height, width, and length:
=(length*height*2)+(length*width*2)+(width*height*2)
To calculate the volume of a rectangular solid, multiply the height by the width by the length:
=height*width*length
Calculating the surface and volume of a cone
The following formula calculates the surface of a cone (including the surface of the base). This formula uses cells named radius and height:
=PI()*radius*(SQRT(height^2+radius^2)+radius)
To calculate the volume of a cone, multiply the square of the radius of the base by (π), multiply by the height, and then divide by 3. The following formula returns the volume of a cone, using cells named radius and height:
=(PI()*(radius^2)*height)/3
Calculating the volume of a cylinder
To calculate the volume of a cylinder, multiply the square of the radius of the base by (π) and then multiply by the height. The following formula calculates the volume of a cylinder, using cells named radius and height:
=(PI()*(radius^2)*height)
Calculating the volume of a pyramid
Calculate the area of the base, multiply by the height, and then divide by 3. This formula calculates the volume of a pyramid. It assumes cells named width (the width of the base), length (the length of the base), and height (the height of the pyramid).
=(width*length*height)/3
Solving Simultaneous Equations
This section describes how to use formulas to solve simultaneous linear equations. The following is an example of a set of simultaneous linear equations:
3x + 4y = 8 4x + 8y = 1
Solving a set of simultaneous equations involves finding the values for x and y that satisfy both equations. For this set of equations, the solution is as follows:
x = 7.5 y = –3.625
The number of variables in the set of equations must be equal to the number of equations. The preceding example uses two equations with two variables. Three equations are required to solve for three variables (x, y, and z).
The general steps for solving a set of simultaneous equations follow. See Figure 10.5, which uses the equations presented at the beginning of this section.
-
Express the equations in standard form. If necessary, use simple algebra to rewrite the equations such that all the variables appear on the left side of the equal sign. The two equations that follow are identical, but the second one is in standard form:
3x –8 = –4y 3x + 4y = 8
- Place the coefficients in an n × n range of cells, where n represents the number of equations. In Figure 10.5, the coefficients are in the range I2:J3.
- Place the constants (the numbers on the right side of the equal sign) in a vertical range of cells. In Figure 10.5, the constants are in the range L2:L3.
-
Use an array formula to calculate the inverse of the coefficient matrix. In Figure 10.5, the following array formula is entered into the range I6:J7. (Remember to press Ctrl+Shift+Enter to enter an array formula, and omit the curly brackets.)
{=MINVERSE(I2:J3)} -
Use an array formula to multiply the inverse of the coefficient matrix by the constant matrix. In Figure 10.5, the following array formula is entered into the range J10:J11. This range holds the solution:
{=MMULT(I6:J7,L2:L3)}
Figure 10.5 Using formulas to solve simultaneous equations.
Working with Normal Distributions
In statistics, a common topic is the normal distribution, also known as a bell curve. Excel has several functions designed to work with normal distributions. This is not a statistics book, so we assume that if you’re reading this section, you’re familiar with the concept.
Figure 10.6 shows a workbook containing formulas that generate the two charts: a normal distribution and a cumulative normal distribution.
Figure 10.6 Using formulas to calculate a normal distribution and a cumulative normal distribution.
The formulas use the values entered into cell B1 (name Mean) and cell B2 (named SD, for standard deviation). The values calculated cover the mean plus/minus three standard deviations. Column A contains formulas that generate 25 equally spaced intervals, ranging from –3 standard deviations to +3 standard deviations.
A normal distribution with a mean of 0 and a standard deviation of 1 is known as the “standard normal distribution.” The worksheet is set up to work with any mean and any standard deviation.
Formulas in column B calculate the height of the normal curve for each of the 25 values in column A. Cell B5 contains this formula, which is copied down the column:
=NORM.DIST(A5,Mean,SD,FALSE)
The formula in cell C5 is the same, except for the last argument. When that argument is TRUE, the function returns the cumulative probability:
=NORMDIST(A5,Mean,SD,TRUE)
Figure 10.7 shows a worksheet with 2,600 data points in column A (named Data) that is approximately normally distributed. Formulas in column D calculate some basic statistics for this data: N (the number of data points), the minimum, the maximum, the mean, and the standard deviation.
Figure 10.7 Comparing a set of data with a normal distribution.
Column F contains an array formula that creates 25 equal-interval bins that cover the complete range of the data. It uses the technique described in Chapter 7, “Counting and Summing Techniques.” The multicell array formula, entered in F2:F26 (named Bins), is
=MIN(Data)+(ROW(INDIRECT("1:25"))*(MAX(Data)-MIN(Data)+1)/25)-1
Column G also contains a multicell array formula that calculates the frequency for each of the 25 bins:
=FREQUENCY(Data,Bins)
The data in column G is displayed in the chart as columns and uses the axis on the left.
Column H contains formulas that calculate the value of the theoretical normal distribution, using the mean and standard deviation of the Data range.
The formula in cell H2, which is copied down the column, is
=NORMDIST(F2,$D$5,$D$6,FALSE)
In the chart, the values in column H are plotted as a line and use the right axis. As you can see, the data conforms fairly well to the normal distribution. There are goodness-of-fit tests to determine the “normality” of a set of data samples, but that’s beyond the scope of this book.