Contents
Microsoft Excel has long been a standard set of functions COUNTIF (COUNTIF), SUMMESLI (SUMIF) и HEARTLESS (AVERAGEIF) and their counterparts that allow you to search for the number, sum and average in the table for one or more conditions. But what if you need to find not the sum or average, but the minimum or maximum according to the condition (s)?
Let’s say we need to find the minimum price for each product in the supplier database:
Thus, the condition will be the name of the product (paper, pencils, pens), and the range for the selection is the column with prices.
For future convenience, we will convert the original range with prices into a “smart table”. To do this, select it and select on the tab Home – Format as a table (Home — Format as Table) or press Ctrl + T. Our “wiser” table will automatically receive the name Table 1, and the columns can be respectively accessed by their names using expressions like Table1[Product] or Table1[Price]. Optionally, the default name Table 1 can be edited on the tab. Constructor (Design), which appears when you click in any cell of our “smart” table. You can read more about such tables and their hidden features here.
Method 1: MINESLI and MAXESLI functions in Excel 2016
Starting with the version of Excel 2016, the Microsoft Excel function set finally has functions that easily solve our problem – these are functions Minnesley (MINIFS) и MAXESLI (MAXIFS). The syntax of these functions is very similar to SUMMESLIMN (SUMIFS):
=Minnesley(Number_range; check_range1; Condition 1; check_range2; Condition 2 …)
where
- Number_range – a range with numbers from which the minimum or maximum is selected
- range_checks – the range that is checked for the condition
- Condition – selection bias
For example, in our case:
Simple, beautiful, elegant. One problem – functions Minnesley и MAXESLI appeared only starting with the 2016 version of Excel. If you (or those who will later work with your file) have older versions, then you will have to shaman in other ways.
Method 2: Array Formula
In the English version it will be, respectively =MIN(IF(Table1[Product]=F4;Table1[Price]))
After entering this formula in the first green cell G4, do not forget to press Enter, Ctrl+Shift+Enterto enter it as an array formula. Then the formula can be copied to the rest of the products in cells G5:G6.
Let’s analyze the logic of this formula in more detail. The IF function checks each array cell from a column Product for equality to the current commodity (Paper). If so, then the corresponding value from the column is returned. Price. Otherwise, the boolean value is FALSE. (FALSE).
So the outer function MIN (MIN) selects the minimum not from all price values, but only from those where the product was Paper, because FALSE function MIN is ignored. If desired, you can select the entire IF (…) function in the formula bar with the mouse
… and press on the keyboard F9to visually see the same resulting array, from which the function MIN and selects the minimum value:
Method 3: DMIN database function
This option uses a little-known (and, unfortunately, underestimated by many) function DMIN (DMIN) from category Working with the database (Database) and requires a slight change in the resulting table:
As you can see, the green cells with the results are transposed from column to row and a mini-table (F4:H5) with conditions is added above them. The logic of this function is as follows:
- Database – our entire table, including headers.
- Field – the name of the column from the table header, from which the minimum value is selected.
- Criterion – a table with selection conditions, consisting (minimum) of two cells: the name of the column that is being checked (Product) and the criterion (Paper, Pencils, Pens).
This is a regular formula (not an array formula), i.e. you can enter and use it in the usual way. In addition, in the same category you can find functions BSSUMM (DSUM), DMAX (DMAX), Count (DCOUNT), which are used in exactly the same way, but can find not only the minimum, but also the sum, maximum, and number of values by condition.
Method 4. Pivot table
If there are a lot of rows in the source table, but the data does not change often, then it will be more convenient to use a pivot table, because. array formula and DMIN function can slow down Excel a lot.
Set the active cell anywhere in our smart table and select on the tab Insert – PivotTable (Insert — Pivot Table). In the window that appears, click OK:
In the PivotTable Designer, drag a field Product to the area of lines, and Price into the range. To force the summary to calculate not the sum (or quantity), but at least right-click on any number and select the command in the context menu Totals by — Minimum:
You can now extract data from the summary for further calculations using the function GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA), which we discussed in detail earlier:
- Why do we need the function GET.DATA.PIVOTAL.TABLE
- Selective calculation of the sum, average, etc. according to one or more criteria
- What are smart tables in Excel and how to work with them