Limit Formula Result to Maximum or Minimum Value

This post will guide you how to limit formula result to a maximum or minimum value in excel. For example, if you want to limit the results of a formula to a maximum value of 200, how to this this in excel. How do I return a formula result only within a specified minimum or maximum values in excel. Do we have any ways to limit the results of a calculation to a maximum value.

You can use the MIN function or Max function to create a formula to limit a number within a minimum or maximum value. Just do the following steps:

1. Limit Formula Result to Maximum Value

If you want to limit a formula result to a maximum value 200, you can use the MIN function to create a formula, just like this:

=MIN(200,SUM(B1:B3))

limit formula result1

If the result of SUM function is greater than 200, then the MIN function will return value 200. Otherwise, return the result of SUM function.

2. Limit Formula Result to Minimum value

If you want to limit a formula result (for example, the sum function) to a minimum value, then you can use the MAX function in combination with the SUM function or other function to create a new formula. Just like this:

=MAX(200,SUM(B1:B3))

limit formula result2

If the result returned by the SUM function is smaller than 200, then returns value 200, otherwise, it will return the result returned by the SUM function.

3. Video: Limit Formula Result to Maximum or Minimum Value in Excel

This video will demonstrate how to limit the result of a formula to a specific minimum or maximum value using the MIN Function or MAX Function in Excel.

4. 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 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])…

Return Larger or Smaller of Two Values

This post will guide you how to get the larger value from two values in excel. How do I get the smaller value from two values in excel. How to calculate the smallest value or largest value from a range of cells in excel. How to compare two cells and return the one with smaller value or larger value in excel.

Assuming that you want to compare two cells in excel and both cells have a certain value and you want to select the cell with smaller value or larger value with a formula in excel.

1. Return Larger of Two Values

To get the larger value from two values, you need to create a formula based on the MAX function. Just like this:

=MAX(A1,B1)

Type this formula into the formula box of cell C1, then press Enter key in your keyboard. And drag the AutoFill Handler over other cells to apply this formula to get the larger values.

return large or small value1

2. Return Smaller of Two Values

To get the smaller of two values, you can use the MIN function. Just like the below formula:

=MIN(A1,B1)

This formula returns the smallest value in the numbers provided.

Type this formula into the formula box of cell C2, then press Enter key.

return large or small value2

3. Video: Return Larger or Smaller of Two Values

This video will explain how to use MAX or MIN function to return the larger or smaller of two values in the given range of cells in Excel.

4. Related Functions

  • 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 Find the Position of First Number in A Text String in Excel

This post will guide you how to get the position of the first number in a text string in Excel. How do I find the position of first number in a text string in Excel 2013/2016.

1. Find the Position of First Number in a Cell using Formula

Assuming that you have a list of data in range B1:B4, in which contain text strings. And you wish to get the position of the first number in those range of cells. How to accomplish it. And you can use an formula based on the MIN function and the SEARCH function. Like below:

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789"))

Type this formula into a blank cell and press Enter key on your keyboard, and drag the AutoFill Handle down to other cells to apply this formula.

find first position of first number in cell1

You would see that the position of the first number is calculated.

If you want to get the position of last number in a text string in your range of cells, you can use another array formula based on the MAX function, the IFERROR function, the FIND function, the ROW function, and the LEN function. Like this:

=MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},B1,ROW(INDIRECT("1:"&LEN(B1)))),0))

Type this formula into a blank cell and press Ctrl + Shift + Enter keys on your keyboard, and drag the AutoFill Handle down to other cells to apply this formula.

find first position of first number in cell2

2. Find the Position of First Number in a Cell using User Defined Function with VBA Code

You can also create a User defined function with VBA Code to find the position of first number in a given cell in Excel. here are the steps:

Step1: press Alt + F11 to open the Visual Basic Editor.

Adding Comma Character at End of Cells vba1.png

Step2: In the Visual Basic Editor, click Insert > Module to insert a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Copy the below VBA code for the custom function and paste it into the new module. Save the VBA module by clicking File > Save or by pressing Ctrl + S.

How to Find the Position of First Number in A Text String in Excel vba 1.png
Function FirstNumberPosition_Excelhow(rng As Range) As Integer

Dim s As String
Dim i As Integer

s = rng.Value

For i = 1 To Len(s)
    If IsNumeric(Mid(s, i, 1)) Then
        FirstNumberPosition_Excelhow = i
        Exit Function
    End If
Next i

End Function

Step4: Type the following formula in a blank cell:

=FirstNumberPosition_Excelhow(B1)

Step5: Press Enter to calculate the result of the formula using the custom function.

How to Find the Position of First Number in A Text String in Excel vba 2.png

3. Video: Find the Position of First Number in a Cell

This video will demonstrate how to find the position of the first number in a cell using a formula or VBA code in Excel.

How to Look Up the Lowest Value in A List by VLOOKUP/INDEX/MATCH Functions in Excel

VLOOKUP function is very useful in our daily work and we can use it to look up match value in a range, then get proper returned value (the returned value may be just adjacent to the match value). Sometimes we only want to look up the lowest value among all matched values in the list and get its adjacent value, how can we do?

This tutorial will help you to look up the lowest value in a list by VLOOKUP function and User Defined Function with VBA code. However, except VLOOKUP function, we can also use INDEX/MATCH functions together in some situations to look up the lowest value as well. Please see details below.

Precondition:

Prepare a table consists of name, score and range columns. Now we want to get the ‘Range’ of the lowest score.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 1

1. Look Up the Lowest Value by VLOOKUP Function

Step1: In E2, enter the formula:

=VLOOKUP(MIN(B2:B6),B2:C6,2,FALSE)

As we want to find the ‘Range’ of the lowest score, we need to look up the lowest score among all score first, so we enter MIN(B2:B6) as lookup_value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 2

Step2: Click Enter to get returned value in C column.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 3

Comment:

a.If there are two duplicate lowest values, VLOOKUP function will look up the first match value and return its adjacent value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 4

b.This method only works well when returned value is listed in the right column.

2. Look Up the Lowest Value using User Defined Function with VBA Code

You can also create a User Defined function with VBA Code to lookup the lowest value in a list to replace the above VLOOKUP formula. Just do the following steps:

Step1: Open your Excel workbook and press Alt + F11 to open the Visual Basic Editor.

Adding Comma Character at End of Cells vba1.png

Step2: In the Visual Basic Editor, click Insert > Module to insert a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Copy the below VBA code for the custom function and paste it into the new module. Save the VBA module by clicking File > Save or by pressing Ctrl + S.

How to Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH Functions in Excel vba 1.png
Function MyVLOOKUP_Excelhow(rng As Range, lookup_value As Variant, column_index As Integer, exact_match As Boolean) As Variant
    Dim lookup_range As Range
    Dim result As Variant
    Set lookup_range = rng
    lookup_value = Application.WorksheetFunction.Min(lookup_range.Columns(1))
    result = Application.WorksheetFunction.VLookup(lookup_value, lookup_range, column_index, exact_match)
    MyVLOOKUP_Excelhow = result
End Function

Step4: In your Excel worksheet, enter a formula using the custom function just like you would any other built-in Excel function. Enter the following formula in Cell E2:

=MyVLOOKUP_Excelhow(B2:C6,0,2,FALSE)

Step5: Press Enter to calculate the result of the formula using the custom function.

How to Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH Functions in Excel vba 2.png

3. Look Up the Lowest Value by INDEX/MATCH Functions

In above example, if we want to know the name of the lowest score, VLOOKUP function doesn’t work. So we use INDEX and MATCH functions combination to look up the lowest value here.

Step1: In B2, enter the formula:

=INDEX(A2:A6,MATCH(MIN(B2:B6),B2:B6,0))

MATCH function returns the location of cell, in this example it returns the lowest value’s row number. Then we can use INDEX function to get proper Name from A2:A6 refer to row number.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 5

Step2: Click Enter to get returned value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 6

Comment:

a. If there are two duplicate lowest values, INDEX/MATCH functions will look up the first match value and return its adjacent value.

Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 7

b. This method works well for that returned value lists on the both sides of match value.

4. Video: Look Up the Lowest Value in A List

This video will demonstrate how to use the VLOOKUP function and VBA code to look up the lowest value in a list in Excel.

5. Related Functions

  • 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 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])…
  • 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 VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel

This post will introduce you to three methods for calculating the average of a range of numbers in Microsoft Excel, excluding the smallest and largest values. The first and second method involves using the formula, while the third method utilizes VBA code.

1. Calculate the Average Excluding the Smallest and Highest Numbers Using Formula

Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often calculate the average excluding the smallest and highest numbers in the range of data.

As we know we can use some formula like MAX or MIN to get the maximum value or minimum value in a range, so we can use formula combination to calculate the average excluding the MAX and MIN values. In this article, we will provide you some useful formulas to solve our problem.

We can use the total amount minus the smallest value and the highest value, then divide the total number-2, so for this case we can use the similar formula combination to calculate the average. Details see steps below.

Step1: In any blank cell, enter the following formula.

 =(SUM(A2:A9)-MIN(A2:A9)-MAX(A2:A9))/(COUNT(A2:A9)-2)

Step 2: Click Enter to get the result. Verify that average is calculated properly.

How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel 10.png

2. Calculate the Average Excluding the Smallest and Highest Numbers Using TRIMMEAN Function

Above formula combination is a little complex, we can use a special function TRIMMEAN to calculate the average for some conditions.

Step 1: In any blank cell, enter the formula

=TRIMMEAN(A2:A9,2/COUNT(A2:A9))

Step 2: Click Enter to get the result. Verify that average is calculated properly. It is the same with the value calculated by the formula combination.

How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel 11.png

3. Calculate the Average Excluding the Smallest and Highest Numbers with VBA Code

If we frequently use the function to calculate the average in daily work, we can create a user defined function by VBA, then we can use the function directly.

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

Step2: Click Insert->Module to insert a module. Then Module1 is created.

Step3: Enter below code in Module window.

vba toCalculate the Average Excluding the Smallest & Highest Numbers in Excel 1.png
Function AverageExcludeMaxAndMin_excelhow(myrange) As Variant
      With Application
        AverageExcludeMaxAndMin_excelhow = (.Sum(myrange) - .Max(myrange) - .Min(myrange)) / (.Count(myrange) - 2)
      End With
End Function

In above code, we create the function as AverageExcludeMaxAndMin_excelhow. You can shorten the function name per your requirement.

Step4: In any blank cell, enter =Average, verify that user defined function AverageExcludeMaxAndMin_excelhow is loaded.

vba toCalculate the Average Excluding the Smallest & Highest Numbers in Excel 2.png

Step5: In any blank cell, enter the formula:

 =AverageExcludeMaxAndMin_excelhow(A2:A9)

Step6: Click Enter to get the result.

vba toCalculate the Average Excluding the Smallest & Highest Numbers in Excel 3.png

So after above steps, we can directly use this function to calculate the average in following work directly.

4. Video: Calculate the Average Excluding the Smallest and Highest Numbers

This video will demonstrate how to use a User Defined Function (UDF) and formula to calculate the average of a range of numbers in Excel, excluding the smallest and largest values.

5. Conclusion

These methods are useful when you want to calculate the average of a range of values but want to exclude any outliers that could skew the result. By following the steps outlined in this post, you can easily calculate the average of a range of values in Excel while excluding the smallest and largest numbers.

6. 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 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])…
  • Excel COUNT function
    The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…

Basic Array Formula With Examples

Are you searching for an article for getting the basic array formula with different examples for better understanding? Then congratulations because you have just landed on the right article. In this article, you would get to know the basic array formula and the use of this formula in different examples with a brief explanation.

So please carefully read this article till the end, and let’s dive into it;

6 Array Formula Examples2

General Formula

The basic array formula is given as follows:

{=MAX(range1-range2)}

Syntax Explanation

  • Minus Operator (-): This symbol is used to subtract any two values.
  • Parenthesis (): The main function of this symbol is to group the elements.
  • range1: This is the first input range in the Excel sheet.
  • Range2: This refers to the second input range.
  • MAX: The MAX Function returns the largest numeric value from the range of input values.
  • MIN: The MIN Function returns the smallest numeric value from a range of input values.

Explanation

In Excel, an Array Formula allows you to perform complex calculations on one or more value sets. The result could be a single cell or an array. An array is simply a list or range of values, but an Array Formula is a particular type of formula that must be entered by pressing Ctrl+Shift+Enter after that, the formula would be displayed in the formula bar surrounded by curly brackets {=...}

Array formulas are commonly used for data analysis, conditional sums and lookups, linear algebra, matrix math and manipulation, and many other tasks. A new Excel user may come across array formulas in other people’s spreadsheets, but creating array formulas is typically an intermediate-to-advanced topic.

An array formula is commonly used to calculate the maximum or minimum change in a data set without using a helper column. In the example, the formula in E2 is mentioned as follows:

{=MAX(B2:B9-C2:C9)}

6 Array Formula Examples2

This array formula must be entered with control + shift + enter.

This page’s example shows a simple array formula. Working from the inside out, the expression:

=B2:B9-C2:C9

As a result, an array of seven values is returned:

{-153;265;-187;287;11;421;423;-127}

6 Array Formula Examples1

Each number in the array results from subtracting the “low” from the “high” in each of the seven rows of data. This array is returned to the MAX function:

=MAX({17;19;32;25;12;26;29;22})

6 Array Formula Examples2

And MAX returns the maximum value in the array, which is 423.

To return the smallest change in the data, substitute the MIN function for the MAX function:

{=MIN(B2:B9-C2:C9)}

6 Array Formula Examples2

As before, this is an array formula that must be entered with control + shift + enter.

More Examples For Better Understanding

For basic array formulas in Excel, use the steps below.

In the following example, you must create a sample data set in Microsoft Excel.

Then, in the formula bar, use the following formula to count the maximum change in a data set.

=MAX(B2:B9-C2:C9)

After pressing Ctrl+Shift+Enter, you will see the MAX value in cell E2.

Then, in the formula bar, use the following formula to count the minimum change in a data set.

=MIN(B2:B9-C2:C9)

Finally, after pressing Ctrl+Shift+Enter, you will see the result for the MIN in cell F2.

More On Array Formulas

To understand array formulas, you must first learn to investigate the results of various operations within a formula as Excel evaluates it. You must understand how to use the F9 key to debug a formula and how to use Excel’s Evaluate Feature.

Summary

This article gives you complete information with the aid of different examples about calculating the basic array formulas.

Related Functions


  • 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])…

 

Calculate Average Of Last 5 Or N Values In Columns

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values, then what would you do?

If you are new to Excel, then your first attempt might be doing this task manually, which is an acceptable way but only when the values of which you want to calculate the average is limited to 2 or 3 but when it comes to calculating the last 5 or N values in the columns then it becomes nearly impossible to do this cumbersome task on time!

Don’t worry about it because after reading this article, you will know the easiest way to calculate the average of the last 5 or N values from the columns within seconds.

Average last N values in columns1

So, let’s dive into the article.

General Formula

To get the average of the last N values, use the formula below.

=AVERAGE(OFFSET(first cell,0,COUNT(range_values)-N,1,N))

Explanations For Syntax:

  • AVERAGE: In Excel, the AVERAGE Function may be used to calculate the arithmetic mean of a set of integers.
  • OFFSET: This Function outputs a reference to a range made up of pieces such as a beginning point, a row, and column offset, and a final height and width in rows and columns. Learn more about the OFFSET Function.
  • COUNT: The COUNT Function returns the result as a number and counts the number of cells that contain numbers.
  • First Cell: It indicates the first cell in the provided input range.
  • Range: This is the input value from your MS Excel spreadsheet.
  • The comma sign (,): is a separator that separates a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.
  • Minus Operator (-): This symbol subtracts 2 values.

Explanation

To average the latest 5 data values in a range of columns, use the AVERAGE Function conjunction with the COUNT and OFFSET functions. The formula in I2 in the example is as follows:

=AVERAGE(OFFSET(A2,0,COUNT(A2:G2)-5,1,5))

  Average last N values in columns1

The OFFSET function may create dynamic ranges from a beginning cell and specify rows, columns, height, and width.

The rows and columns parameters act as “offsets” from the beginning reference. The height and width parameters, all optional, decide how many rows and columns are included in the final range. We want the OFFSET function to return a range that starts at the last item and grows “backward,” therefore we provide the following arguments:

A2 is the initial reference — it is the cell directly to the right of the formula and the first cell in the range of values we are dealing with.

Rows – We use 0 for the rows option because we want to stay in the same row.

Columns – We use the COUNT function to count all values in the range for the columns input, then deduct 5. This moves the start of the range of 5 columns to the left.

Height – we use 1 since we want a 1-row range as the end result.

Width – we pick 5 since we want a final range with 5 columns.

For the formula in I2, OFFSET yields a final range of C2:G2. This is sent to the AVERAGE Function, which returns the average of the five values in the range.

To understand better, consider the following example, which follows a step-by-step procedure:

  • Consider the following example to calculate the average of the last 5 values.
  • This illustration will provide input data from Column A to Column G.
  • Next, enter the supplied formula in the formula bar section.
  • Finally, we shall obtain the result in the selected cell I2.

Less Than 5 Values Average

If you are want to compute the average of the last 2, 3, or 4 data, you don’t need to do it manually. Utilizing the formula discussed above will result in a circular reference mistake when computing the average of the 2, 3, or 4 values in the columns; the range will extend back into the cell that contains the formula. To avoid this mistake and complete your task, modify the formula as follows:

=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))

So If you want to average last N values in columns that less than 5 values, you can use the following formula:

=AVERAGE(OFFSET(A2,0,COUNT(A2:C2)-MIN(5,COUNT(A2:C2)),1,MIN(5,COUNT(A2:C2))))

Average last N values in columns1

In this case, we utilize the MIN function to “catch” cases where there are fewer than 5 values and the real count when there are.

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 COUNT function
    The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
  • 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])….

Get Address of First Cell in Range

We will learn how to get address of first cell in range in this post. At Instance, we must determine the address of first cell in range to use as the data table’s value. For the DATA table, we have a range of value values in the form of an array that is sent to the formula as input, and therefore the formula returns the first cell value.

We will be using the following functions in this article:

The next sections include information on the functions mentioned before that we will use.

ADDRESS retrieves the address of a cell using the row and column numbers.

Syntax:

=ADDRESS(row_num, col_num, [abs_num] )
  • Row_num: The Row number
  • Col_num: The Column number
  • [abs num]: [Optional] integer, one of the following values: 1, 2, 3, or 4. If this parameter is ignored or set to 1, the returned address will be absolute, e.g. $A$1.

Now we’ll combine these functions into a formula. We’ll begin with a data table. We need to determine the data table’s initial cell address.

Utilize the following formula:

=ADDRESS(ROW(table_array), COLUMN(table_array))

Variables:

  • table array: array representation of a data table

Examples:

Let us validate this formula by applying it to an example.

Here we have a data table and we need to use the formula to get the first cell address in the same data table.

Address of first cell in range1

Use the following Formula:

= ADDRESS(ROW(A1:E9), COLUMN(A1:E9))

Address of first cell in range1

Note: A1:E9 is array of tables

Let’s See How This Formula Works:

= COLUMN(A1:E9)

Address of first cell in range1

The COLUMN function returns the value of the first column in the table array.

= ROW(A1:E9)

Address of first cell in range1

The ROW function returns the index of the first row in the table array.

The ADDRESS function accepts the row number and column value parameters and returns the row’s and column’s absolute references.

The array sent to the function is denoted by cell reference. To get the result, press Enter.

As you can see, the formula works correctly since it delivers the value for the first cell in the data table.

Alternatively, you may use the CELL function:

CELL is an Excel built-in information function. Excel’s Cell function saves all the data in a specified cell and returns the cell’s info type. Using the follow formula:

=CELL("address", [array_reference])

or

=CELL("address",INDEX(A1:E9,1,1))

Address of first cell in range1

Note:

  • [array_reference] : table array given as reference.

As seen by the above calculation, you may use the first cell value as a reference.

The INDEX function can be used to get cell reference to the first cell in the given range by giving INDEX 1 for row number and 1 for column number.

Address of first cell in range1

The ADDRESS function may be used in conjunction with the ROW, COLUMN, and MIN functions to get the address of the first cell in a range. I hope this post on finding the get address of first cell in range in Excel was informative. It’s extremely easy and very helpful for people who use excel regularly.

Related Functions

  • 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 ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel ADDRESS function
    The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
  • Excel COLUMN function
    The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • 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])….

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate column in Ms Excel easily, then congratulations, you are thinking right, but here a problem arises that there isn’t any doubt that by this way you can extract one or two matches into the separate column easily but with the aid of this way you cannot extract multiple matches into separate columns and if you would do that by this way then there are 90% chances that you would 100% get tired of it and can’t complete your task at the right time.

But don’t be worry about it because after carefully reading this article extracting multiple matches into the separate columns would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

 General Formula:


For extracting multiple matches(items) into seprate columns you can use the  Array Formula which is based upon INDEX and SMALL, which is stated as follows:

=IFERROR(INDEX(STU_Range,SMALL(IF(CLASS_Range=$E2, ROW(STU_Range) -MIN (ROW(STU_Range))+1),COLUMNS($E$2:E2))),"")

excel multiple matches into separate column1

Syntax Explanations:


Before knowing about how to use this formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to extracting multiple matches into the separate columns:

  • IFERROR: This Function returns a custom result whenever a formula generates an error and returns the expected result when no error is detected.
  • INDEX: In a range or array, this index function contributes to returning the value at a given position.
  • SMALL: From the given range of data, this small Function returns the Nth
  • IF: In Excel, this IF Function contributes to returning two different values, one value for the TRUE result and another for the FALSE result.
  • ROW: In Excel, this Row function contributes toreturning the row number as a reference.
  • MIN: From the range of input values, this MIN function contributes to returning the smallest numeric value.
  • Absolute Reference: The Absolute referenceis nothing but an actual fixed location in a worksheet.
  • COLUMNS: From a given reference, this Column function contributes to counting the columns.
  • Comma symbol (,): This symbol acts as a separator that contributes to separating a list of values.
  • Minus Operator (-): This minus symbol contributes to subtracting any two values.
  • Parenthesis (): The primary purpose of this parenthesis symbol is to group the various elements.
  • Name– It represents the input ranges in your worksheet.
  • Plus operator (+): This plus symbol adds the values.

Let’s See How This Formula Works:


To use this array formula for getting the work done, you must enter this formula with Control + Shift + Enter. As soon as you would enter this formula into the first cell, you need to drag it down and across to fill in the other cells.

As you can see in the above screenshot, this formula uses two names ranging: “ CLASS_Range ” and “ STU_Range,” where “ STU_Range ” refers to B2:B12 and on the other hand “ CLASS_Range ” refers to A2:A12.

You would definitely wonder that how this formula works to extract multiple matches into columns? So here is the answer. In this formula, we use the Small Function and INDEX function, which work together.

As the SMALL Function (dynamically constructed by IF) is used to obtain row number corresponding to an “nth match,” so after getting the row number from SMALL Function, this would then pass it into the INDEX function, which returns the value at that row, this is also the main motive of this formula.

The snippet “IF(CLASS_Range=$E2, ROW(STU_Range) -MIN (ROW(STU_Range))+1” tests the named range “ STU_Range ” for the value in E2. If the value is found, then from an array of relative row numbers, it would return a row number, which is created with:

=ROW(STU_Range) -MIN (ROW(STU_Range))+1

The output of this formula is :

{1;2;3;4;5;6;7;8;9;10;11}

excel multiple matches into separate column1

Now the final result is an array that would contain the numbers where there is a match, and FALSE where there is not any match found:

{1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE;FALSE;10;FALSE}

excel multiple matches into separate column1

Then this array goes into the SMALL Function. By expanding range(Given Below), The k value for SMALL (nth) returns:

COLUMNS($E$2:E2)

The SMALL function returns each matching row number, which is then supplied as the row_num to the INDEX function as the array with the range named “ STU_Range.”

excel multiple matches into separate column1

Notes:

Now, this question would pop up in your mind that how would it handle the errors? Then whenever the COLUMN would return a value for k that does not exist, the #NUM error would be thrown by the SMALL Function at the next moment. This usually occurs when all the matches have occurred. To tackle the errors, the formula is wrapped up in the Function named “IFERROR,” which would receive the errors and then return an empty string (” “).

Related Functions


  • 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 MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • 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. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • 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 IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel COLUMN function
    The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

How to Find the Earliest and Latest Date in Excel

We have a range of dates and we want to look up the earliest and the latest date based on certain criteria like the earliest date for a showing movie, we can use MIN and MAX functions with IF function or INDEX function together to find the matched date based on some criteria. Except using formula, we can also use PivotTable to find the min or max date.

See the table below. We have a list of entertainments in recent days. And we want to know the earliest date and latest date for certain entertainments.

Find the Earliest and Latest Date 1

Find the Earliest Date Based on Criteria with Functions MIN & IF


Step 1: In E2 cell enter the formula =MIN(IF($A$2:$A$13=D2,$B$2:$B$13)).

Find the Earliest and Latest Date 2

Comment:

In above formula IF function returns the data satisfied the criteria $A$2:$A$13=D2, then MIN function returns the earliest date among the filtered dates.

Step 2: Press Ctrl+Shift+Enter to get the result. Verify that it returns a five digits number, so we need to convert it to date format.

Find the Earliest and Latest Date 3

Step 3: Click Home, in Number group, click General dropdown list and select Short Date.

Find the Earliest and Latest Date 4

Step 4: Verify that the five digits number is converted to date format properly.

Find the Earliest and Latest Date 5

Find the Latest Date Based on Criteria with Functions MAX & IF


Step 1: In E6 cell enter the formula =MAX(IF(A2:A13=D6, B2:B13)).

Find the Earliest and Latest Date 6

Step 2: Press Ctrl+Shift+Enter to get the result. Verify that it returns a five digits number, so we need to convert it to date format.

Find the Earliest and Latest Date 7

Step 3: Click Home, in Number group, click General dropdown list and select Short Date. Verify that number is converted to date format properly.

Find the Earliest and Latest Date 8

There is another way to find the latest date by functions MIN and INDEX. See method below.

Find the Latest Date Based on Criteria with Functions MIN & INDEX


Step 1: In E6 cell enter the formula =MAX(INDEX((D6=A2:A13)*B2:B13,)).

Find the Earliest and Latest Date 9

Step 2: Press Ctrl+Shift+Enter to get the result. We get the same 5 digits number in above method step#2. The we can follow previous steps#3-#4 to convert it to date format.

Find the Earliest and Latest Date 10

Video: Find the Earliest and Latest Date in Excel

SAMPLE Files

Below are sample files in Microsoft Excel that you can download for reference if you wish.

Related Functions


  • 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. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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 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])…
  • 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])….

How to Find the Smallest Value and Smallest Positive Value in Excel

Sometimes we want to find out the smallest positive value among a set of values. We should ignore the negative value and zero value when getting the smallest positive value. So we design different cases in this article to demonstrate how can we get the smallest positive value by different formulas.

Returns the Smallest Value by Small Function


If there are all positive values for a set of values, we can use SMALL function to get the smallest value directly. See the table below.

Find the Smallest Positive Value 1

There are a set of values, obliviously the smallest value is 1. But if we want use a formula to find out the smallest value, we can use SMALL function.

Step 1: In D2 cell, enter the formula =SMALL(A2:B6,1).

Find the Smallest Positive Value 2

Step 2: Click Enter to get the result.

Find the Smallest Positive Value 3

Returns the k-th Smallest Value by Small Function


In above case, we enter the formula =SMALL(A2:B6,1) then we can get the smallest value 1. For SMALL function, the parameter is SMALL(array,k), k is ‘Returns the k-th minimum value in the dataset’. So, we can change k number to get the k-th smallest value.

Follow above steps, in D2 cell change the formula to =SMALL(A2:B6,2). Then we can get the second smallest value 2.

Find the Smallest Positive Value 4

Returns the Smallest Positive Value by Small Function


Find the Smallest Positive Value 5

Update above table a little. Replace a positive number with 0. So If we still use above SMALL function without any criteria, it will return 0 as the smallest value. So if we still want to use the SMALL function here, we need to count the number of zero value, then we need to add 1 base on the number of zero value.

Step 1: In D2 cell, enter the formula =SMALL(A2:B6,COUNTIF(A2:B6,0)+1), where COUNTIF function is used for counting the number of zero value.

Find the Smallest Positive Value 6

Step 2: Let’s replace some values with zero values and check the formula again.

Find the Smallest Positive Value 7

Returns the Smallest Positive Value by MIN Function


If we don’t want to add any criteria in SMALL formula to identify if zero value or negative value exists among the set of values, we can directly use MIN function.

See the table below, it contains both negative value and zero value.

Find the Smallest Positive Value 8

Step 1: In D2 cell, enter the formula =MIN(IF(A2:B6>0,A2:B6)). IF function returns an array which satisfy the criteria ‘every value in the array >0’.

Step 2: Press control+shift+enter to returns value.

Find the Smallest Positive Value 9

Related Functions


  • 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 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. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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 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])….

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 Find Max And Min Value with Single or Multiple Criteria in Excel

This post will guide you how to find the maximum value in a range of cells based on single or multiple criteria in Excel. How do I calculate the Minimum value based on criteria with MIN Function in Excel.

Find Max Value based on Single Criteria


Assuming that you have a list of data in range A1:B5, in which contain product names and sale values. And you want to find the maximum value of product name “excel” in range A1:B5. You can use an Excel Array formula based on the MAX function. Like this:

=MAX((A2:A5="excel")*B2:B5)

Then you need to type this formula into a blank cell, and press Ctrl + Shift + Enter keys to change it as array formula. the Maximum value of product “excel” would be returned.

find max value based single criteria1

You can also use another array formula based on the MAX function and the IF function to achieve the same result. Like this:

=MAX(IF(A2:A5="excel",B2:B5))

find max value based single criteria2

Find Max Value based on Multiple Criteria


Assuming that you want to find the maximum value of product “excel” from the month of Jan in range A1:D5, you can use the following array formula based on the MAX function and the IF function to find the max value based on multiple criteria. Like this:

=MAX(IF(A2:A5="Jan", IF(B2:B5="excel",D2:D5),))

Then you need to type this formula into a blank cell, and press Ctrl + Shift + Enter keys to change it as array formula. the Maximum value of product “excel” in the month of Jan would be returned.

find max value based single criteria3

Find Min Value based on Single Criteria


If you want to find the minimum value of the product “excel” from the range A1:B5, you can use the following formula based on the MIN function:

=MIN(IF(A2:A5="excel",B2:B5))

find max value based single criteria5

Find Min Value based on Multiple Criteria


If you want to find the minimum value of the product “excel” from the month of Jan in range A1:D5, you can use the following array formula based on the MIN function and the IF function to find the min value based on the multiple criteria. Like this:

=MIN(IF(A2:A5="Jan",IF(B2:B5="excel",D2:D5)))

find max value based single criteria4

Related Functions


  • 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])…
  • 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. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

How to Check If value Is between Two Numbers in Excel

This post will guide you how to check if a cell value is between two numbers in Excel. How do I build an IF statement that test if a cell value is between two given values in formula in Excel.

Check Value If It Is between Two Numbers


Assuming that you have list of data in range A1:C4, which contain numbers. And you need to test if a numeric value in Cell C2 is between two numbers in Range: A2:B2. How to do it. You can use IF function and the AND function to build an IF statement to check values. Like this:

=IF(AND(C2>A2,C2<B2),"True","False")

Type this formula into a blank cell which you want to place the result, and press Enter key on your keyboard. Then drag the AutoFill Handle down to cell C4 to apply this formula.

check value is between two numbers1

Note: this formula is only valid when the Cell value in A2 is smaller than the cell value in B2.

You can also use another formula based on the AND function, the MIN function and the MAX function to achieve the same result. Like this:

=AND(C2>MIN(A2,B2),C2<MAX(A2,B2))

check value is between two numbers2

Related Functions


  • 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])…
  • 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. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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],…)…

How to Get Cell Address of Max or Min Value in a Range in Excel

This post will guide you how to return the cell address of a maximum cell value from a given range of cells with a formula in Excel. How do I Find the minimum value from a given range and return its cell address with formula in Excel 2010/2013/2016.

Return Cell Address of Max Value in a Range


Assuming that you have a list of data in range B1:B5, which contain some numbers, and you need to find the largest value from this range, and return the cell address. How to do it. You can use a formula based on the CELL function, the INDEX function, the MATCH function and the MAX function to get the cell address of the largest value in range. Like this:

=CELL("address", INDEX(B1:B5,MATCH(MAX(B1:B5),B1:B5,0)))

Type this formula into a blank cell and press Enter key on your keyboard. It would return the cell address of the maximum value in range B1:B5.

get cell address of max value1

You can change the range B1:B5 as you need to get the cell address of the maximum value in a list.

Return Cell Address of Min Value in a Range


If you want to find the smallest value in range B1:B5, and return the cell address. You can use the following formula based on the CELL function, the INDEX function, the MATCH function and the MIN function. Like this:

=CELL("address", INDEX(B1:B5,MATCH(MIN(B1:B5),B1:B5,0))) 

Type this formula into a blank cell and press Enter key on your keyboard. It would return the cell address of the minimum value in range B1:B5.

get cell address of max value2

Let’s see how those two formula work:

The INDEX/MATCH function will return the reference of the Maximum or minimum value from a range. And the Cell function returns the cells address。

Related Functions


  • 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 MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • 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 Highlight The Highest and Lowest Value in Each Row or Column in Excel

This post will guide you how to highlight the highest value in each row or column in Excel. How do I highlight the lowest value in each row and column with conditional formatting function.

Highlight Highest Value in Each Row


Assuming that you have a list of data in range A1:D7, and you want to highlight all highest values in each row or highlight all lowest values in each row. How to achieve it. You need to use the conditional formatting feature to achieve the result. Just do the following steps:

#1 select the range of cells that you want to highlight the highest values in each row.

highlight highest value in row1

#2 go to HOME tab, click Conditional Formatting command under Styles group. And then select New Rule from the drop down menu list. And the New Formatting Rule dialog will appear.

highlight highest value in row2

#3 select Use a formula to determine which cells to format in the Select a Rule Type list box, and type the following formula in the Format values where this formula is true text box. And then click Format button.

=B2=MAX($B2:$D2)

highlight highest value in row3

#4 switch to Fill tab, select on background color as you need. Click Ok button.

highlight highest value in row4

#5 click Ok button. The highest value in each row has been highlighted.

highlight highest value in row5

Highlight Lowest Value in Each Row


If you want to highlight lowest value in each row in your table, you just need to repeat the above steps, just use the following formula instead of it.

=B2=MIN($B2:$D2)

highlight highest value in row10
highlight highest value in row11

Highlight Highest Value in Each Column


If you want to highlight highest value in one column, you can repeat the above steps, and then use the following formula:

=B2=MAX($B$2:B$7)

highlight highest value in row6
highlight highest value in row7

Highlight Lowest Value in Each Column


If you want to highlight highest value in one column, you can repeat the above steps, and then use the following formula:

=B2=MIN(B$2:B$7)

highlight highest value in row8
highlight highest value in row9

Video: Highlight Highest and Lowest Values in each Row or Column

Related Functions


  • 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 Display Negative Time Value in Excel

This post will guide you how to display negative time value in Excel. How do I change the way negative times are displayed in Excel. How to show negative dates or times with a formula in Excel.

If the cell value is a negative time or date value, and the Excel will only show #### error in that cell. So how to correct this error to show the correct negative time or date.

display negative time1

Display Negative Time Value


You can change the Excel’s Default Date System to correct this error. Just do the following steps:

#1 click File tab, and select Options from the menu list. And the Excel Options dialog will open.

display negative time2

#2 click Advanced from the left pane, and check Use 1904 data system under When calculating this workbook section. Click Ok button.

display negative time3

#3 the negative time will be shown normally.

display negative time4

Display Negative Time Value with Formula


You can also use a formula based on the Text function, the Max function and the MIN function to achieve the result of displaying negative time value properly in Excel.

Just like this formula:

=TEXT(MAX($B$1:$B$2)-MIN($B$1:$B$2),"-H::MM")

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

display negative time5

You will see that a negative time value will be displayed in that cell.

The combination of the MAX function and the MIN function will subtract the earlier time from the later time, and get the positive time value. And then it will be formatted using the TEXT function so that it shows a negative time.

Note: you should know that the returned result is actually a text value, not a numeric value.

Video: Display Negative Time Value

Related Functions


    • Excel Text function
      The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
    • 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])…

 

Find the Earliest and Latest Date in a Range of Dates in Excel

This post will guide you how to find the earliest date in a range of dates in Excel. How do I get the earliest and latest date in a range with a formula in Excel. How to find the highest and lowest date in a range of dates in Excel.

Assuming that you have a list of data in a range A1:C3 that contain dates, and you want to get the earliest date in these dates, how to achieve it. You can use a formula based on the MIN function or the SMALL function to get the earliest date. And if you want to get the latest date in those range of cells, you can use a formula based on the MAX function or the LARGE function.

Find Earliest (Lowest) Date


If you want to find the earliest date in the range of cells A1:C3, just type the following formula into a blank cell, and then press Enter key.

=MIN(A1:C3)

find earliest date1

Or

=SMALL(A1:C3,1)

find earliest date2

Find Latest Date


If you want to get the latest date in a range of cells, you can use the following formula, and type it into a blank cell, and then press Enter key.

=MAX(A1:C3)

find earliest date3

Or

=LARGE(A1:C3,1)

find earliest date4

Related Functions


  • 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])…
  • 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 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) …

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)…

Converting Week Number to Date

This post will guide you how to convert a week number to a date with a formula in Excel. How do I calculate a date from a week number and a year with formula in Excel. Or how to get week number from a given date with formula in Excel.

Convert Week Number to Date


Assuming that you have a week number and a year number in your worksheet, and you want to get the start date and end date in that give week in that year. How to achieve it.

You need to create a formula based on the MAX function, the DATE function and the WEEKDAY function to convert week number to a date in Excel. Just do the following steps:

#1 Type the year number in Cell C1, and type the week number in Cell C2.

#2 Type this formula into the formula box of the Cell C3, then press Enter key in your keyboard. The Serial number of the start date is calculated in Cell C3.

=MAX(DATE(C1,1,1),DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),2)+(C2-1)*7+1)

convert week to date1

#3 Type the following formula into the formula box of the Cell C4, and then press Enter key in your keyboard. And the serial number of the end date is calculated in Cell C4.

=MIN(DATE(C1+1,1,0),DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),2)+C2*7)

convert week to date2

#4 select the cells C3:C4 and then go to HOME tab, click Number format list box, and select Short Date item. The serial number are changed to a standard date format.  Or you can also select Long Date format from the drop-down list of the Number format.

convert week to date3

Get Week Number From a Date


If you want to get the week number from a given date, then you can use the WEEKNUM function to quickly achieve it. Just type the formula into the Cell D3:

=WEEKNUM(C3,1)

convert week to date4

Related Functions


  • 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])…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel WEEKNUM function
    The Excel WEEKNUM function returns the week number of a specific date, and the returned value is ranging from 1 to 53.The syntax of the WEEKNUM function is as below:=WEEKNUM (serial_number,[return_type])…