Fabulous Examples of Excel Functions:
Power and Square Root Functions
This Keynote Support tutorial teaches the Microsoft Excel POWER function and how it can include math calculations and other functions. It also teaches the SQRT function - giving plenty of examples and discussing how to use the SQRT function with decimals and rounding.
A related function, SUMSQ, is covered in a separate tutorial Microsoft Excel: The SUMSQ Function.
Author: Keynote Support
For an excellent video-based learning course on Excel, check out Advanced Microsoft Excel 2010 Training. We've reviewed this course and find it very well done and a good value. A beginner's course is also available, along with free online tutorials.
Before we begin, don't forget that every Microsoft Excel function begins with an equal = sign unless the function is imbedded inside of another function. Now - click on a link to go directly to that function.
POWER
The POWER function returns the result of a number raised to a power. The syntax of the POWER function is:
POWER(base,power)
… where "base" is the base number and can be any real number, and "power" (or exponent) is the number to which the base number is "raised" — or multiplied by itself.
There is a special symbol in Excel that stands for power (or exponent) for simple formulas: the caret (^) - discussed in Excel Math Basics: A Beginner's Guide. The POWER function can be used for simple expressions as well, but is invaluable when the base or power arguments contain formulas or other functions.
As a review, the number 2 raised to the "second power" or "squared" would be written 2² = 2x2 = 4. Likewise, 2 raised to the third power, or "cubed," would be 2³ = 2x2x2 = 8, and the POWER function would be:
=POWER(2,3) This Excel function returns the number 2 raised to the third power.
Look at the sample Excel worksheet below as we illustrate more complex POWER functions that contain formulas and other Excel functions.
=POWER(C2,C3) This Excel function is identical to our example above except we find the base number 2 and the power 3 in cells C2 and C3. Excel raises 2 to the 3rd power (2x2x2) and returns a value of 8 as you can see in column 1 of the worksheet.
=POWER(C5,C6) This Excel function illustrates that the base and power do not have to be integers. Excel raises the contents of cell C5 (2.114) to the power of 3.2 (contents of cell C6) and displays 10.97 because we have formatted the cell containing the function to display a number with 2 decimal places. The actual value Excel stores in the worksheet is 10.9732669744184.
=POWER(C2,C4/C3) This Excel function contains a formula for the power. Excel raises the number 2 to the power of 3 (9 ÷ 3) and returns a value of 8.
=POWER(MIN(C2:C6),C2*C3) This function illustrates how other Excel functions can be included in the POWER function. Excel first determines the smallest number in cells C2 through C6 (2). The power argument contains a formula: 2x3 = 6, so Excel raises 2 to the 6th power and returns a result of 64 (2x2x2x2x2x2 = 64). For more information about the MIN function, see our tutorial Microsoft Excel Functions: AVERAGE, MEDIAN, MODE, MAX, and MIN.
=SQRT
To find the square root of the contents of a cell, use the Excel function SQRT. The square root of a number (e.g. X) is whatever number, when multiplied by itself, equals that number (X). The syntax of the SQRT function is:
SQRT(number)
… where number is the number for which you want Excel to calculate the square root. It can be any positive number, or an Excel formula or function which provides a positive number. The square root Excel returns will always be a positive number.
SQRT(16) This Excel function determines the square root of 16 and returns a value of 4 because 4² = 16.
Look at the sample Excel worksheet below as we illustrate more complex SQRT functions that contain formulas and other Excel functions.
=SQRT(C2*C4) This Excel function determines the square root of 4x9, which is 36, and therefore returns a value of 6 because 6²=36.
=SQRT(C3) Why has Excel returned the #NUM! error value? Because C3 contains a negative number and negative numbers cannot have square roots.
=SQRT(ABS(C3)) This Excel function first determines the absolute value of C3 (25), and then returns a value of 5 because 5² = 25.
What is an Absolute Value? It is a number without its positive or negative sign. The Absolute Value of a number IS just the number portion.
Using SQRT with Decimals and the ROUND function
Few numbers, like 9, 16, 25, etc., have a square root that is an integer. Therefore, make sure you format any cell containing a square root with ample decimal places, or indicate somewhere on the worksheet that the square root has been rounded to a particular decimal point.
If your worksheet calculates square roots with decimals that you subsequently use in other functions or formulas, your spreadsheet may appear to contain errors. Take a look at the example below and how we used the ROUND function within the SQRT function to prevent this:
=SQRT(C2) This first Excel function entered into cell A2 determines the square root of 6.23 and returns a value of 2.50. Cell A2, which contains the function, was formatted to display the answer as a number with 2 decimal places.
=2*SQRT(C2) This Excel function, entered into cell A3, calculates the square root of C2 and multiplies the answer by 2 … and Excel returns a value of 5.00, right? Wrong! Why is 4.99 displayed?
The actual square root of 6.23, calculated and stored in cell A2, is 2.49599679486974. But we formatted the cell to display a number with 2 decimal places, so Excel displays 2.50.
When subsequently, we ask Excel to multiply the square root of 6.23 by 2, it uses the real value, gets an answer of 4.99199358973947, and displays 4.99 in the formatted cell.
And now it looks as though the spreadsheet contains an error. This misconception can be easily avoided by using the ROUND function inside of the SQRT function as shown in Row 4 above, and discussed below:
=2*ROUND(SQRT(C2),2) This Excel function determines the square root of cell C2, rounds the answer to 2 decimal places (2.50), and then multiplies this rounded number by 2 - returning a value of 5.00. For more information about the ROUND function, see Microsoft Excel: The ROUND Function.
Final Thoughts
It is recommended to lock any cell containing a function or formula, and protect the worksheet to prevent accidental erasures. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.
A related function involving the sum of the squares of numbers is the SUMSQ function. We have provided a separate tutorial for this function: Microsoft Excel: The SUMSQ Function.
We hope this article has been helpful. Cheers!
Other Popular Tutorials
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-functions-power-squareroot.shtml">Fabulous Examples of Excel Functions: POWER and SQRT</a>
Return to the top of Fabulous Examples of Excel Functions: POWER and SQRT
