How to find the closest larger number in an unordered list of values in Excel
Aug 13, 2023
How to find the closest larger number in an unordered list of values in Excel
Have you ever needed to find the closest larger number in an unordered list of values in Excel? In this tutorial, we’ll show you how! This is a great formula to use when you need to determine a material size based on the length or size requested by a customer.https://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/in …https://www.techonthenet.com https://www.facebook.com/techonthenetcom https://twitter.com/tech_on_the_net https://www.pinterest.com/techonthene …
Content
0.02 -> Have you ever needed to find the closest larger number
in an unordered list of values in Excel?
6 -> In this tutorial,
we'll show you how
9.12 -> Here we have a list of material lengths
in column A in the range A2:A13
15.18 -> In column B, a customer requests a size
such as 318 in cell B2
21.26 -> 445 in cell B3, and so on
25.44 -> Since we need to provide a length that is at least
as long as the customer requested
30.54 -> We need a formula that will find the
next larger value in the list of material lengths
35.6 -> Notice that our material lengths
are in no particular order
39.94 -> To get started, let's begin by entering
the SMALL command
43.88 -> The SMALL function is used to return
the nth smallest value from a set of values
49.66 -> As you can see, the SMALL function
takes two parameters
53.74 -> The first parameter we will need to enter is the range
of cells that contain our unordered list of numbers
60 -> In this example, we will enter the range A2:A13
64.92 -> Notice that we used absolute referencing
on the range A2:A13 in the formula
71.06 -> This is done so that the formula can be
successfully copied to cell C3 and C4
76.46 -> and not readjust the column A cells
that will be searched
80.08 -> The second parameter we will enter determines
the position to return from the SMALL function
85.84 -> We will determine the correct position
using the COUNTIF function
89.68 -> to count the number of values smaller
than our customer requested size
93.84 -> and then add one so that we get
the next larger value
97.62 -> To do this, we will enter A2:A13 as the range to count
102.54 -> Again, we will use absolute referencing
for this range
106.44 -> Next, we will enter "
110.24 -> and then concatenate a reference to
cell B2 using the ampersand sign (&)
115.26 -> This tells Excel to count the number of values
that are less than the value in cell B2
120.62 -> Now we want to add 1 to
the result of the COUNTIF function
124.48 -> since we want the next larger value
126.72 -> Now, let's see what
our formula returns
130.06 -> You should see the value 352
appear in cell C2
134.74 -> This is the next larger material length
for the value 318
139.56 -> Now, let's copy our formula down
to cell C3 and C4
144.34 -> As you can see, we get 470 as the next larger value
when the customer requests 445 as the size
153.1 -> and 550 as the next larger value
when the customer requests 527
159.7 -> This covers our example of how
to find the next larger value
163.42 -> when dealing with material lengths in Excel
166.24 -> If you would like to see more examples
168.52 -> or would like to download
the sample data we used for this tutorial
171.78 -> please visit our website
at TechOnTheNet.com
175 -> If you found our tutorial helpful,
please leave a like on this video
178.96 -> and don't forget to subscribe to our YouTube channel
for more great Excel tutorials
Source: https://www.youtube.com/watch?v=pWD4nATPTNk
