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. Show
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 FormulasRevelation 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.
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 FormulasSuppose 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:
Note: If you are using multiple values instead of a single continuous range, you’ll need to place each set in parentheses.
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 FormulasIn 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 StepsNow 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.
Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams. Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell— called the objective cell— subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell. Put simply, you can use Solver to determine the maximum or minimum value of one cell by changing other cells. For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.
WindowsmacOSWeb
Note:Versions of Solver prior to Excel 2007 referred to the objective cell as the "target cell," and the decision variable cells as "changing cells" or "adjustable cells". Many improvements were made to the Solver add-in for Excel 2010, so if you're using Excel 2007 your experience will be slightly different. Example of a Solver evaluation
In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell F5), until the total profit (objective cell F7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell F7, =SUM (Q1 Profit:Q2 Profit).
1. Variable cells 2. Constrained cell 3. Objective cell After Solver runs, the new values are as follows.
Define and solve a problem
Step through Solver trial solutions
Change how Solver finds solutions
Save or load a problem model
Solving methods used by Solver
You can choose any of the following three algorithms or solving methods in the Solver Parameters dialog box:
Important:You should enable the Solver add-in first. For more information, see Load the Solver add-in. Example of a Solver evaluation
In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell D5), until the total profit (objective cell D7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell D7, =SUM(Q1 Profit:Q2 Profit).
After Solver runs, the new values are as follows.
Define and solve a problem
Notes:
Step through Solver trial solutions
Change how Solver finds solutions
Save or load a problem model
Solving methods used by Solver
Note:Portions of the Solver program code are copyright 1990-2010 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.
Because add-in programs aren’t supported in Excel for the web, you won’t be able to use the Solver add-in to run what-if analysis on your data to help you find optimal solutions. If you have the Excel desktop application, you can use the Open in Excel button to open your workbook to use the Solver add-in. More help on using SolverFor more detailed help on Solver contact: Frontline Systems, Inc. Portions of the Solver program code are copyright 1990-2009 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc. Need more help?You can always ask an expert in the Excel Tech Communityor get support in the Answers community. See AlsoUsing Solver for capital budgeting Using Solver to determine the optimal product mix Introduction to what-if analysis Overview of formulas in Excel How to avoid broken formulas Detect errors in formulas Keyboard shortcuts in Excel Excel functions (alphabetical) Excel functions (by category) |