Find range in excel

Find range in excel DEFAULT

We usually require certain metrics to understand the data that we are working with. There are a number of such representative metrics, like the average, the median, etc. Among these metrics, an often-used value is the &#;Range&#;.

In this tutorial, we will show you two easy ways in which you can find the range of a series of numbers in Excel:

  1. Using a formula with the MIN and MAX built-in functions
  2. Using a formula with the SMALL and LARGE built-in functions

What is Range and How is it Calculated?

The range is a measure of the spread of values in a series. In other words, it is the variation between the upper and lower limits of the series on a particular scale.

To find the range of a set of numbers, you need to find the difference between the largest and smallest numbers.

For example, if you have a series of numbers {4,2,6,5,3} then the range can be calculated as follows:

Range = largest value - smallest value= 6 – 2

How to Find the Range in Excel

Calculation of the range is a very simple process, requiring three basic arithmetic operations:

  1. Finding the largest value
  2. Finding the smallest value
  3. Finding the difference between the two

Given below are two methods to quickly calculate the range of a set of numbers in Excel. To demonstrate both methods, we will use the following dataset:

Dataset to find the range in Excel

Finding the Range in Excel with MIN and MAX Functions

The first way to find the range is to use a combination of the MIN and MAX functions.

The MIN Function

The Excel MIN function returns the smallest numeric value in a range of values. The syntax for the MIN function is as follows:

=MIN (number1, [number2], )

Here,

  • number1 can be a numeric value, a reference to a numeric value, or a range of numeric values.
  • number2,&#; is optional. It can be a numeric value, a reference to a numeric value, or a range of numeric values.

For example, to find the minimum value of numbers in the range B2:B7, you will write the MIN function as follows:

=MIN(B2:B7)

The MAX Function

The Excel MAX function returns the largest numeric value in a range of values. The syntax for the MAX function is as follows:

=MAX (number1, [number2], )

Here,

  • number1 can be a numeric value, a reference to a numeric value, or a range of numeric values.
  • number2, … is optional. It can be a numeric value, a reference to a numeric value, or a range of numeric values.

For example, to find the maximum value of numbers in the range B2:B7, you will write the MAX function as follows:

=MAX(B2:B7)

Note: Both MIN and MAX functions ignore empty cells, logical values like TRUE and FALSE, as well as text values.

Using the MIN and MAX functions to Find the Range of A Series

To find the range of values in the given dataset, we can use the MIN and MAX functions as follows:

  1. Select the cell where you want to display the range (B8 in our example).
  2. Type in the formula: =MAX(B2:B7)-MIN(B2:B7)
  3. Press the Return key.

Max and Min formula to find range in Excel

Note: You can replace the reference B2:B7 with reference to the cells containing the values you want to calculate the range for.

Explanation of the Formula

The formula simply performed the basic steps required to calculate the range:

  1. Finding the largest value: =MAX(B2:B7)
  2. Finding the smallest value: =MIN(B2:B7)
  3. Finding the difference between the two: =MAX(B2:B7) &#; MIN(B2:B7)

Finding the Range in Excel with SMALL and LARGE Functions

The second way to find the range is to use a combination of the SMALL and LARGE function.

The SMALL Function

The Excel SMALL function returns the ‘n-th smallest value’ in a range of values. So you can use it to find the 1st smallest value, 2nd smallest value, 3rd smallest value, and so on.

The syntax for the SMALL function is as follows:

=SMALL (array, n)

Here,

  • array is the range of cells that you want to find the n-th smallest value from.
  • n is an integer that specifies the position from the smallest value, i.e. the nth position.

For example, to find the 3rd smallest value in the range B2:B7, you will write the SMALL function as follows:

=SMALL(B2:B7,3)

Similarly, to find the smallest value in the range B2:B7, you will write the function as follows:

=SMALL(B2:B7, 1)

Notice the above function gives a result equivalent to the function:

=MIN(A2:A7)

The LARGE Function

The Excel LARGE function returns the ‘n-th largest value’ in a range of values. So you can use it to find the 1st largest value, 2nd largest value, 3rd largest value and so on.

The syntax for the LARGE function is as follows:

= LARGE (array, n)

Here,

  • array is the range of cells that you want to find the n-th largest value from.
  • n is an integer that specifies the position from the largest value, i.e. the nth position.

For example, to find the 3rd largest value in the range B2:B7, you will write the LARGE function as follows:

= LARGE (B2:B7,3)

Similarly, to find the largest value in the range B2:B7, you will write the function as follows:

= LARGE (B2:B7, 1)

Notice the above function gives a result equivalent to the function:

=MAX(B2:B7)

Note: In cases where you’re working with large volumes of data, using the MIN and MAX functions are more efficient to use than the SMALL and LARGE. This is because the SMALL and LARGE functions require more computing and resources.

Using the SMALL and LARGE functions to Find the Range of A Series

To find the range of values in the given dataset, we can use the SMALL and LARGE functions as follows:

  1. Select the cell where you want to display the range (B8 in our example).
  2. Type in the formula: =LARGE(B2:B7,1) &#; SMALL(B2:B7,1)
  3. Press the Return key.

LARGE and SMALL formula to find range

Note: You can replace the reference B2:B7 with reference to the cells containing the values you want to calculate the range for.

Explanation of the Formula

The formula simply performed the basic steps required to calculate the range:

  1. Finding the largest value: = LARGE(B2:B7,1)
  2. Finding the smallest value: = SMALL(B2:B7,1)
  3. Finding the difference between the two: =LARGE(B2:B7,1)-SMALL(B2:B7,1)

Applications and Limitations of the Range

The range provides a great way for us to get a basic understanding of how spread out the numbers in the dataset are.

So, a higher range value means the data is quite spread out, while a smaller range value means the data is less spread out, or more concentrated.

It must be noted, though, that the range is a very crude measurement since it is quite sensitive to outliers.

A single value that is too high or too low can completely alter the range, giving an erroneous representation of the data. As such, it doesn’t always provide a true indication of the spread in the dataset.

Having said that, the range is easy to calculate. It only requires basic operations. So, it is a good way to help you get a very basic understanding of the nature of your data.

Other Excel tutorials you may also like:

Sours: https://spreadsheetplanet.com/find-range-in-excel/

Range in Excel

Find Range in Excel

Find Range in Excel (Table of Contents)

Range in Excel

Whenever we talk about the range in excel, it can be one cell or can be a collection of cells. It can be the adjacent cells or non-adjacent cells in the dataset.

What is Range in Excel & its Formula?

A range is the collection of values spread between the Maximum value and the Minimum value. A range is a difference between the Largest (maximum) value and the Shortest (minimum) value in a given dataset in mathematical terms.

Range defines the spread of values in any dataset. It calculates by a simple formula like below:

Range = Maximum Value – Minimum Value

How to Find Range in Excel?

Finding a range is a very simple process, and it is calculated using the Excel in-built functions MAX and MIN. Let&#;s understand the working of finding a range in excel with some examples.

Range in Excel &#; Example #1

We have given below a list of values:

23, 11, 45, 21, 2, 60, 10, 35

The largest number in the above-given range is 60, and the smallest number is 2.

Thus, the Range = = 58

Explanation:

  • In this above example, the Range is 58 in the given dataset, which defines the span of the dataset. It gave you a visual indication of the range as we are looking for the highest and smallest point.
  • If the dataset is large, it gives you the widespread of the result.
  • If the dataset is small, it gives you a closely centered result.

A process of defining the range in Excel

For defining the range, we need to find out the maximum and minimum values of the dataset. In this process, two function plays a very important role. They are:

  1. MAX
  2. MIN

Use of MAX function:

Let’s take an example to understand the usage of this function.

Range in Excel &#; Example #2

We have given some set of values:

Example 2

For finding the maximum value from the dataset, we will apply here the MAX function as below screenshot:

Range in Excel Example

Hit enter, and it will give you the maximum value. The result is shown below:

Range in Excel Example

Range in Excel &#; Example #3

Use of MIN function:

Let’s take the same above dataset to understand the usage of this function.

Example 3

For calculating the minimum value from the given dataset, we will apply the MIN function here as per the below screenshot.

Range in Excel Example

Press ENTER key, and it will give you the minimum value. The result is given below:

Range in Excel Example

Now you can find out the range of the dataset after taking the difference between Maximum and Minimum value.

We can reduce the steps for calculating the range of a dataset by using the MAX and MIN functions together in one line.

For this, we again take an example to understand the process.

Range in Excel &#; Example #4

Let’s assume the below dataset of a company employee with their achieved sales target.

Example 4

Now for identifying the span of sales amount in the above dataset, we will calculate the range. For this, we will follow the same procedure as we did in the above examples.

We will apply the MAX and MIN functions for calculating the Maximum and Minimum sales amount in the data.

For finding the range of sales amount, we will apply the below formula:

Range = Maximum Value – Minimum Value

Refer to the below screenshot:

Range in Excel Example

Press Enter key, and it will give you the range of the dataset. The result is shown below:

Range in Excel Example

As we can see in the above screenshot, we applied the MAX and MIN formulas in one line, and by calculating their results difference, we found the range of the dataset.

Things to Remember

  • If the values are available in the non-adjacent cells, you want to find out the range; you can pass the cell address individually, separated with a comma as an argument of the MAX and MIN functions.
  • We can reduce the steps for calculating the range by applying MAX and MIN functions in one line. (Refer to Example 4 for your reference)

Recommended Articles

This has been a guide to Range in Excel. Here we discuss how to find Range in Excel along with excel examples and downloadable excel template. You may learn more about Excel from the following articles –

  1. Excel Function for Range
  2. Excel Named Range
  3. VBA Range
  4. VBA Selecting Range
Sours: https://www.educba.com/range-in-excel/
  1. Monk fruit vs stevia
  2. Tallmadge new and used tires
  3. Ford e450 class c motorhome
  4. Salvation army newark ny

How to Find Range in Excel (Easy Formulas)

Normally, when I use the word range in my tutorials about Excel, it&#;s a reference to a cell or a collection of cells in the worksheet.

But this tutorial is not about that range.

A &#;Range&#; is also a mathematical term that refers to the range in a data set (i.e., the range between the minimum and the maximum value in a given dataset)

In this tutorial, I will show you really simple ways to calculate the range in Excel.

What is a Range?

In a given data set, the range of that data set would be the spread of values in that data set.

To give you a simple example, if you have a data set of student scores where the minimum score is 15 and the maximum score is 98, then the spread of this data set (also called the range of this data set) would be 73

Range = 98 – 15

&#;Range&#; is nothing but the difference between the maximum and the minimum value of that data set.

How to Calculate Range in Excel?

If you have a list of sorted values, you just have to subtract the first value from the last value (assuming that the sorting is in the ascending order).

But in most cases, you would have a random data set where it&#;s not already sorted.

Finding the range in such a data set is quite straightforward as well.

Excel has the functions to find out the maximum and the minimum value from a range (the MAX and the MIN function).

Suppose you have a data set as shown below, and you want to calculate the range for the data in column B.

Dataset to find Range in Excel

Below is the formula to calculate the range for this data set:

=MAX(B2:B11)-MIN(B2:B11)

Formula to calculate range in Excel

The above formula finds the maximum and the minimum value and gives us the difference.

Quite straightforward&#; isn&#;t it?

Calculate Conditional Range in Excel

In most practical cases, finding the range would not be as simple as just subtracting the minimum value from the maximum value

In real-life scenarios, you might also need to account for some conditions or outliers.

For example, you may have a data set where all the values are below , but there is one value that is above

If you calculate arrange for this data set, it would lead to you making misleading interpretations of the data.

Thankfully, Excel has many conditional formulas that can help you sort out some of the anomalies.

Below I have a data set where I need to find the range for the sales values in column B.

Dataset to find Range in Excel

If you look closely at this data, you would notice that there are two stores where the values are quite low (Store 1 and Store 3).

This could be because these are new stores or there were some external factors that impacted the sales for these specific stores.

While calculating the range for this data set, it might make sense to exclude these newer stores and only consider stores where there are substantial sales.

In this example, let&#;s say I want to ignore all those stores where the sales value is less than 20,

Below is the formula that would find the range with the condition:

=MAX(B2:B11)-MINIFS(B2:B11,B2:B11,">")

Calculating conditional range in Excel

In the above formula, instead of using the MIN function, I have used the MINIFS function (it&#;s a new function in Excel and Microsoft ).

This function finds the minimum value if the criteria mentioned in it are met. In the above formula, I specified the criteria to be any value that is more than 20,

So, the MINIFS function goes through the entire data set, but only considers those values that are more than 20, while calculating the minimum value.

This makes sure that values lower than 20, are ignored and the minimum value is always more than 20, (hence ignoring the outliers).

Note that the MINIFS is a new function in Excel is available only in Excel and Microsoft subscription. If you&#;re using prior versions, you would not have this function (and can use the formula covered later in this tutorial)

If you don&#;t have the MINIF function in your Excel, use the below formula that uses a combination of IF function and MIN function to do the same:

=MAX(B2:B11)-MIN(IF(B2:B11>,B2:B11))

Just like I have used the conditional MINIFS function, you can also use the MAXIFS function if you want to avoid data points that are outliers in the other direction (i.e., a couple of large data points that can skew the data)

So, this is how you can quickly find the range in Excel using a couple of simple formulas.

I hope you found this tutorial useful.

Other Excel tutorials you may like:

Sours: https://trumpexcel.com/find-range-in-excel/
How To Calculate The Interquartile Range In Excel

Value exists in a range

The COUNTIF function counts cells that meet supplied criteria, returning the number of occurrences found. If no cells meet criteria, COUNTIF returns zero. Any positive number means the value was found. This means you can use the greater than operator (>) to run a simple test on the result of COUNTIF to force a TRUE or FALSE result:

You can also wrap the formula inside an IF statement to force a specific result. For example, to return "Yes" or "No", use:

=IF(COUNTIF(range,value),"Yes","No")

This works because IF considers any number > 0 to as TRUE when it appears as the logical test argument.

Testing for a substring or partial match

If you want to test a range to see if it contains specific text (a substring or partial text), you can add wildcards to the formula. For example, if you have a value to look for in cell C1, and you want to check the range A1:A, you can configure COUNTIF to look for that value anywhere in a cell by concatenating asterisks:

=COUNTIF(A1:A,"*"&C1&"*")>0

The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the value in C1, the formula will count the substring in C1 anywhere it appears in each cell of the range.

An alternative formula using MATCH

As an alternative, you can use a formula that uses the MATCH function instead of COUNTIF:

The MATCH function returns the position of a match (as a number) if found, and #N/A if not found. By wrapping MATCH inside ISNUMBER, the final result will be TRUE when MATCH finds a match and FALSE when MATCH returns #N/A.

Sours: https://exceljet.net/formula/value-exists-in-a-range

In excel range find

So, you've named a range of cells, and perhaps you forgot the location. You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook.

  1. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To.

    Or, press Ctrl+G on your keyboard.

  2. In the Go to box, double-click the named range you want to find.

    The Go To dialog box

Notes: 

  • The Go to popup window shows named ranges on every worksheet in your workbook.

  • To go to a range of unnamed cells, press Ctrl+G, enter the range in the Reference box, and then press Enter (or click OK). The Go to box keeps track of ranges as you enter them, and you can return to any of them by double-clicking.

  • To go to a cell or range on another sheet, enter the following in the Reference box: the sheet name together with an exclamation point and absolute cell references. For example: sheet2!$D$12 to go to a cell, and sheet3!$C$$F$21 to go to range.

  • You can enter multiple named ranges or cell references in the Reference box. Separating each with a comma, like this: Price, Type, or BC22,FG30,HH29. When you press Enter or click OK, Excel will highlight all the ranges.

More about finding data in Excel

Sours: https://support.microsoft.com/en-us/office/find-named-rangesdfdcdabe6
Learn Excel 2010 - \

Tip: Try using the new XLOOKUP and XMATCH functions, improved versions of the functions described in this article. These new functions work in any direction and return exact matches by default, making them easier and more convenient to use than their predecessors.

Suppose that you have a list of office location numbers, and you need to know which employees are in each office. The spreadsheet is huge, so you might think it is challenging task. It's actually quite easy to do with a lookup function.

The VLOOKUP and HLOOKUP functions, together with INDEX and MATCH, are some of the most useful functions in Excel.

Note: The Lookup Wizard feature is no longer available in Excel.

Here's an example of how to use VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

In this example, B2 is the first argument—an element of data that the function needs to work. For VLOOKUP, this first argument is the value that you want to find. This argument can be a cell reference, or a fixed value such as "smith" or 21, The second argument is the range of cells, CE7, in which to search for the value you want to find. The third argument is the column in that range of cells that contains the value that you seek.

The fourth argument is optional. Enter either TRUE or FALSE. If you enter TRUE, or leave the argument blank, the function returns an approximate match of the value you specify in the first argument. If you enter FALSE, the function will match the value provide by the first argument. In other words, leaving the fourth argument blank—or entering TRUE—gives you more flexibility.

This example shows you how the function works. When you enter a value in cell B2 (the first argument), VLOOKUP searches the cells in the range C2:E7 (2nd argument) and returns the closest approximate match from the third column in the range, column E (3rd argument).

A typical use of the VLOOKUP function

The fourth argument is empty, so the function returns an approximate match. If it didn't, you'd have to enter one of the values in columns C or D to get a result at all.

When you're comfortable with VLOOKUP, the HLOOKUP function is equally easy to use. You enter the same arguments, but it searches in rows instead of columns.

Using INDEX and MATCH instead of VLOOKUP

There are certain limitations with using VLOOKUP—the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value. Now if your spreadsheet isn't built this way, then do not use VLOOKUP. Use the combination of INDEX and MATCH functions instead.

This example shows a small list where the value we want to search on, Chicago, isn't in the leftmost column. So, we can't use VLOOKUP. Instead, we'll use the MATCH function to find Chicago in the range B1:B It's found in row 4. Then, INDEX uses that value as the lookup argument, and finds the population for Chicago in the 4th column (column D). The formula used is shown in cell A

Using INDEX and MATCH to look up a value

For more examples of using INDEX and MATCH instead of VLOOKUP, see the article https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ by Bill Jelen, Microsoft MVP.

Give it a try

If you want to experiment with lookup functions before you try them out with your own data, here's some sample data.

VLOOKUP Example at work

Copy the following data into a blank spreadsheet.

Tip: Before you paste the data into Excel, set the column widths for columns A through C to pixels, and click Wrap Text (Home tab, Alignment group).

Density

Viscosity

Temperature

50

0

Formula

Description

Result

=VLOOKUP(1,A2:C10,2)

Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is , and then returns the value from column B in the same row.

=VLOOKUP(1,A2:C10,3,TRUE)

Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is , and then returns the value from column C in the same row.

=VLOOKUP(,A2:C10,3,FALSE)

Using an exact match, searches for the value in column A. Because there is no exact match in column A, an error is returned.

#N/A

=VLOOKUP(,A2:C10,2,TRUE)

Using an approximate match, searches for the value in column A. Because is less than the smallest value in column A, an error is returned.

#N/A

=VLOOKUP(2,A2:C10,2,TRUE)

Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is , and then returns the value from column B in the same row.

HLOOKUP Example

Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.

Tip: Before you paste the data into Excel, set the column widths for columns A through C to pixels, and click Wrap Text (Home tab, Alignment group).

Axles

Bearings

Bolts

4

4

9

5

7

10

6

8

11

Formula

Description

Result

=HLOOKUP("Axles", A1:C4, 2, TRUE)

Looks up "Axles" in row 1, and returns the value from row 2 that's in the same column (column A).

4

=HLOOKUP("Bearings", A1:C4, 3, FALSE)

Looks up "Bearings" in row 1, and returns the value from row 3 that's in the same column (column B).

7

=HLOOKUP("B", A1:C4, 3, TRUE)

Looks up "B" in row 1, and returns the value from row 3 that's in the same column. Because an exact match for "B" is not found, the largest value in row 1 that is less than "B" is used: "Axles," in column A.

5

=HLOOKUP("Bolts", A1:C4, 4)

Looks up "Bolts" in row 1, and returns the value from row 4 that's in the same column (column C).

11

=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

Looks up the number 3 in the three-row array constant, and returns the value from row 2 in the same (in this case, third) column. There are three rows of values in the array constant, each row separated by a semicolon (;). Because "c" is found in row 2 and in the same column as 3, "c" is returned.

c

INDEX and MATCH Examples

This last example employs the INDEX and MATCH functions together to return the earliest invoice number and its corresponding date for each of five cities. Because the date is returned as a number, we use the TEXT function to format it as a date. The INDEX function actually uses the result of the MATCH function as its argument. The combination of the INDEX and MATCH functions are used twice in each formula – first, to return the invoice number, and then to return the date.

Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.

Tip: Before you paste the data into Excel, set the column widths for columns A through D to pixels, and click Wrap Text (Home tab, Alignment group).

Invoice

City

Invoice Date

Earliest invoice by city, with date

Atlanta

4/7/12

="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/yy")

Atlanta

4/9/12

="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/yy")

Atlanta

4/11/12

="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/yy")

Atlanta

4/21/12

="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/yy")

Atlanta

4/25/12

="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy")

Atlanta

4/27/12

Atlanta

4/28/12

Atlanta

5/1/12

Atlanta

5/2/12

Atlanta

5/8/12

Austin

4/9/12

Austin

4/11/12

Austin

4/19/12

Austin

4/28/12

Austin

5/6/12

Dallas

4/25/12

Dallas

4/28/12

Dallas

5/1/12

Dallas

5/1/12

Dallas

5/2/12

Dallas

5/2/12

Dallas

5/4/12

Dallas

5/5/12

Dallas

5/8/12

New Orleans

4/9/12

New Orleans

5/2/12

New Orleans

5/4/12

Tampa

4/18/12

Tampa

4/26/12

Tampa

4/29/12

Tampa

5/1/12

Tampa

5/6/12

Quick Reference Card: VLOOKUP refresher

Lookup and reference functions (reference)

Use the table_array argument in a VLOOKUP function

Sours: https://support.microsoft.com/en-us/office/look-up-values-with-vlookup-index-or-matchc3ce3c-4db

You will also like:

She has golden skin, in which surrounding objects are reflected like in a mirror. Like Tari, the golden girl is completely naked. Tari is not surprised.



2227 2228 2229 2230 2231