An essential skill any Excel user should know is how to determine the range of values in Excel. Most businesses don’t have time to waste sorting through rows and rows in Excel in order to find the highest and lowest values of revenue, sales, or other information. The difference between the highest and lowest figures in a group of data – the range – can be valuable in accurate decision making, budgeting, and forecasting.
Let’s pretend you are the purchasing manager for Revelation, Inc., a small business that distributes computers. You can bid bulk pricing for raw materials that will significantly improve Revelation’s profits. The sooner you can purchase materials, the better, because the price increases the closer they are to the ship date. You can use sales data from the prior fiscal year to budget for raw materials, and the range can help you forecast next year’s sales based on this year’s results. You can use the average sales per month for insight into what raw materials you should need when. Identifying that specific gap is also great for setting performance standards, because you can figure out how you perform against other months.
Because Excel offers multiple ways to write range formulas to suit your individual needs, here are three range formula options to get you started!
1. Minimum and Maximum Formulas
Revelation keeps a spreadsheet with information including the state, model, number of units, unit price, and total revenue for each product per state. The past year’s product sales are arranged as follows:
You need to find which products have the smallest and largest demand. This is a small list, but if you sell or resell a lot of product, the following formula can be invaluable. You can find minimum and maximum units easily with the MIN() and MAX() functions.
- In cell B15, type “=MIN(C2:C13)”.
- In cell B16, type “=MAX(C2:C13)”.
You now have a quick report of the fewest number of units sold in a state (102 tablets in Iowa) and the most sold (450 laptops in Illinois).
2. Top k and Bottom k Formulas
Suppose you’re interested in more data – not just the lowest selling item but the three lowest. You can find these with the SMALL() function. To use SMALL(), you’ll need two parameters:
- The same range or list of values as you used for MIN().
Note: If you are using multiple values instead of a single continuous range, you’ll need to place each set in parentheses.
- The value k, which is the desired position from the bottom of the list. If you’re looking for the smallest value, then k = 1. To find the second smallest value, k = 2, etc.
To find the largest three values, use the LARGE() function with the same parameters as SMALL().
Note that SMALL() with k = 1 produces exactly the same result as MIN(). Similarly, LARGE() with k = 1 yields the same as MAX().
3. Conditional Minimum and Maximum Formulas
In some situations, you might need a minimum value that meets specific criteria. For example, you might want to know the fewest units sold for the spring quarter duration or for a specific product type.
Excel provides SUMIF(), COUNTIF(), and other helpful conditional formulas. Unfortunately, there is no MINIF() or MAXIF(), but you can create the same effect with a slightly more complicated method called an array formula. An array formula evaluates a range of cells instead of a single cell.
Typically an IF() formula tests the truth value of a single cell, but as an array formula, we can force it to evaluate each cell in a range. With an array formula you will get an error if you just press enter – #VALUE!. Remember to press CTRL, SHIFT, and ENTER after you finish your array formula.
Let’s find the minimum value for desktops. First, type the matching value (desktop) into the cell you compare your function to. If you are looking for desktop values, then type “desktop” in B18. The formula below will compare that cell reference to the range you are testing. Nest the MIN() and IF() statements as follows: “=MIN(IF(B2:B13=B18,C2:C13))” and press <CTRL>-<SHIFT>-<ENTER>.
The three formulas in rows 18, 19, and 20 calculate the minimum numbers of desktops, laptops, and tablets sold. You can write identical formulas with MAX() to find the greatest number of each product sold.
Next Steps
Now you can use three different methods (Minimum and Maximum Formulas, Top k and Bottom k Formulas, Conditional Minimum and Maximum Formulas) to find the range of values in any data set.
11 Comments
In the max/min example, what formula returns the (max/min) as well as the location (state).
Add to cell F2 “=CONCATENATE(B2,C2)” and drag formula down.
=INDEX(A2:A13,MATCH(CONCATENATE(B18,C18),F2:F13,0))
Will return Iowa
=INDEX(A2:A13,MATCH(CONCATENATE(B19,C19),F2:F13,0))
Will return Minnesota
=INDEX(A2:A13,MATCH(CONCATENATE(B20,C20),F2:F13,0))
Will return Iowa
I’m trying to write a single formula that will show the percentage in a cell (ex 20%) based on a pay scale range of what we sell a product for.
As an example, if a particular cell shows our sell price of $6200, then I want a different cell (formulating cell) to show the percentage that corresponds with that particular range (in this case 20%). If the cell that shows the sell price of $6200 changes (as an example) to $8100, then I want the percentage to automatically change 25% for that range, as in the example below.
example:
$7000-8000=25%
$6999-$6000=20%
$5999-$5000=15%
$4999-$4000=10%
If cell B1 contains the number being evaluated, the statement that will render the correct percentages is:
=IF(B1>8000,0.3,IF(AND(B1>=7000,B1<=8000),0.25,IF(AND(B1>=6000,B1<=6999),0.2,IF(AND(B1>=5000,B1<=5999),0.15,IF(AND(B1>=4000,B1<=4999),0.1,0.0))))) This combines two functions, the IF() and the AND(). This is also a deeply nested function (one function within others), more than five levels deep (generally considered a bad idea – too complex to be later altered) All the stated ranges will result in the requested percentages. If the B1 is outside those ranges If the number being evaluated is less than 4,000, the result is 0% If the number being evaluated is more than 8,000, the result is 0% Without wandering into VBA programming, this is the best way to “single formula” an answer. If these ranges get added to, try stating them on one sheet and using VLOOKUP() on another sheet to find the right percentage.
I have a formula using the concatenate option.
In cell C31 I have:
=CONCATENATE(MIN(1/8),” – “, MAX(B31*2)) that works fine.
In cell B31 I have: 3/8
I just don’t want a decimal as the final result. I get 0.125 – 0.75. I want 1/8 – 3/4
Can you help please.
J
Microsoft® Excel® doesn’t normally resolve exact values to fractional notation because “1/8” can also be written “2/16”, ”4/32” and so on. You can, however, force it to display with the lowest numerator, as a fractional notation with a Custom formatting of “# ?/?”
To force a formatting within a formula, you use the TEXT() function. For your formula, the cell content would need to change to look like this:
=CONCATENATE(TEXT(MIN(1/8),”# ?/?”),”-“,TEXT(MAX(B31*2),”# ?/?”))
Notice the TEXT() function around your critical numbers? This will result in the output you requested: 1/8-3/4
How would I devise a formula to solve for the percentages of a range in volume? For instance if there are a total of 943 entries. In that Column I want to know how many cells fall between 20-30% (my column is in %’s from 0-100) and then give me a percentage of that range for say units in the 10-20%,20-30% categories and so on. So It would need to find the number of cells between say 10-20% then divide them by the total of 943?
Thanks in advance if you can shed some light! 🙂
If you need to count all cells in a range that answer more than one criterion, (such as greater than X and less than Y) then you’ll want to use the COUNTIFS() function. If your percentages happen to be in a cell range (let’s say A2:A101) then you’ll want to specify both conditions, in pairs within a COUNTIFS()
=COUNTIFS(A2:A101,”>=0.20″,A2:A101,”<=0.30")
In English this says “Count all cells within A2:A101 that are greater or equal to 20% and less than or equal to 30%.” Note that you must repeat the range twice; one range for each condition. Note that you must put quotes around each condition. Since we have two conditions, we have four quote characters. Note, also, that percentages within COUNTIFS() are written as decimals.
0.20 is 20%
0.30 is 30% and so on
In my test data, of 943 cells, that just happened to come to 12. You were correct about how to calculate the percentages of each range. If we have 943 cells and 12 of them are within a certain range, the percentage is calculated as 12/943 or 0.0127 or 1.27%
awesome that worked great thank you for the info!!
I have 2 columns. Column A has reading of date (say last 10 years) and Column 2 has respective value to these dates. How can i find out last 1 year Minimum or Maximum value and respective dates?
The easiest method to distill a min/max per year is to create a column, between the A and B that contains the year only (YEAR(A2)).
Then sort the data by date
Then select the Year and Respective data together and insert a PivotTable.
When defining your Row data, choose MIN (as the graphic shows).
That will give you MIN per year.
Select the original data again, insert a second Pivot and define Row Data for Max() instead.