How to use the Excel DEVSQ function
Just as it is with using other different functions in Excel, there are also ways to use the Excel DEVSQ function. This post will guide you on how to use the Excel DEVSQ function with syntax in Excel.
=DEVSQ ([number1], [number2], [number2] ...)
How the function works
The DEVSQ function in excel calculates the sum of the squares of deviations from mean for a data range without dividing by N-1 or N.
Arguments can be arrays, names, numbers or references that consist of numbers.
- number1 – First value or reference.
- number2 – [optional] Second value or reference.
In figure 1 below, the formula in D13 is:
The formulas in E5 and F5 respectively are:
= D5-$D$12 - DEVIATION
= E5^2 - SQUARED DEVIATION
The sum of F5:F9 in F10 is 1210, which agrees with the value calculated using DEVSQ in D13.
Figure 1: Excel DEVSQ function
- Variance and standard deviation functions deal with the negative deviations by squaring the deviations prior to being average.
- Any empty cell or the one that contains text or logical values will be overlooked.