How to use the SUMSQ function
What is the SUMSQ function?
The SUMSQ function calculates the sum of the squares of the arguments.
1. Introduction
What is the square?
In mathematics, the square of a number is the result of multiplying the number by itself. Squaring a number is denoted by x2. For example, 52 means 5 * 5 = 25.
Squaring a number results in a positive result as negative signs are removed in multiplication. (-5)2 = 25 Squares grow very rapidly. Already x2 reaches large values quickly as x increases, higher powers grow even faster.
The square root is the inverse operation of squaring. √25 = 5, since squaring 5 gives 25. Squaring is used in geometry to calculate area of squares, side length squared = area. Squaring is also used when calculating the area of a circle.
For example, A = π r²
A is the area.
r is the radius.
π is pi.
The graph of y = x2 is a parabola, symmetric about the origin.
Squares appear frequently in equations in physics, math, statistics, and other fields.
What is the sum?
Calculating a sum involve adding the numbers together creating a total. Summation combines multiple addends into a total amount. For example, the sum of 2, 3, and 5 is 2 + 3 + 5 = 10.
The numbers added can be positive or negative, resulting in a net sum. For example, the sum of -5, -2, and 5 is -2
Sums can be written using the Greek capital sigma ∑ notation to compactly denote repeated addition. ∑ provides a shorthand for writing out sequences of numbers being added together.
What is the sum of squares?
The sum of squares is the result obtained when you take a set of numbers, square each number, and then sum the squares.
If the numbers are x1, x2, ..., xn, the sum of squares is denoted:
∑(xi2) = x12 + x22 + ... + xn2
The sum of square are used in statistics formulas like sample variance, appears in optimization methods like least squares regression and has applications in numerical integration formulas.
Can be computed iteratively or with summation notation.
The sum of square are related to the square of sums, (∑xi)2
2. Syntax
SUMSQ(number1, [number2], ...)
| number1 | Required. The argument for which you want the sum of the squares. |
| [number2] | Optional. Up to 253 additional number arguments. |
3. Example 1
Square the following values and then add them: 2, 3, and 4?
Formula in cell D3:
The formula in cell D3 returns 29, lets calculate this manually and see if this is correct. The first step is to square all the numbers:
22 = 4
32 = 9
42 = 16
The next step is to calculate the total:
4+9+16 = 29
This sum is the same value as in cell D3.
4. Example 2
Calculate the hypotenuse in a right triangle if leg 1 is 6 units and leg 2 is 8 units?
What we know:
- A right triangle enables us to calculate the hypotenuse using the Pythagorean theorem:
c = √(a2 + b2) - c = hypotenuse
- a = leg 1 = 6 units
- b = leg 2 = 8 units
Formula in cell C5:
The formula returns 10 units in cell C5. Here is how the formula works:
- Square the numbers and then add them. SUMSQ function.
- Calculate the square root to determine the length of the hypotenuse. SQRT function.
Lets calculate the hypotenuse value manually:
c = √(a2 + b2)
- Square value 6. 62 = 6 * 6 = 36
- Square value 8. 82 = 8 * 8 = 64
- Add values. 36 + 64 = 100.
- Square root the total. √100 = 10
10 is also the calculated value in cell C5.
5. Example 3
You have a dataset containing the weight of a specific vegetable in a study. Calculate the population standard deviation of the weight values to understand the spread of weight across the population of vegetables?
Here are the data points: 40, 76, 60, 31, 27, 42, 60, 59, 42, and 43
What we know:
The formula for calculating the standard deviation of a population is:
σ = √(∑(x - μ)²) / N
- x are data points
- μ is the average of the data points
- N is the total number of data points
Formula in cell E15:
This formula returns 14.44 which represents the standard deviation for a population. This is what the formula calculates in greater detail:
standard deviation for the population (σ) = √(∑(x - μ)²) / N
- Calculate the average of all data points (μ).
AVERAGE(B16:B25) - Subtract each data point with the average (x - μ).
B16:B25-AVERAGE(B16:B25) - Square the difference and then add the numbers (∑(x - μ)²(x - μ)²).
SUMSQ(B16:B25-AVERAGE(B16:B25)) - Divide the total with the number of data points ((∑(x - μ)²) / N).
SUMSQ(B16:B25-AVERAGE(B16:B25))/ROWS(B16:B25) - Square the ratio (√(∑(x - μ)²) / N).
SQRT(SUMSQ(B16:B25-AVERAGE(B16:B25))/ROWS(B16:B25))
Lets calculate the standard deviation manually:
The average is calculated like this:
μ = ∑xᵢ / n
-
- ∑ (sigma) represents the sum
- i is the index that goes from 1 to n
- n is the total number of values
The total of 40, 76, 60, 31, 27, 42, 60, 59, 42, and 43 is 480.
480/10 = 48
Subtract each data point with the average:
40 - 48 = -8
76 - 48 = 28
60 - 48 = 12
31 - 48 = -17
27 - 48 = -21
42 - 48 = -6
60 - 48 = 12
59 - 48 = 11
42 - 48 = -6
43 - 48 = -5
Square the difference and then add the numbers
-8² = 64
28² = 784
12² = 144
-17² = 289
-21² = 441
-6² = 36
12² = 144
11² = 121
-6² = 36
-5² = 25
64 + 784 + 144 + 289 + 441 + 36 + 144 + 121 + 36 + 25 = 2084
Divide the total with the number of data points
2084 / 10 = 208.4
Square the ratio
√208.4 = 14.4360659460949
Excel has dedicated formulas for calculating the standard deviation: STDEV.S and STDEV.P functions. These functions are easier to work with than the formula I created above.
6. Example 4
An electrical engineer is measuring the voltage of an AC circuit over time. They take four voltage readings at different points in the cycle: 120V, -170V, 220V, and -90V. What is the root mean square (RMS) voltage of this AC signal?
What we know:
- The root mean square math formula is:
RMS = √[(x12 + x22 + ... + xn2) / n]
Formula in cell D19:
The formula returns 157.96 V which represents the root mean square voltage of 120V, -170V, 220V, and -90V.
Lets calculate this value manually. Lets begin squaring the values:
120² = 14400
-170² = 28900
220² = 48400
-90² = 8100
Add the values:
14400 + 28900 + 48400 + 8100 = 99800
Divide the result with the number of values:
99800 / 4 = 24950
Calculate the square root of the ratio:
√24950 = 157.955689989313
Explaining formula in cell D19
Step 1 - Square the numbers and then add them
SUMSQ(B19:B22) returns 99800
Step 2 - Count the number of values and then calculate the ratio
The ROWS function calculate the number of rows in a cell range.
Function syntax: ROWS(array)
SUMSQ(B19:B22)/ROWS(B19:B22)
becomes
99800 / 4 = 24950
Step 3 - Square root the ratio
SQRT(SUMSQ(B19:B22)/ROWS(B19:B22))
becomes
SQRT(24950) and returns 157.955689989313
Functions in 'Math and trigonometry' category
The SUMSQ function function is one of 62 functions in the 'Math and trigonometry' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form