DEVSQ Function

Used to take a dataset's sample mean to calculate the squares' deviation.

Author: Christy Grimste
Christy Grimste
Christy Grimste
Real Estate | Investment Property Sales

Christy currently works as a senior associate for EdR Trust, a publicly traded multi-family REIT. Prior to joining EdR Trust, Christy works for CBRE in investment property sales. Before completing her MBA and breaking into finance, Christy founded and education startup in which she actively pursued for seven years and works as an internal auditor for the U.S. Department of State and CIA.

Christy has a Bachelor of Arts from the University of Maryland and a Master of Business Administrations from the University of London.

Reviewed By: Elliot Meade
Elliot Meade
Elliot Meade
Private Equity | Investment Banking

Elliot currently works as a Private Equity Associate at Greenridge Investment Partners, a middle market fund based in Austin, TX. He was previously an Analyst in Piper Jaffray's Leveraged Finance group, working across all industry verticals on LBOs, acquisition financings, refinancings, and recapitalizations. Prior to Piper Jaffray, he spent 2 years at Citi in the Leveraged Finance Credit Portfolio group focused on origination and ongoing credit monitoring of outstanding loans and was also a member of the Columbia recruiting committee for the Investment Banking Division for incoming summer and full-time analysts.

Elliot has a Bachelor of Arts in Business Management from Columbia University.

Last Updated:December 3, 2023

What is the DEVSQ Function?

The DEVSQ function, most frequently used in Microsoft Excel, is a formula used to take the sample mean of a dataset to calculate the deviation (DEV) of the squares (SQ).

In some cases, like finding the total sum of the deviations of squares, it would be crucial to use the DEVSQ function. However, in other cases that may not need the variables for calculation, the DEVSQ function is unreliable.

Another commonly used function is the SUMIFS function in Excel if you are looking to find the total sum of a given dataset.

The SUMIFS function can be located under the Math & Trig tab or simply inputting “=SUMIFS.”

The article will continue to discuss the function's formula, a component breakdown of the formula, and an Excel sheet example.

The example will detail a scenario using the DEVSQ function and a follow-through series in navigating the function.

    Key Takeaways

    • The DEVSQ function calculates the sum of the squared deviation.
    • Another similar function that calculates the sum of variables is the SUMIF function.
    • The DEVSQ function only includes numerical values, and at least one numerical set is required.
    • You can locate the DEVSQ function under Formulas → Statistical → DEVSQ.
    • The DEVSQ function does not calculate non-numerical values like text.

    DEVSQ Function Formula

    The formula required in calculating the squares deviation is as follows:

     DEVSQ(number1, [number2])

    Further breaking down the DEVSQ formula, let’s take a closer look at the components that formulate the calculation.

    First, the “number1” variable of the DEVSQ function is the numerical variable of the given dataset. The first variable or number is required as it will help return the intended calculations of the squared deviations.

    Similarly, the “number2” variable is the continuation of the first variable. However, as noted in the formula above, “number2” is an optional input.

    The function must include numerical values, as it calculates the sum of all chosen numbers. The example in the next section will depict how the function is used.

    How to use the DEVSQ Function in Excel?

    In the example below, five values are used to determine the sum of all values presented.

    Values

    In this example, a dataset of six values is inputted into the Excel table to demonstrate how the DEVSQ function can calculate the sum of the given values.

    After all necessary data is inputted into the Excel sheet, you can calculate its sum of squared deviations. Then, to navigate the DEVSQ function, you can simply type in the “Sum of Squared Deviations” table, i.e., “=DEVSQ,” and the function should appear.

    Data

    After double-clicking the DEVSQ function, you may highlight, in this example, the six given values in cells B2 through B7.

    Sheet

    Another way to locate the function is to follow the steps as directed in the instructions:

    • In the Excel sheet, locate the Formulas tab at the top bar. 
    • Click on More Functions.
    • In the dropdown bar, you will see six additional categories. Click on Statistics.
    • Another dropdown bar will appear with functions in alphabetical order. Simply scroll down and locate “DEVSQ.”Excel

    Going back to the example, the highlighted cell C9 returns a sum of the squared deviations to be about 30.83. Also, located in the upper right-hand corner, the DEVSQ function is demonstrated to use the given values.

    The squared deviation function can determine its sum by taking all the dataset values.

    If you receive an error presenting #NUM! or #VALUE!, please consider the following situations:

    • Inputting non-numerical data such as text.
    • Inputting of special characters or letters that do not read as a numerical set.

    Reviewed and edited by Parul Gupta LinkedIn

    Free Resources

    To continue learning and advancing your career, check out these additional helpful WSO resources: