Fabulous Examples of Excel Functions: SUMSQ
This Keynote Support tutorial teaches the Microsoft Excel SUMSQ function - an Excel built-in function that calculates the sum of the squares. This tutorial also illustrates how the SUMSQ function can be used in conjunction with formulas and other Excel functions. Examples on a sample spreadsheet help the student better understand the use of this function.
Author: Keynote Support
The related functions of POWER and SQRT are covered in a separate tutorial Microsoft Excel: Power and Square Root Functions.
If you are new to Excel, we recommend the video-based course Excel 2010 Tutorial for Beginners. We have this course (over 9 hours of hands-on lessons) and find it well done and a good value. An advanced Excel course is also available, along with free, online video tutorials.
Don't forget that every Excel function begins with an equal = sign unless the function is imbedded inside of another function. Now - let's begin!
The SUMSQ function returns the sum of the squares of the arguments listed in the function. The syntax of the SUMSQ function is:
where number1, number2, are from 1 to 255 arguments for which Excel will calculate the sum of their squares. Squaring a number is multiplying that number by itself.
The argument can be a number; or a cell reference, formula, or function that provides a number to the SUMSQ function. Only numbers in an array are counted. Text, error values, empty cells, and logical values are ignored.
If an argument does not lead to a number or results in an error value, the function itself may fail.
=SUMSQ(2,3) This Excel function returns the sum of the square of 2 (2² = 4) and 3 (3² = 9) and returns a value of 13 (4 + 9 = 13).
Basic Examples of SUMSQ
Look at the sample Excel worksheet below as we illustrate more complex SUMSQ functions that contain formulas and other Excel functions.
=SUMSQ(C2,C3) This Excel function is identical to our example above except that the function references cells that contain the numbers 2 and 3. Excel returns a value of 13.
=SUMSQ(C2+C3) This Excel function squares a number that is first derived from a formula. This function squares the sum of the numbers in cells C2 and C3: 2+3=5, and 5x5=25. This function contains only one argument.
=SUMSQ(-C2/C4) This Excel function returns the sum of the square of -2 divided by 4. First Excel completes the division: -2/4 = -.5. Then Excel squares -.5 and returns a value of 0.25. (The product of two negative numbers is always a positive number.)
=SUMSQ(C2:C5) This Excel function sums the squares of the numbers in cells C2 through C5. The squares are: 2x2=4, 3x3=9, 4x4=16, and 5x5=25. Then Excel adds these squares for a value of 54 (4+9+16+25=54).
Advanced Examples of SUMSQ
Any argument of the SUMSQ function can contain a formula or another function as long as the formula or function results in a number. Here are some more complex examples of the SUMSQ function.
=SUMSQ(C2+C3,C4) This Excel function displays a math formula for the first argument, so Excel first adds the contents of cells C2 and C3 and squares the sum (2 + 3 = 5, and 5² = 25). Then Excel adds that value to the square of C4, which is 16. Lastly, Excel sums the squares and returns a value of 41 (25 +16 = 41).
=SUMSQ(PRODUCT(C2,C3),PRODUCT(C4,C5)) This Excel function illustrates that other functions can reside inside of the SUMSQ function. This Excel function sums the squares of two products. First, Excel multiples 2x3 and squares the result (2 x 3 = 6, and 6² = 36). Then Excel squares the product of 4x5 and squares the result (4 x 5 = 20, and 20² = 400). Lastly, Excel sums these squares and returns a value of 436.
=SUMSQ(IF(C5>C2,10,0),C3) This Excel function also illustrates that other functions can reside inside of the SUMSQ function. The IF function tells Excel that if the expression C5>C2 is true, the first number listed, 10, should be used. The expression is true, so Excel squares 10 for a value of 100.
Next, Excel squares the value of cell C3 (3² = 9). Lastly, Excel sums the squares and returns a value of 109 (100 + 9 = 109).
If the value in cell C5 had NOT been greater than the value in C2, the result of the IF function would have been zero (0). And zero squared = zero. (Don't miss our excellent tutorial IF and Nested IFs.)
Don't forget that each Excel function begins with an equal = sign unless the function is imbedded inside of another function. In addition, it is advisable to lock any cell containing a formula or function, and then protect the worksheet to prevent accidental erasure. Step-by-step directions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide - "Locking Cells and Protecting Worksheets."
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-functions-sumsq.shtml">Fabulous Examples of Excel Functions: SUMSQ</a>
Return to the top of Fabulous Examples of Excel Functions: the SUMSQ Function