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

How to Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel

This post will guide you how to find the largest value in a column and then return the adjacent cell value with a formula in Excel. How do I find the max value and return the cell value from the left adjacent column in Excel.

1. Find the Largest Value and Return the Adjacent Cell Value

Assuming that you have a list of data in range A1:B5, and you want to find the largest value from the Sales column and then return the adjacent cell value in product column. How to do it. You can use a formula based on the VLOOKUP function and the MAX function to achieve the result. Like this:

=VLOOKUP(MAX(A2:A5), A2:B5, 2, FALSE)

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

find largest value return adjacent cell value1

You should see that the adjacent cell value is extracted.

The A2:B5 is the data range that you want to use in the VLOOKUP function.  And the Number 2 is the column number that the matched value is returned, it should be the product column number.

2. Find the Max Value and Return Value from Left Adjacent Cell

If you want to find the max value in Sales column and return value from the left adjacent cell in your worksheet, you need to use the INDEX function to create a formula in combination with the MATCH function and the MAX function. Like this:

=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0))

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

find largest value return adjacent cell value2

3. Video: Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel

This video will demonstrate how to use a formula to find the largest value in a column and return the adjacent cell value in Excel.

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

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

How to Extract the Column Header of the Largest Value in a Row in Excel

This post will guide you how to find the largest value in a row and return column header using VBA code or a formula in Excel. How to retrieve the column header of the largest value in a row with a formula in Excel. How to return the column title of the max value in the row in Excel.

The first method involves using a VBA user-defined function to create a custom formula that can be used to find the largest value in a given row and return the header of the column where that value is located.

The second method involves using a combination of built-in Excel functions to create a formula that can achieve the same result.

In this post, we will explore both methods and provide step-by-step instructions on how to implement them.

1. Video: Return the Column Header of the Largest Value in a Row

This video tutorial provides step-by-step instructions on how to return the column header of the largest value in a row in Excel using VBA code, a user-defined function, and a formula.

2. Return the Column Header of the Largest Value in a Row using Formula

Assuming that you have a list of data in range A1:D4, in which contain sales data for each month. And you want to find the highest value for each product and return the Month column header in your worksheet. How to do it. You can use an Excel formula based on the INDEX function, the MATCH function and the MAX function. Like this:

=INDEX($B$1:$D$1,MATCH(MAX(B2:D2),B2:D2,0))

You need to type this formula into cell E2 and press Enter key on your keyboard, and then copying this formula from cell E2 to range E3:E4, you would notice that you will get the column header for in every row.

extract column header of the largest value in row1

Let’s see how this formula works:

The INDEX function will return a reference of the cell at the intersection of a specific row and column in range B1:D1.

The MAX function will return the maximum number from a given range B2:D2. And pass the returned result into the MATCH function as its argument.

The MATCH function will return the relative position of the maximum value in the given range.

3. Return the Column Header of the Largest Value in a Row with User-defined Function

You can also create a user-defined function in VBA to extract the column header of the latest value in a row in Microsoft Excel. just do the following steps:

Step1: Open your Excel workbook and press Alt + F11 to open the VBA editor.

Step2: Insert a new module by right-clicking on the project and selecting “Insert > Module“.

Step3: Copy and paste the code above into the module. Save the module and return to your Excel sheet.

How to Extract the Column Header of the Largest Value in a Row in Excel vba2.png
Function GetLargestValueColumnHeaderbyExcelHow(rowRange As Range) As String
    Dim maxVal As Double
    Dim maxCol As Integer
    Dim headerRow As Integer
    Dim currentCell As Range
    
    headerRow = 1 'assuming headers are in the first row
    maxVal = -1E+20 'initialize max value as a very small value
    
    For Each currentCell In rowRange 'loop through each cell in the row range
        If currentCell.Value > maxVal Then 'check if current cell value is greater than max value
            maxVal = currentCell.Value 'update max value if necessary
            maxCol = currentCell.Column 'update max column if necessary
        End If
    Next currentCell
    
    GetLargestValueColumnHeaderbyExcelHow = Cells(headerRow, maxCol).Value 'return the column header of the max value
End Function

Step4: In a cell where you want to display the column header of the largest value in a row, enter the following formula, then press Enter to display the column header of the largest value in the selected row.

 =GetLargestValueColumnHeader(B2:D2)

Where B2:D2 is the range of cells representing the row you want to check.

How to Extract the Column Header of the Largest Value in a Row in Excel vba3.png

Step5: click on the AutoFill Handler down to cell E2,E3 to apply this formula.

How to Extract the Column Header of the Largest Value in a Row in Excel vba4.png

4. Conclusion

whether you prefer using VBA code or a formula, there are multiple ways to return the column header of the largest value in a row in Excel. With the help of a user-defined function, built-in functions such as VLOOKUP or INDEX/MATCH, or a combination of both, you can quickly and easily extract the information you need from your worksheet.

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

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

 

Extract all the matches with helper Column

With Excel’s powerful functions IF, INDEX, and MATCH, we can find exactly what you’re looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more!

Generic Formula


=IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),””)

Summary


Excel’s INDEX and MATCH functions can be used to extract multiple matches in a single cell. The need for an array formula is unnecessary when simple indexing formulas are available, making this even easier like G5 shown here:

=IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),"")

In this formula, F5 is the total number, A2:D12 is the total data, and D2:D12 is the helper column.

Clarification


Managing duplicate is a challenge with lookup formulas that can easily find the first match, but it becomes more difficult when you’re looking for “all matches.” Lookup functions like VLOOKUP and INDEX + MATCH will return only one instance of your criteria; however, if many items are meeting those requirements, then this process gets exponentially slower as each additional record needs to be processed individually instead of just using their index numbers or names–which would have been much simpler.

With this formula, we can easily extract multiple matches. It’s a lot easier than trying to match each individual prompt with arguments from previous rows! The helper column contains an equation like so:

=SUM(D1,AND(B2=$G$2,C2=$H$2))

how to extract all matches with helper column1

This helper column tests each row in the data to see if Department in column B and C values match up with their corresponding G2 and H2 value. The two logical operations must return TRUE for AND operation, so they both must return True!

The AND function has the practical effect of generating an incrementing counter that only changes when there is a match. This works because TRUE/FALSE values are coerced to 1 or 0 for addition in the sum operation, respectively; thus, they don’t contribute anything but their respective literals while adding up all other input characters together with spaces between them (for example, “the cat” would become just “+t”).

When we look at the extraction area, column G contains a formula that looks like this:

=IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),"")

how to extract all matches with helper column1

INDEX + MATCH looks up the name for a match found, using row number in column F as its value.

INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1)

INDEX uses the array (($A$2:$D$12) and receives the data of all 3 columns, and MATCH is configured to match exactly what’s found in the helper column(the named one with the ‘helper’ title). The third argument tells INDEX that you want 3 fields matched: row number, name/value pair & text content itself.

The formula is clever because it doesn’t matter what values come in at the start; MATCH will only match one of them. Of course, the “helper column” contains duplicates, but they’re irrelevant since all we care about are those first few rows that correspond with each entry on our data table!

Except for the column number, the formulas in columns G and H are the same as column G, which is increased by one.

The IF statement that wraps the INDEX/MATCH formula is a powerful tool for checking each row in an extraction area to see if it equals some condition. In this case, we want matching records, and so F2 gets checked against the total counts of all matches, which gives us our match count (totalNumber). If there are less than equal numbers found, logic will run; else, everything remains “EMPTY.”

The formula in F2 named “totalNumber” is easy to use.

=MAX(D2:D12)

how to extract all matches with helper column1

The MAX function is all you need because it provides a total match count based on the helper column.

Conclusion


If you need to find a value in the intersection of two columns, this article will be helpful for you. Use INDEX and MATCH with a helper column for your most efficient solution.

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 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 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 Create Dynamic Drop Down List without Blank in Excel

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel.

In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be used to reduce mistake and typing errors. And it is also be used to restrict the user for the type of data that can be entered in the range.

Assuming that you have a list of data in your current worksheet, and you can want to create a dynamic drop down list based on those data without blank cells, and if you use the usual method to create it, and it may be doesn’t work. And you can use an OFFSET formula and count the entries in the column, and calculate the number of rows in the range, and then create a source list without blanks, and use formulas to pull the numbered items into a new column. Just do the following steps:

Step1: select cell A2 next to the original data list B1:B8, and type the following formula in Cell A2, and copy it down to cell A8, and it will number the cells that are not blank.

=IF(B2=””,””,MAX(A$1:A1)+1)

create dynamic drop down list with blank1

Step2: then you can create a new source list column without blanks based on the source data list, just enter the following formula into the cell D2 in a new column, and then copy it down to the cell D8 to create a new data list with all the blanks at the end.

=IFERROR(INDEX($B$2:$B$8,MATCH(ROW()-ROW($D$1),$A$2:$A$8,0)),””)

create dynamic drop down list with blank2

Step3: you can create a dynamic drop down list based on the newly source range without blanks. Select a cell that you want to create dynamic drop down list , and go to Data tab, and click Data Validation command under Data Tools group. And the Data Validation dialog will appear.

create dynamic drop down list with blank3

Step4: select List value from the Allow drop down list in the Data Validation dialog box, and enter the following formula into the Source text box. And click Ok button.

=OFFSET($D$1,1,0,MAX($A:$A),1)

create dynamic drop down list with blank4

Step5: You would see that the dynamic drop down list should be created without blanks in your current worksheet.

create dynamic drop down list with blank5

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

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

Changing Negative Number to Zero in Excel

This post will guide you how to change negative number to zero in a cell in Excel. How do I make negative numbers to zero with a formula in Excel. How to remove all negative numbers from a range of cells and then changed them to zero value in Excel.

Changing Negative Number to Zero in Excel


If you want to change all negative numbers to zero value from a cell in Excel, you can use a formula based on the MAX function. Like this:

=MAX(B1,0)

changing negative number to 01

The MAX function will check if the value of the Cell B1 is less than 0, it indicated that this value is a negative value. And it will return 0. Otherwise, It will return the value of Cell B1.

You can also use another formula based on the IF function to change all negative numbers to zero in Excel. Just like this:

=IF(B1<0,0,B1)

changing negative number to 02

Related Functions


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

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

 

Finding the Max and Min value in an Alphanumeric Data

This post will guide you how to find the maximal and minimal value or string from an alphanumeric data list in excel. How do I retrieve or get the max or min string value based on alphabetic order from a string data in excel.

For example, if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.

To get the max value based on the alphabetic order, you can use the following formula:

=LOOKUP(2,1/(COUNTIF(B1:B5,">"& B1:B5)=0), B1:B5)

find max value1

You can also use the following array formula to achieve the same result. Just follow these steps to apply the array formula:

#1 type the formula in the formula box of cell D1

=INDEX(B1:B5,MATCH(MAX(COUNTIF(B1:B5,”<“& B1:B5)),COUNTIF(B1:B5,”<“& B1:B5),0))

#2 Press Ctrl + Shift +Enter on your keyboard to make the formula as an array formula.

#3 the formula will be change as the following style:

{=INDEX(B1:B5,MATCH(MAX(COUNTIF(B1:B5,"<"& B1:B5)),COUNTIF(B1:B5,"<"& B1:B5),0))}

#4 let’s see the result.

find max value2

To get the min value based on the alphabetic order, you can use the following formula:

=LOOKUP(2,1/(COUNTIF(B1:B5,"<"&B1:B5)=0),B1:B5)

find max value3

Of course, you can also use another array formula to get the minimal value based on alphabetic order, just refer to the above steps to apply the following array formula.

{=INDEX(B1:B5,MATCH(MAX(COUNTIF(B1:B5,">"& B1:B5)),COUNTIF(B1:B5,">"& B1:B5),0))}

find min value5

Related Functions


  • Excel LOOKUP function
    The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
  • 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 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 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])…