PART 2: CALCULATING WITH EXCEL |
139 |
|
|
If you need to find the square root, you can use the SQRT function.
Figure 344 SQRT is a built-in function for square roots.
To calculate a square root, you can raise a number to the one-half (1/2) power. Since (1/2) is a rational number, you could alternatively use =D2^0.5.
Figure 345 Raising to a fraction takes the root.
To find the cube root of a number, you can raise the number to the one-third (1/3) power.
2
Figure 346 For cube roots, raise to the 1/3 power.
To find the fourth root of a number, you raise the number to either the one-fourth (1/4) or 0.25 power.
Figure 347 Raise to the 1/4 power.
You can find any root in the same way: To find the nth root, you simply raise the number to the 1/n power. For example, to find the 17th root of a number, you raise it to the oneseventeenth (1/17) power.
Figure 348 Find the nth root by raising to 1/n.
Although Excel only offers a function for a square root, you can use the technique of raising to a fractional power in order to determine any root of a number.
CALCULATE A GROWTH RATE
Problem: I work for a quickly growing company. In the first year, we had $970,000 in sales. In the fifth year, we had $6,175,000 in sales. I need to determine our compounded annual growth rate.
140 |
POWER EXCEL WITH MR EXCEL |
|
|
Strategy: Sales in the fifth year are 6,175/970 higher than in the first year. The formula for growth is (Year5/Year1) -
100% or 537%.
Figure 349 Five-year growth rate.
However, a compounded growth rate is a number, x, that will calculate like this:
Year1 * (100% + x) * (100% + x) * (100% + x) * (100% + x) = Year5
This is the same as: Year1 * (100% + x)^4 = Year5
So, in order to calculate x, you have to be able to find the fourth root of (Year5/Year1). The formula to find the fourth root is to raise the number to the 1/4 power. Thus, the for- mula to calculate the compounded growth rate is: (Year5/ Year1)^(1/4)-100% = x.
To prove that this formula is working, multiply year 1 by 1.5884235 four times. The answer should be very close to
Year 5.
Figure 350 Compounded growth rate.
Figure 351 Prove that the 58.84% growth rate is accurate.
FIND THE AREA OF A CIRCLE
Problem: I need to order pizza for my department’s staff meeting. The pizza place has two deals. I can buy three medium (12”) pizzas for $18 or two large (16”) pizzas for $20. Which is the better deal?
Strategy: You will have to figure out the area of a 12” pizza vs. the area of a 16” pizza. The formula for the area of a circle is pi * r2 (where r is the radius). The radius of a pizza is one-half the diameter. If you enter the diameter of the pizza in B2, the radius is =B2/2.
PART 2: CALCULATING WITH EXCEL |
141 |
|
|
Pi is a Greek letter that represents 3.141592654. Excel of- fers the PI function to return this number. It is a lot easier to remember =PI() than the many digits in 3.141592654.
Figure 352 =PI() returns the value of pi to 15-digit precision.
Here’s how you determine which is the better pizza deal:
1. Set up a worksheet. In cell B2, enter the diameter of the pizza. 2. In cell C2, calculate the radius as =B2/2.
3. In cell D2, calculate the area of the pizza in square inches, using =PI()*C2^2.
4. |
Figure 353 Area is pi times radius squared. |
|
|
In column E, enter the quantity of pizzas. |
|
||
5. |
Calculate the total square inches in column F by using =E2*D2. |
|
|
2 |
|||
6. |
Enter the cost for the special in column G. In column H, calculate the dollars per square inch of |
||
|
pizza, using =G2/F2. |
|
|
|
|
Figure 354 Cost per square inch.
Results: From a purely mathematical point of view, the special with two large pizzas is a slightly better deal, pricing the pizza at 4.97 cents per square inch.
Additional Details: My eight-grade math teacher, Mr. Nick Irwin, would like me to mention, for the sake of completeness, that the circumference of the pizza is pi times the diameter. That would be =PI()*B2.
FIGURE OUT LOTTERY PROBABILITY
Problem: The Super Lotto jackpot is $8 million this week. Should I play?
Strategy: It depends on how many numbers are in the game. You need to figure out the number of pos- sible combinations in the game.
You can use the COMBIN function as follows to figure out the number of possible combinations for games in which you choose 6 of 40, 44, 48, and so on numbers:
1. Set up a spreadsheet with the number of balls in the lotto game (40, 44, 48, and so on) in cell A2.
2. In cell B2, identify how many numbers you need to select correctly. 3. Enter the formula =COMBIN(A2,B2) in cell C2.
142 |
POWER EXCEL WITH MR EXCEL |
|
|
Figure 355 Combinations of choosing 6 numbers.
If your state lottery game requires you to select 6 numbers out of 40, then the odds against you winning are 3.83 million to 1. For a $1 bet and an $8 million payout, the odds are in your favor.
For a game with 44 numbers, the odds are 7 million to 1. This payoff is only slightly in your favor. For games with 48 or 54 numbers, the payout is not worth the long odds of the game.
Additional Details: COMBIN figures combinations. Here, the sequence in which the balls are drawn is not relevant. If you had a game in which you had to match both the numbers and the order in which they were drawn, you would want to use the PERMUT function to find the number of permutations of drawing 6 numbers in sequence out of 40.
Additional Details: Since the first edition of this book, two multi-state lotteries have become popular in the United States. These require the player to match five numbers from one pool of numbers and then one number from a separate pool of numbers. This means you have to win two drawings to win the jackpot.
Multiply the combinations from the first drawing with the combinations from the second drawing. Here are the calculations for Mega Millions and PowerBall lotteries.
Figure 356 The odds are much higher for these lotteries.
It only makes statistical sense to play the $1 Mega Millions when the jackpot is above $259 million. Because the PowerBall costs $2 to play, it only makes sense when the jackpot is above $350 million. As you can see, lotteries are a tax on people who can’t use Excel.
HELP YOUR KIDS WITH THEIR MATH
Problem: My kids have math homework, and I want to check their answers. They are doing least common multiples, greatest common denominators, Roman numerals, and factorials.
Strategy: You can easily solve problems involving least common multiples, greatest common denomina- tors, roman numerals, and factorials using Excel.
PART 2: CALCULATING WITH EXCEL |
143 |
|
|
Least Common Multiples: When you have to add fractions that have different denominators, one of the first steps is to find the least common multiple of the two denominators. The math homework asks your kids to add 3/26 + 3/4. You want to figure out the least common multiple of 26 and 4, so enter 26 in one cell and 4 in another cell. The formula to find the least common multiple is =LCM(A2:B2). The an- swer is 52. You can now have your kids change 3/26 to 6/52 and 3/4 to 39/52. Expressing the problem as 39/52 + 6/52 makes it easy to see that the answer is 45/52.
Greatest Common Denominators: This time, the problem is
2/9 + 2/4. The LCM of 9 and 4 is 36 as shown in row 3 above. You can change 2/9 to 8/36 and 2/4 to 18/36. The problem then becomes 8/36 + 18/36. The answer is 26/36. However, can the fraction 26/36 be further reduced? You need to find the greatest common denominator of 26 and 36. To do so, you use the GCD function =GCD(A6:B6). Because the an- swer is greater than 1, your 26/36 answer can be reduced by dividing both the numerator and denominator by 2; 26/36 is
the same as 13/18. |
Figure 357 Middle school math. |
|
|
|
|
Roman Numerals: Your kids are supposed to use Roman numerals. To do this, you can use the ROMAN |
2 |
|
function as shown in rows 9:14. |
|
|
|
|
|
The ROMAN function will work with numbers from 1 to 3,999. If you omit an optional second argument, you will get classic Roman numerals, as shown above.
Calculating Roman numerals is fairly obscure. Other than middle school students and Latin teachers, who has to do this? The NFL commissioner needs to calculate future Super Bowl numbers. The people who do movie credits need to figure out the information to use in the copyright line. Excel wasn’t invented when Foreigner IV was released and I somehow doubt that that Holy See fires up Excel when naming the next pope.
If you remember the basics of Roman Numerals, I is 1, V is 5. To show 7, you would use VII. But, to show
4, you would use IV. Since the I occurs before the V, it represents 1 subtracted from 5. Modern convention says that you can represent 4 with IV and 9 with IX, but you can not use IL for 49. The optional second argument of the ROMAN function allows you to break the rules more and more.
Figure 358 Excel offers more concise Roman numerals.
Starting in Excel 2013, you can convert Roman numerals back to regular numbers using the =ARABIC() function. This isn’t a function that Microsoft wanted to add to Excel. But, since they are trying to remain compliant with the Open Document Spreadsheet standard, it was added to Excel 2013.