DEVSQ Function

Calculates the sum of the squared deviations from the sample mean

Sum of Squared Deviations in Excel

The DEVSQ Function is categorized under Statistical functions. It will calculate the sum of the squared deviations from the sample mean. The function, for example, can be used to find the variation in temperatures, blood pressure readings, etc.

As a financial analyst, DEVSQ is useful in understanding the variance in daily stock values that can signify market instability and higher risks for investors.

When we take the square root of the sum of squares, we get the standard deviation, an even more useful number. The variance and standard deviation functions deal with negative deviations by squaring deviations before they are averaged. DEVSQ calculates the sum of the squared deviations from the mean, without dividing by N or by N-1.

 

Formula

=DEVSQ(number1, [number2], …)

 

The DEVSQ function uses the following arguments:

  1. Number1 (required argument) – It is the value for which we wish to calculate the sum of squared deviations.
  2. Number2 (optional argument)

 

The arguments can be between 1 to 255. However, in Excel 2003, the DEVSQ function can only accept up to 30 number arguments. We can use a single array or a reference to an array instead of arguments separated by commas.

The equation for the sum of squared deviations is:

 

DEVSQ Formula

 

To learn more, launch our free Excel crash course now!

 

How to use the DEVSQ Function in Excel?    

To understand the uses of the DEVSQ function, let us consider an example:

 

Example

Suppose we are given the following data:

 

DEVSQ Function

 

The formula to use for calculating the sum of the squared deviations is:

 

DEVSQ - Example 1

 

We get the result below:

 

DEVSQ - Example 1a

 

Things to remember about the DEVSQ Function

  1. #VALUE! error – Occurs when any of the number arguments that are provided directly to the DEVSQ function are text strings that cannot be interpreted as numbers.
  2. #NUM! error – Occurs when none of the values supplied to the DEVSQ function is numeric. Text representations of numbers that are provided as a part of an array are not interpreted as numeric values by the function.
  3. For DEVSQ, arguments can be numbers, names, arrays, or references that contain numbers.
  4. Empty cells and cells that contain text or logical values are ignored by the function. Hence, they will not be included in the calculation.

 

Free Excel Course

To keep learning about Excel functions and developing your skills, check our Free Excel Crash Course! Learn how to create more sophisticated financial analysis and modeling to become a successful financial analyst.

 

free Excel keyboard shortcuts course

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!