📌 Part 1: Introduce
🔍 Definition The SUMSQ function in Excel returns the sum of the squares of the arguments.
🎯 Purpose The primary purpose of the SUMSQ function is to compute the sum of the squares of given numbers, which can be useful in various mathematical and statistical analyses.
📜 Syntax & Arguments
=SUMSQ(number1, [number2], ...) - number1: Required. The first number you want to square and sum.
- [number2], …: Optional. Additional numbers you want to square and sum.
🔎 Explain the Arguments in function
- number1: This is the primary number for the SUMSQ function. It represents the first value you wish to square and sum.
- [number2], …: You can use additional numbers with SUMSQ. They represent subsequent values you wish to square and sum.
🔙 Return value The SUMSQ function returns the sum of the squares of the specified numbers.
❗ Remarks
- Arguments can be numbers or names, arrays, or references containing numbers.
- Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted.
- If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
📌 Part 2: Examples
🔢 Example 1: Calculating Total Square of Sales and Returns
- 🎯 Purpose: To compute the sum of the squares of sales and returns.
- 📊 Datasheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sales | Returns | SUMSQ Formula | Result |
| 2 | 150 | -20 | =SUMSQ(A2:A4,B2:B4) | 94925 |
| 3 | 200 | -30 | ||
| 4 | 180 | -25 |
- 📝 Explanation: This example demonstrates how to use SUMSQ to calculate the sum of the squares of sales and returns. The function squares each sale and return value and sums them up, resulting in 94,925.
For the formula =SUMSQ(A2:A4,B2:B4), we’ll square each value in the range A2:A4 and B2:B4, then sum them up:
(150^2) + (-20^2) + (200^2) + (-30^2) + (180^2) + (-25^2) = 22500 + 400 + 40000 + 900 + 32400 + 625 = 94925
🔢 Example 2: Calculating Square of Profits and Losses
- 🎯 Purpose: To compute the sum of the squares of monthly profits and losses.
- 📊 Datasheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Profits | Losses | SUMSQ Formula | Result |
| 2 | 300 | -50 | =SUMSQ(A2:A4,B2:B4) | 115025 |
| 3 | 250 | -40 | ||
| 4 | 320 | -35 |
- 📝 Explanation: This example demonstrates how to use SUMSQ to calculate the sum of monthly profits and losses squares. The function squares each profit and loss value and then sums them up, resulting in a total of 115,025.
🔢 Example 3: Calculating Square of Production and Wastage
- 🎯 Purpose: To compute the sum of the squares of production and wastage units.
- 📊 Datasheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Production | Wastage | SUMSQ Formula | Result |
| 2 | 500 | -30 | =SUMSQ(A2:A4,B2:B4) | 270850 |
| 3 | 550 | -25 | ||
| 4 | 480 | -20 |
- 📝 Explanation: This example illustrates how to use SUMSQ to calculate the sum of the squares of production and wastage units. The function squares each production and wastage value and then sums them up, resulting in a total of 270,850.
🔢 Example 4: Calculating Square of Inventory and Damages
- 🎯 Purpose: To compute the sum of inventory and damaged items squares.
- 📊 Datasheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Inventory | Damages | SUMSQ Formula | Result |
| 2 | 400 | -15 | =SUMSQ(A2:A4,B2:B4) | 162650 |
| 3 | 420 | -10 | ||
| 4 | 390 | -5 |
- 📝 Explanation: This example showcases how to use SUMSQ to calculate the sum of the squares of inventory and damaged items. The function squares each inventory and damage value and then sums them up, resulting in 162,650.
🔢 Example 5: Calculating Square of Orders and Returns
- 🎯 Purpose: To compute the sum of the squares of orders placed and items returned.
- 📊 Datasheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Orders | Returns | SUMSQ Formula | Result |
| 2 | 600 | -40 | =SUMSQ(A2:A4,B2:B4) | 365825 |
| 3 | 620 | -35 | ||
| 4 | 580 | -30 |
- 📝 Explanation: This example demonstrates how to use SUMSQ to calculate the sum of the squares of orders placed and items returned. The function squares each order and return value and then sums them up, resulting in a total of 365,825.
🔢 Example 6: Using SUMSQ with IF for Discounted Items
- 🎯 Purpose: To compute the sum of the squares of discounted items.
- 📊 Datasheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Items | Discount | SUMSQ Formula | Result | Discounted |
| 2 | 100 | 5% | =SUMSQ(IF(D2:D4>0, A2:A4)) | 50500 | Yes |
| 3 | 150 | 0% | No | ||
| 4 | 200 | 10% | Yes |
- 📝 Explanation: This example demonstrates how to use SUMSQ with the IF function to calculate the sum of the squares of items with a discount. The formula checks if there’s a discount and then squares the item value, summing them up to 50,500.
🔢 Example 7: Using SUMSQ with SUM for Total Sales
- 🎯 Purpose: To compute the sum of the squares of sales and then add a bonus.
- 📊 Datasheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sales | Bonus | SUMSQ Formula | Result |
| 2 | 500 | 50 | =SUM(SUMSQ(A2:A4), B2:B4) | 855165 |
| 3 | 550 | 55 | ||
| 4 | 600 | 60 |
- 📝 Explanation: This example showcases how to use SUMSQ with the SUM function to calculate the sum of the squares of sales and then add a bonus to each squared value. The squared sales values are summed up to 855,000, and the bonuses add 165, resulting in a total of 855,165.
📌 Part 3: Tips and tricks
- 🚫 Ensure that the arguments you’re using with SUMSQ are numbers or references that contain numbers.
- ✅ You can nest other functions within SUMSQ for more complex calculations.
- 🔄 When using SUMSQ with arrays or references, ensure they only contain numbers to avoid errors.
- 🔄 Regularly check for updates and additional functionalities Microsoft provides for the SUMSQ function.