Tag: Excel ABS

Sort Positive Numbers and Negative Numbers by Absolute Values

If both positive numbers and negative numbers exist in the same column, when sorting them by absolute values, we can sort them with the help of ABS function and helper column. In this article, we will show you the way to sort numbers by absolute values.

EXAMPLE

We want to sort numbers in “Numbers” column from smallest to largest by absolute values.

ANALYSIS

If we directly click “Sort Smallest to Largest” button, numbers are sorted by actual values, negative numbers are always the smallest ones. So, if we want to sort them by absolute values, we need to do below two steps:

a. Convert negative numbers to positive number.

b. Sort all positive numbers.

We can apply ABS function to help us solve this problem.

STEPS

First of all, create a helper column. A helper column can help us save temporary data or simplifies some complex formulas. In this instance, we create a helper column to save absolute values of the given numbers.

a. In this instance, just create the helper column adjacent to “Numbers” column.

b. Enter =ABS(A2) into B3 and press Enter. ABS function is used for returning the absolute value of the given number. The syntax is ABS(number).

c. Drag down the fill handle to copy down this formula from B2 to B8.

d. Select range B2:B8, then click Data > Sort A to Z (Sort Smallest to Largest) in Sort & Filter section.

e . Verify that below “Sort Warning” dialog pops up immediately. As we want to sort numbers in “Numbers” column (A column) with the help of helper column (B column), so just keep default option checked, and click “Sort”. If we just want to sort current selection, check on “Continue with the current selection”. The two options are mutually exclusive.

f. After clicking “Sort” button on “Sort Warning” dialog, numbers are sorted from smallest to largest properly in both two columns. We can see that in A column negative numbers are still displayed with negative sign.

g. Delete helper column, now numbers in A column are sorted by absolute values properly as we expect.

Related Functions

  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

How to Average Absolute Values in Excel

We can use AVERAGE function to calculate average of certain values. We can use ABS function to get absolute values for both positive number and negative number. If we want to get the average absolute values, we need to combine both above two functions in the formula. In this free tutorial, we will provide two formulas for you to average absolute values.

Precondition:

Prepare table below. There are some negative numbers.

How to Average Absolute Values in Excel1

Method: Average Absolute Values by Formula

Step 1: In D2 enter the formula =AVERAGE(ABS(A2:B8)).

How to Average Absolute Values in Excel2

Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get value.

How to Average Absolute Values in Excel3

Step 3: If you don’t want to enter an array formula and you like apply a regular formula instead, you can enter this formula =AVERAGE(INDEX(ABS(A2:B8),0,0)).

How to Average Absolute Values in Excel4

Step 4: Just press Enter as usual.

How to Average Absolute Values in Excel5

We get the same result.

Related Functions

  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…

How to Get the Maximum or Minimum Absolute Value in Excel

It is easy to find the maximum or minimum value in a batch of data in excel, but if this batch of numbers contains both positive and negative numbers, the maximum or minimum absolute value cannot be found out by basic MAX or MIN function in excel. This article will help you to solve this issue by using ABS function.

Prepare a table of numbers contains both positive and negative numbers:

Get the Maximum or Minimum Absolute Value 1

As we all know, compare the absolute values for positive number and negative number, actually we compare the values without negative sign, so|-11| is larger than |10|. How can we find out the maximum or minimum value among the positive and negative numbers? Please follow below steps.

Get the Maximum or Minimum Absolute Value in Excel

Step 1: Select a blank cell, enter the formula =MAX(ABS(A1:C4)).

Get the Maximum or Minimum Absolute Value 2

Step 2: Press Ctrl + Shift + Enter together, then the MAX absolute value is displayed.

Get the Maximum or Minimum Absolute Value 3

Step 3: Use the similar formula to get the MIN absolute value. Enter the formula =MIN(ABS(A1:C4)).

Get the Maximum or Minimum Absolute Value 4

Step 4: Press Ctrl + Shift + Enter together, then the MIN absolute value is displayed.

Get the Maximum or Minimum Absolute Value 5

Get the Maximum or Minimum Absolute Value by User Defined Function

We can also define a function like MINABS or MINABS then we can directly use it to find out the MAX or MIN absolute value. See steps below.

Step 1: Click Developer tab->Visual Basic or Alt+F11 to load Microsoft Visual Basic for Applications window.

Step 2: Click Insert->Module to insert a module.

Get the Maximum or Minimum Absolute Value 6

Step 3: Enter below code in Module window.

Get the Maximum or Minimum Absolute Value 7

Function GETMAXABS(SelectRng As Range) As Double
    myarray = SelectRng.Value
    For i = 1 To UBound(myarray, 1)
        For j = 1 To UBound(myarray, 2)
            myarray(i, j) = VBA.Abs(myarray(i, j))
        Next
    Next
    GETMAXABS = Application.WorksheetFunction.Max(myarray)
End Function

Please see screenshot below:

Notes:

  1. We define the function GETMAXABS to get the maximum absolute value in excel.
  2. We can use the similar code to define another function GETMINABS to get the minimum absolute value as well. Just replace ‘max’ to ‘min’ in above code.

Step 4: Now we can try the user defined function GETMAXABS. After entering =get, we can find that defined function GETMAXABS is loaded properly.

Get the Maximum or Minimum Absolute Value 8

Step 5: Enter =GETMAXABS(A1:C4).

Get the Maximum or Minimum Absolute Value 9

Step 6: This time click Enter. Verify that MAX absolute value is displayed.

Get the Maximum or Minimum Absolute Value 10

Related Functions

  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

How to Change Negative Numbers to Positive in Excel

This post will guide you how to change all negative numbers to positive in Excel 2013/2016. How do I remove negative sign to a number in cells with a formula in Excel.

Change Negative Numbers to Positive with a Formula

Assuming that you have a list of data in range B1:B5, in which contain numeric values. And you want to convert all negative numbers to positive. How to do it. You can use an Excel formula based on the ABS function to achieve the result. Here is the formula that you can use it.

=ABS(B1)

Type this formula into a blank cell and press Enter key on your keyboard, and then copy this formula from Cell C1 to range C2:C5 to apply this formula.

change negative numbers to positive1

Change Negative Numbers to Positive with Find and Replace function

You can also use the Find and Replace command to achieve the same result of changing negative numbers to positive in Excel. Here are the steps:

#1 select the range that you want to convert negative numbers to positive.

change negative numbers to positive2

#2 go to HOME tab, click Find & Select command under Editing group. And select Replace from the drop down menu list. And the Find and Replace dialog will open.

change negative numbers to positive3

#3 type the negative sign in the Find what text box, and keep blank in the Replace with text box.

change negative numbers to positive4

#4 Click Replace All button in the Find and Replace dialog box. You would notice that all negative signs have been removed in the selected range of cells.

change negative numbers to positive5

change negative numbers to positive6

Related Functions

  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

Calculating Discount Rate

This post will guide you how to calculate discount rate based on the discounted price and the original price in Excel. How do I generate percentage discount rate with a formula in Excel. How to calculate discount price in Excel.

Calculating Discount Rate

Assuming that you have a list of data in range A1:B4 that contain original price of product and the current sales price (discounted price), and you want to get percentage discount rate. And how to achieve it. You just need to do the following steps:

#1 type the following formula in a blank cell (C2), then press Enter key in your keyboard.

=(B2-A2)/ABS(A2)

calculate discount rate1

#2 drag the AutoFill handle from the Cell C2 to C4 to apply the above formula.

calculate discount rate2

#3 select all discount rate cells C2:C4, and then right click on it, select Format Cells, and the Format Cells dialog will open.

calculate discount rate3

#4 click Number tab, select Percentage in the category list box. And type 2 in the decimal places box, and then click OK button.

calculate discount rate4

#5 you will see that the discount percentage rate have been generated.

calculate discount rate5

Assuming that you have original prices and the discount rate in your data, and you want to calculate the current sales price or discounted price, you just need to use the following formula.

=A2-(B2*A2)

Type this formula in a blank cell and then press Enter key.

calculate discount rate6

Related Functions

  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

 

Sum the Absolute Values in Excel

This post will guide you how to sum the absolute values in a range of cells in Excel. How do I sum the absolute values with a formula in Excel 2013/2016.

Sum the Absolute Values

Assuming that you have a list of data in range B1:B6 that contain negative numbers and positive numbers, and you want to sum all absolute values in these range of cells, how to achieve it.

You can use a formula based on the SUMIF function to achieve the result. Like this:

=SUMIF(B1:B6,”>0”)-SUMIF(B1:B6,”<0”)

Type this formula into a blank cell and then press Enter key in your keyboard.

sum absolute values1

The sum of the absolute values have been calculated.

You can also use another formula based on the SUMPRODUCT function and the ABS function to achieve the same result. Like this:

=SUMPRODUCT(ABS(B1:B6))

Type it into the formula box of a blank cell, then press Enter key.

sum absolute values2

You can also use an Excel Array formula based on the SUM function and the ABS function to sum the absolute values in range B1:B6. Like this:

=SUM(ABS(B1:B6))

You need to type this formula into a cell, and then press Ctrl + Shift + Enter keys in your keyboard.

sum absolute values3

Related Functions

  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel SUMIF Function
    The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

Find Closest Value or Nearest Value in a Range in Excel

This post will guide you how to find the closest value or nearest value in a range of cells or in a column in Excel. How do I find the closest match in a range to a given lookup value in Excel. How to find the closest larger and closest smaller values from a range of cells in excel.

Find Closest Value or Nearest Value in a Range

Assuming that you have a list of numbers in range of cells B1:B6, and you have a lookup value in Cell C1, and you want to find the nearest value that matches the lookup value in that range. How to achieve it.

You need to use an excel array formula based on the INDEX function, the MATCH function, the MIN function and the ABS function. Just like this:

=INDEX(B1:B6,MATCH(MIN(ABS(B1:B6-C1)),ABS(B1:B6-C1),0))

Type this formula into a blank cell D2, and press CTRL+SHIFT+Enter keys in your keyboard. and the nearest value is retrieved from the range B1:B6.

find closest value1

Find Closest Smaller Value

If you want to find the closest smaller value from a range of cells in excel, you need to use another formula based on the LARGE function and the COUNTIF function. Just like this:

=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,">"&C1)+1)

Type this formula into a blank cell, and then press Enter key. and the closest smallest value will be retrieved from the given range of cells.

find closest value2

Find Closest Largest Value

If you want to find the closest largest value from a range of cells in excel, you need to use another formula based on the SMALL function and  the COUNTIF function. Just like this:

=SMALL($B$1:$B$6,COUNTIF($B$1:$B$6,”<”&C1)+1)

Type this formula into a blank cell, and then press Enter key. and the closest largest value will be retrieved from the given range of cells.

find closest value3

Related Functions

  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

Excel SQRT Function

This post will guide you how to use Excel SQRT function with syntax and examples in Microsoft excel.

Description

The Excel SQRT function returns the square root of a positive number. And if you provide a negative number, the function will return #NUM! Error.

The SQRT function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The SQRT function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2011 for Mac.

Syntax

The syntax of the SQRT function is as below:

=SQRT (number)

Where the SQRT function argument is:

  • Number -This is a required argument. The number for which you want to calculate the square root.

Note:

  • If the supplied number is negative, the SQRT function will return #NUM! Error.
  • If the supplied number is non-numeric, The SQRT function will return #VALUE! Error.

Excel SQRT Function Examples

The below examples will show you how to use Excel SQRT Function to calculate the square root for a positive number.

1# to calculate the square root of number 20, enter the following formula in Cell B1.

=SQRT(20)

excel sqrt examples1

2# to calculate the square root of a negative number -20, enter the following formula in Cell B2.

=SQRT(-20)

excel sqrt examples2

You will see that the #NUM! Error message is returned.

3# using the ABS function to get the absolute value of a negative number and then combine with the SQRT function to calculate the square root to avoid the #NUM! Error message. Enter the following formula in Cell B3.

=SQRT(ABS(-20))

excel sqrt examples3

Related Functions

  • Excel ABS Function
    The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

Excel IF Function With Numbers

Normally, If you want to write an IF formula for numbers in combining with the  logical operators in excel, such as: greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to.etc.

The generic IF formula with logical operator for number values is as below:

=IF(B1>10,”good”,”bad”)

Excel IF formula for range of numbers

If you want to check if a cell values is between two values or checking for the range of numbers or multiple values in cells, at this time, we need to use AND or OR logical function in combination with the logical operator and IF function.

For example, you need to check if the value in cell B1 is between values in Cell A1 and A3, then we can write down the following excel IF formula:

=IF(AND(B1>A1,B1<A3),"good","bad")

We can enter the above formula into the formula bar in the cell C1 and then press the Enter key. And rag the Fill Handle to the range C1:C3.it will apply for the other cells for this formula.

Excel IF Function Examples For Numbers1

Excel IF formula for negative numbers

If you want to create an IF formula to display some text string in cells if the value in cell falls between the range negative numbers -10 to positive numbers 10.

Actually, whatever negative numbers or positive numbers, we still can use AND or OR logical function to create a logical test in IF formula. we can write down the following IF formula:

=IF(AND(B1>-10, B1<10),”good”,”bad”)

Excel IF Function Examples For Numbers2

Also, we can use another excel function ABS() to achieve the same results. Let’s see the below IF formula in combination with ABS function.

=IF(ABS(B1)<10,”good”,”bad”)

Excel IF Function Examples For Numbers3

Related Formulas

  • Excel IF formula with operator : greater than,less than
    Now we can use the above logical test with greater than operator. Let’s see the below generic if formula with greater than operator:=IF(A1>10,”excelhow.net”,”google”) …
  • Excel IF function with text values
    If you want to write an IF formula for text values in combining with the below two logical operators in excel, such as: “equal to” or “not equal to”.
  • Excel IF function with Dates 
    If you have a list of dates and then want to compare to these dates with a specified date to check if those dates is greater than or less than that specified date. …
  • Excel IF Function With Numbers
    If you want to check if a cell values is between two values or checking for the range of numbers or multiple values in cells, at this time, we need to use AND or OR logical function in combination with the logical operator and IF function…

Related Functions

  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…) …
  • Excel IF function 
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE….
  • Excel nested if function
    The nested IF function is formed by multiple if statements within one Excel if function. This excel nested if statement makes it possible for a single formula to take multiple actions…
  • Excel ABS function
    The Excel ABS function returns the absolute value of a number

 

 

 

Excel ABS Function

This post will guide you how to use Excel ABS function with syntax and examples in Microsoft excel.

Description

The Excel ABS function returns the absolute value of a number

The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The ABS function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the ABS function is as below:

=ABS (number)

Where the ABS function argument is:
Number – This is a required argument.  A number that you want the absolute value

Example

The below examples will show you how to use Excel ABS function to return the absolute value of a number.

#1 =ABS(B1)

excel abs function example1

Note: the above formula will return the absolute value of B1 cell.

More Excel ABS Function Examples

  • Sum the Absolute Values
    Assuming that you have a list of data in range B1:B6 that contain negative numbers and positive numbers, and you want to sum all absolute values in these range of cells, You can use a formula based on the SUMIF function to achieve the result….