Abs Function

Purpose

Return value

Syntax

=ABS(number)

• number - The number to get the absolute value of.

Using the ABS function

The ABS function returns the absolute value of a number. You can think about absolute value as a number’s distance from zero on a number line. ABS converts negative numbers to positive numbers. Positive numbers and zero (0) are unaffected.

The ABS function takes just one argument , number , which must be a numeric value. If number is not numeric, ABS returns a #VALUE! error.

Basic example

Negative numbers become positive, while positive numbers and zero (0) are unaffected:

=ABS(-3) // returns 3
=ABS(5) // returns 5
=ABS(0) // returns 0

Absolute Variance

Calculating the variance between two numbers is a common problem. For example, with a forecast value in A1 and an actual value in B1, you might calculate variance like this:

=B1-A1 // negative or positive result

When B1 is greater than A1, variance is a positive number. However, when A1 is greater than B1, the result will be negative. To ensure the result is a positive number, you can use ABS like this:

=ABS(B1-A1)  // ensure positive result

See a detailed example here .

Counting absolute variances with conditions

The ABS function can be used together with the SUMPRODUCT function to count absolute variances that meet specific conditions. For example, to count absolute variances greater than 100, you can use a formula like this:

=SUMPRODUCT(--(ABS(variances)>100))

This formula is explained in more detail here .

Square root of negative number

The SQRT function calculates the square root of a number. If you give SQRT a negative number, it will return a #NUM! error:

=SQRT(-4) // returns #NUM!

To handle a negative number like a positive number, you can use the ABS function like this:

=SQRT(ABS(A1))

Calculating tolerance

To calculate whether a value is within tolerance or not, you can use a formula like this:

=IF(ABS(actual-expected)<=tolerance,"OK","Fail")

See a detailed explanation here .

Purpose

Return value

Syntax

=AGGREGATE(function_num,options,ref1,[ref2],...)

• function_num - Operation to perform (1-19).
• options - Values to ignore (0-7).
• ref1 - First argument.
• ref2 - [optional] Second argument (k).

Using the AGGREGATE function

The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc. performed on one or more references. The AGGREGATE function is like an upgraded version of the older SUBTOTAL function , and provides more calculation options, and more control over ignoring specific things. There are two reasons that make the AGGREGATE function especially useful, compared to other functions which perform the same operations:

1. AGGREGATE has a number of options for ignoring errors, hidden rows, and other functions that may appear in data.
2. AGGREGATE can handle many array operations natively, without Control + Shift + Enter .

AGGREGATE can run a total of 19 functions , and the function to perform is given as a number, which appears as the first argument in the function, function_num . The second argument, options , controls how AGGREGATE handles errors and values in hidden rows. See the table below for all available options.

The AGGREGATE function takes four arguments : function_num , options , ref1 , and ref2 . For the first 13 functions supported, only the first three arguments are required: function_num specifies the operation, options controls various behaviors, and ref1 is the array of values to process.

The last 6 functions require all four arguments: function_num specifies the operation, options controls various behaviors , ref1 is the array of values to process, and ref2 is the “second argument” for the function being called. For example, functions like SMALL and LARGE take a second argument, k , and ref2 represents that argument. The following six functions require the ref2 argument:

LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)

Example #1

To return the MAX value in the range A1:A10, ignoring both errors and hidden rows, provide 4 for function number and 7 for options :

=AGGREGATE(4,7,A1:A10) // max value

To return the MIN value with the same options, change the function number to 5:

=AGGREGATE(5,7,A1:A10) // min value

Example #2

In the example shown above, the formula in D5 is:

=AGGREGATE(4,6,values)

where “values” is the named range B5:B14. The function number is 4, which specifies MAX. Options is provided as 6, to ignore errors only.

Example #3 - nth largest

The formulas in D8:D10 demonstrate how to return “nth largest” values:

=AGGREGATE(14,6,values,1) // 1st largest
=AGGREGATE(14,6,values,2) // 2nd largest
=AGGREGATE(14,6,values,3) // 3rd largest

The function number here is 14, which runs the LARGE function . Because the LARGE function requires a k argument, it appears as the last argument in the three formulas above.

Example #4 - array operation

What makes AGGREGATE especially useful for more complex formulas is that it can handle arrays natively when the function number is 14-19. For example, to find the MAX value on Mondays, with data that includes dates and values, you could use AGGREGATE like this:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Here we specify 14 for function (LARGE) and 6 for option (ignore errors). Then we build a logical expression using the TEXT function to check all dates for Mondays. The result of this operation is an array of TRUE/FALSE values, which become the denominator of the original values. FALSE evaluates as zero, and throws a #DIV/0! error. TRUE evaluates as 1 and returns the original value. The final array of values and errors acts like a filter. AGGREGATE ignores all errors and returns the largest (maximum) of the surviving values. More complete example here .

Function numbers

The table below lists the function numbers available to the AGGREGATE function, along with the name of the associated function. The third column, Ref2 , indicates the “second argument” expected by the last 6 functions.

Behavior options

The AGGREGATE function has many options for ignoring errors, hidden rows, and other functions. Options are set with the options argument. Possible values are 0-7, as shown in the table below.

Note: The AGGREGATE function is like an upgraded version of the older SUBTOTAL function , and provides more calculation options, and more control over ignoring specific things. However, one subtle difference between AGGREGATE and SUBTOTAL is that the default behavior for hidden rows is reversed. While SUBTOTAL will always ignore values in rows hidden by a filter, and needs a different function number to ignore manually hidden rows, AGGREGATE will always ignore manually hidden rows and needs a specific option to ignore rows hidden by a filter. Even when the option argument is set to 4 (ignore nothing), AGGREGATE will ignore values in manually hidden rows.

Notes

• AGGREGATE returns a #VALUE! error if a second function argument is required, but not provided.
• 3D references do not work with AGGREGATE.
• The AGGREGATE function is designed for vertical ranges, not horizontal ranges.