Minimum value in excel time. Maximum and Minimum by condition in MS EXCEL. How to make a selection in Excel by condition

This spreadsheet will handle almost all calculations. It is ideal for accounting. For calculations, there are special tools - formulas. They can be applied to a range or to individual cells. To find out the minimum or maximum number in a group of cells, it is not necessary to look for them yourself. It is better to use the options provided for this. It will also be useful to figure out how to calculate the average value in Excel.

This is especially true in tables with large amounts of data. If the column, for example, indicates the prices of the products of the shopping center. And you need to find out which product is the cheapest. If you search for it "manually", it will take a lot of time. But in Excel, this can be done in just a few clicks. The utility also calculates the arithmetic mean. After all, these are two simple operations: addition and division.

Maximum and minimum

Here is how to find the maximum value in Excel:

Place the cell cursor anywhere.
Go to the "Formulas" menu.
Click Insert Function.
Select "MAX" from the list. Or write this word in the "Search" field and click "Find".
In the Arguments window, enter the addresses of the range whose maximum value you want to know. In Excel, cell names consist of a letter and a number ("B1", "F15", "W34"). And the name of the range is the first and last cells that are included in it.
Instead of an address, you can write several numbers. Then the system will show the largest of them.
Click OK. The result will appear in the cell where the cursor was.

The next step is to specify a range of values

Now it will be easier to figure out how to find the minimum value in Excel. The algorithm of actions is completely identical. Simply select "MIN" instead of "MAX".

The average

The arithmetic mean is calculated as follows: add all the numbers from the set and divide by their number. In Excel, you can calculate sums, find out how many cells are in a row, and so on. But it's too complicated and long. You will have to use many different functions. Keep information in mind. Or even write something down on a piece of paper. But the algorithm can be simplified.

Here is how to find the average in Excel:

Place the cell cursor in any free space in the table.
Go to the "Formulas" tab.
Click on "Insert Function".
Select AVERAGE.
If this item is not in the list, open it using the "Find" option.
In the Number1 area, enter the address of the range. Or write several numbers in different fields "Number2", "Number3".
Click OK. The desired value will appear in the cell.

So you can carry out calculations not only with positions in the table, but also with arbitrary sets. Excel, in fact, plays the role of an advanced calculator.

other methods

The maximum, minimum and average can be found in other ways.

Find the function bar labeled "Fx". It is above the main work area of the table.
Place the cursor in any cell.
Enter an argument in the "Fx" field. It starts with an equal sign. Then comes the formula and the address of the range/cell.
You should get something like "=MAX(B8:B11)" (maximum), "=MIN(F7:V11)" (minimum), "=AVERAGE(D14:W15)" (average).
Click on the "tick" next to the function field. Or just press Enter. The desired value will appear in the selected cell.
The formula can be copied directly into the cell itself. The effect will be the same.

Excel-tool "Autofunctions" will help to find and calculate.

Place the cursor in the cell.
Find the button whose name starts with "Auto". This depends on the default option selected in Excel (AutoSum, AutoNumber, AutoOffset, AutoIndex).
Click on the black arrow below it.
Select MIN (minimum), MAX (maximum), or AVERAGE (average).
The formula will appear in the marked cell. Click on any other cell - it will be added to the function. "Drag" the box around it to cover the range. Or Ctrl-click the grid to select one element at a time.
When you're done, press Enter. The result will be displayed in a cell.

In Excel, calculating the average is fairly easy. No need to add and then divide the amount. There is a separate function for this. You can also find the minimum and maximum in a set. It's much easier than counting by hand or looking up numbers in a huge spreadsheet. Therefore, Excel is popular in many fields of activity where accuracy is required: business, audit, personnel records management, finance, trade, mathematics, physics, astronomy, economics, science.

Initial data
The source data can be any numeric dataset, such as a horizontal or vertical range, a two-dimensional array, or even non-contiguous ranges or individually populated cells. For convenience, I took a one-dimensional vertical array A1: A5

To solve this problem, you can use both an additional column with an intermediate formula, and one array formula

1. Solution with extra column
In B1 write the formula

IF(A1<>0;A1;"")

and copy it to the range B2:B5. This intermediate formula in an additional column will "remove" zeros from the original data, replacing them with an empty string ""

In C1 write the formula

It will return the desired result - a number 3

IF(A1<>0;A1;"")

you have to use the formula

IF(A1>0;A1;"")

2. Solution with an array formula
To solve this problem, use the following array formula:

MIN(IF(A1:A5<>0;A1:A5))

How it works: This is the part of the formula A1:A5<>0 forms an array (TRUE:TRUE:FALSE:FALSE:TRUE), then using the IF() function we will transform the array into the following: (5:7:FALSE:FALSE:3). Since the function MIN() ignores text and logical values, then in the end we get the desired - the number 3

If there are negative numbers in the range, then instead of the formula

MIN(IF(A1:A5<>0;A1:A5))

you have to use the formula

MIN(IF(A1:A5>0,A1:A5))

3. Solution with a simple formula
Use this formula:

SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

How it works: Using the function COUNTIF we count the number of zeros in the range and by adding one to this number we return the first smallest number greater than zero using the function LEAST

If there are negative numbers in the range, then instead of the formula

Excel has a function to determine the minimum value. However, finding the minimum value by condition is problematic. A function from the add-in can cope with this task. =MINCESLI(similar to the standard Excel SUMIF function).

In Excel version 2016 and above there is a built-in function MINESLI you can use it. If your Excel is an older version, then this function can be used by installing the add-in VBA-Excel.

The function has the following arguments =MINESLI( RANGE;CRITERIA;[SEARCH_RANGE])

RANGE- Range of checked cells.

CRITERION- A condition in the format of a number, expression, or text that specifies the minimum value check.
[ SEARCH_RANGE ]- Actual range to determine the minimum value. If this parameter is not specified, the cells specified by the parameter will be used. RANGE.

Example 1

As a criterion, you can specify values and logical expressions:

Consider the following example, which determines the minimum score in the literature. For this, in the parameter CRITERION the value "Literature" is specified, and the parameter RANGE- list of items.
If you specify a logical expression as a criterion "<>Russian", then the minimum grade will be determined in all subjects except for the Russian language.

Example 2

In the following example, the parameter SEARCH_RANGE is not set, so the minimum value is determined among the cells specified in the parameter RANGE.

SPECIAL CASES

In particular cases, the solution can be obtained by enumeration of all possible combinations.

Program (C#):

Public static int FactGen(int n) ( int fact = new int; fact = 1; for (int i = 1; i< n + 1; i++) fact[i] = i * fact; return fact; } public static int PermsGenerator(int arr, int num, int fact) { int i, j, f, newind, newnum, newval, size = arr.Length; int result = new int; Array.Copy(arr, result, size); for (i = 0, newnum = num; i < size - 1; i++) { newind = newnum / (f = fact); newnum = newnum - newind * f; newval = result; for (j = i + newind; j >i; j--) result[j] = result; result[i] = newval; ) return result; ) public static int SumAbs(int arr) ( int sum = 0, prev = arr; foreach (int value in arr) ( sum += Math.Abs(value - prev); prev = value; ) return sum; ) public static int MaxArr(int arr, bool detprn) ( int i, j, sum, smax = -1, size = arr.Length; int perm, result = new int, fact = FactGen(size), sorted = new int, empire = new int;Array.Copy(arr, sorted, size);Array.Sort(sorted);for (i = 0; i< fact; i++) { if ((sum = SumAbs(perm = PermsGenerator(sorted, i, fact))) >smax) ( smax = sum; result = perm; ) if (detprn) ( Console.Write("\nPermutation: "); foreach (int value in perm) Console.Write(value + " "); Console.Write(" Sum: (0) Max Sum: (1)", sum, smax); ) ) Console.Write("\nInitial selection: "); foreach (int value in arr) Console.Write(value + " "); Console.Write("\nSorted selection: "); foreach (int value in sorted) Console.Write(value + " "); Console.Write("\nBest permutation: "); foreach (int value in result) Console.Write(value + " "); Console.WriteLine("\nMaximum amount: (0)", smax); empire=sorted; empire=sorted; empire=sorted; for (i = 2, j = 0; i< size - 1; i++, j = (size) - 1 + ((i + 1) & 1) - j) empire[i] = sorted[j]; Console.Write("Эвристический алгоритм: "); foreach (int value in empire) Console.Write(value + " "); Console.WriteLine("\nСумма: {0}", SumAbs(empire)); return result; } static void Main(string args) { bool binc; int i, j, n, nmax = 10; int arr; Random rand = new Random(); MaxArr(new int { 1, 2, 3, 4 }, false); MaxArr(new int { 1, 2, 3, 4, 5 }, false); for (n = 6; n < nmax + 1; n++) { arr = new int[n]; for (i = 0; i < n;) { arr[i] = rand.Next(1, 5 * n); binc = true; for (j = 0; j < i; j++) binc &= (arr[i] != arr[j]); if (binc) i++; } MaxArr(arr, false); } }

Results:

Initial Sample: 1 2 3 4 Sorted Sample: 1 2 3 4 Best Permutation: 2 4 1 3 Max Sum: 7 Heuristic: 2 4 1 3 Sum: 7 Initial Sample: 1 2 3 4 5 Sorted Sample: 1 2 3 4 5 Best permutation: 2 4 1 5 3 Max sum: 11 Heuristic algorithm: 2 4 1 5 3 Sum: 11 Original sample: 26 10 21 4 27 5 Sorted sample: 4 5 10 21 26 27 Best permutation: 10 26 4 27 5 21 Max sum: 99 Heuristic: 10 26 4 27 5 21 Sum: 99 Original sample: 34 3 32 16 28 27 26 Sorted sample: 3 16 26 27 28 32 34 Best permutation: 26 28 3 32 16 34 27 Max sum: 97 Heuristic: 26 28 3 34 16 32 27 Sum: 97 Original sample: 27 3 34 38 18 29 31 39 Sorted sample: 3 18 27 29 31 34 38 39 Best permutation: 29 34 3 38 18 39 27 31 Max sum: 128 Heuristic: 29 34 3 39 18 38 27 31 Sum: 128 Initial sample: 40 27 4 9 32 35 41 39 2 Sorted sample: 2 4 9 27 32 35 39 40 41 Best permutation: 32 2 39 4 40 9 41 27 35 Maximum sum: 223 Heuristic algorithm: 27 35 2 41 4 40 9 39 32 Sum: 221 Initial sample: 41 35 45 27 34 33 18 24 16 25 Sorted sample: 16 18 24 25 27 33 34 35 41 45 Best permutation: 27 34 16 35 18 41 24 45 25 33 Max sum: 150 Heuristic algorithm: 27 34 16 45 18 41 24 35 25 33 Sum: 150

Thus:

A better combination was found for the original array.
A counterexample for the heuristic algorithm was not immediately found.

GENERAL DECISION (10.02.2018)

Let be
a = (a 0 = M 1 , a 1 = M 2 , …, a n-2 = M n-1 , a n-1 = M n ) - initial sequence,
b = (b 0 , b 1 , …, b n-2 , b n-1 ) - the same sequence in ascending order,
с = (c 0 , c 1 , …, c n-2 , c n-1 ) - required sequence.

Consider separately the cases of even and odd n.

Case n = 2k

S(a) = S c (a) - |a n-1 - a 0 |, where
S c a) = |a 0 - a 1 | + |a 1 - a 2 | + … + |a n-3 - a n-2 | + |a n-2 - a n-1 | + |a n-1 - a 0 |.

S c (a) is an algebraic sum that contains each original element a i twice, and its maximum value is

S c_max = 2∑ i = 0, …, k-1 (h i - b i), where h i = b i+k , i = 0…k-1.

This value is achieved in two variants of permutations:
1) c 2i h (all largest elements have even indices);
2) c 2i + 1 h (all largest elements have odd indices).

At the same time, the minimum |a n-1 - a 0 | = b k - b k-1 is achieved by placing a pair of median elements at the edges of the sequence c.

The maximum sum S is equal to S max = 2∑ i = 0, …, k-2 (b i+k - b i) + b k-1 - b k , or

S max = 2∑ i = 0, …, k-2 (b n-1-i - b i) + b n-k - b k-1 ,

and is achieved in those cases when the sequence c contains the largest elements in a checkerboard pattern, and the median elements b k-1 and b k are at the edges of the sequence.
The number of such permutations for pairwise different a i is 2(k-1)! 2.

Case n = 2k+1

A similar consideration shows that the maximum of S is equal to

S max = 2∑ i=0…k-2 (b k+2+i - b k) + b k+1 - b k-1 + max (b k+1 - b k , b k - b k-1), or

S max = 2∑ i=0…k-2 (b n-1-i - b i) + b n-k - b k-1 + max (b k+1 - b k , b k - b k-1),

and is achieved in those cases when the elements with indices greater than k are staggered, and the median and the element closest to it are at the edges of the sequence.
The number of such permutations for pairwise distinct a i is not less than (k-1)!k! (if the median differs from the nearest neighbors by the same value, then there are twice as many permutations).

PROGRAM (C#):

Public static void T(string text, Stopwatch timer) ( TimeSpan ts = timer.Elapsed; string elapsedTime = String.Format("(0:00):(1:00):(2:00).(3:00) ", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10); Console.Write(text + elapsedTime); ) public static int FactGen(int n) ( int fact = new int; fact = 1; for (int i = 1; i< n + 1; i++) fact[i] = i * fact; return fact; } public static int PermsGenerator(int arr, int num, int fact) { int i, j, f, newind, newnum, newval, size = arr.Length; int result = new int; Array.Copy(arr, result, size); for (i = 0, newnum = num; i < size - 1; i++) { f = fact; newind = i; while (newnum >= f) ( newind++; newnum -= f; ) newval = result; for (j = newind; j > i; j--) result[j] = result; result[i] = newval; ) return result; ) public static int SumAbs(int arr) ( int sum = 0, prev = arr; foreach (int value in arr) ( sum += Math.Abs(value - prev); prev = value; ) return sum; ) public static int CalcOptQuant (int n) ( int k = (n - 1) / 2, k2 = Math.Max(k-2, 0); int fact = FactGen(k + 2); return 2*fact[k] * ( (n-k-k< 2) ? fact : fact[k]); } public static int CalcMaxSum(int brr) { int i, size = brr.Length, k = size / 2, sum = 0; for (i = 0; i < k - 1; i++) sum += brr - brr[i]; sum = 2 * sum + brr - brr; if (size - 2 * k >0) sum += Math.Max(brr - brr[k], brr[k] - brr); returnsum; ) public static int MaxArr(int arr, bool detprn) ( int i, j, sum, smax = -1, size = arr.Length; int perm, result = new int, fact = FactGen(size), sorted = new int , empire = new int; Stopwatch sw = new Stopwatch(); Console.WriteLine("\nInitial selection: "); foreach (int value in arr) Console.Write(value + " "); sw.Restart(); for (i = 0; i< fact; i++) { perm = PermsGenerator(arr, i, fact); sum = SumAbs(perm); if (sum >smax) ( smax = sum; result = perm; ) ) if (detprn) Console.Write("\nBest permutations"); for (i = 0, j = 0; i< fact; i++) { perm = PermsGenerator(arr, i, fact); sum = SumAbs(perm); if (sum == smax) { j++; if (detprn) { Console.Write("\n#{0}: ", j); foreach (int value in perm) Console.Write(value + " "); } } } Array.Copy(arr, sorted, size); Array.Sort(sorted); Console.WriteLine("\nОтсортированная выборка: "); foreach (int value in sorted) Console.Write(value + " "); Console.Write("\nФакт Наибольшая сумма: {0} Лучших перестановок: {1}" + "\nРасчёт Наибольшая сумма: {2} Лучших перестановок, не менее: {3}", smax, j, CalcMaxSum(sorted), CalcOptQuant(size)); T("\nRuntime = ", sw); sw.Reset(); return result; } static void Main(string args) { bool binc; int i, j, n, nmax = 12; int arr = new int; Random rand = new Random(); MaxArr(new int { 0, 1, 2, 3, 4, 5 }, true); MaxArr(new int { 5, 1, 3, 5, 7, 9 }, true); MaxArr(new int { 0, 1, 2, 3, 4 }, true); MaxArr(new int { 0, 1, 2, 3 }, true); MaxArr(new int { 0, 1, 2 }, true); for (n = 3; n <= nmax; n++) { Array.Resize(ref arr, n); for (i = 0; i < n;) { arr[i] = rand.Next(10, 99); binc = true; for (j = 0; j < i; j++) binc &= (arr[i] != arr[j]); if (binc) i++; } MaxArr(arr, false); } }

RESULTS:

Initial sample: 0 1 2 3 4 5 Best permutations #1: 2 4 0 5 1 3 #2: 2 4 1 5 0 3 #3: 2 5 0 4 1 3 #4: 2 5 1 4 0 3 #5: 3 0 4 1 5 2 #6: 3 0 5 1 4 2 #7: 3 1 4 0 5 2 #8: 3 1 5 0 4 2 Sorted sample: 0 1 2 3 4 5 Fact Highest sum: 17 Best permutations: 8 Calculation Biggest sum: 17 Best permutations, at least: 8 Runtime = 00:00:00.01 Initial sample: 5 1 3 5 7 9 Best permutations #1: 5 1 7 3 9 5 #2: 5 1 9 3 7 5 # 3: 5 3 7 1 9 5 #4: 5 3 9 1 7 5 #5: 5 7 1 9 3 5 #6: 5 7 3 9 1 5 #7: 5 9 1 7 3 5 #8: 5 9 3 7 1 5 #9: 5 1 7 3 9 5 #10: 5 1 9 3 7 5 #11: 5 3 7 1 9 5 #12: 5 3 9 1 7 5 #13: 5 7 1 9 3 5 #14 : 5 7 3 9 1 5 #15: 5 9 1 7 3 5 #16: 5 9 3 7 1 5 Sorted sample: 1 3 5 5 7 9 Fact Highest sum: 24 Best permutations: 16 Calculation Highest sum: 24 Best permutations , at least: 8 Runtime = 00:00:00.01 Initial sample: 0 1 2 3 4 Best permutations #1: 1 3 0 4 2 #2: 1 4 0 3 2 #3: 2 0 4 1 3 #4: 2 1 4 0 3 #5: 2 3 0 4 1 #6: 2 4 0 3 1 #7: 3 0 4 1 2 #8: 3 1 4 0 2 sorted Current sample: 0 1 2 3 4 Fact Biggest sum: 11 Best permutations: 8 Calculation Biggest sum: 11 Best permutations, at least: 4 Runtime = 00:00:00.01 Initial sample: 0 1 2 3 Best permutations #1: 1 3 0 2 #2: 2 0 3 1 Sorted sample: 0 1 2 3 Fact Highest sum: 7 Best permutations: 2 Calculation Highest sum: 7 Best permutations, at least: 2 Runtime = 00:00:00.00 Initial sample: 0 1 2 Best permutations #1: 0 2 1 #2: 1 0 2 #3: 1 2 0 #4: 2 0 1 Sorted sample: 0 1 2 less than: 2 Runtime = 00:00:00.01 Initial sample: 25 51 87 Sorted sample: 25 51 87 Fact Highest sum: 98 Best permutations: 2 Calculation Highest sum: 98 Best permutations, at least: 2 Runtime = 00:00:00.00 Original sample: 78 10 34 64 Sorted sample: 10 34 64 78 Fact Highest sum: 166 Best permutations: 2 Calculation Highest sum: 166 Best permutations, no less ee: 2 Runtime = 00:00:00.00 Original sample: 23 26 93 16 45 Sorted sample: 16 23 26 45 93 :00:00.00 Original Sample: 85 12 11 13 90 88 Sorted Sample: 11 12 13 85 88 90 Actual Highest Sum: 382 Best Permutations: 8 Calculation Highest Sum: 382 Best permutations, at least: 8 Runtime = 00:00:00.00 Initial sample: 82 43 28 55 29 39 18 Sorted sample: 18 28 29 39 43 55 82 Fact Highest sum: 206 Best permutations: 24 Calculation Highest sum: 206 Best permutations, at least: 24 Runtime = 00:00:00. 00 Original sample: 64 20 43 89 47 94 52 71 Sorted sample: 20 43 47 52 64 71 89 94 Fact Highest sum: 300 Best permutations: 72 Calculation Highest sum: 300 Best permutations, min: 72 00.02 Original sample: 45 53 81 17 76 97 26 50 51 Sorted sample: 17 26 45 50 51 53 76 81 97 00:00.23 Original Sample: 55 51 36 12 82 63 89 58 14 22 Sorted Sample: 12 14 22 36 51 55 58 63 82 89 Runtime = 00:00:02.46 Original sample: 96 95 67 49 65 69 53 78 35 55 94 Sorted sample: 35 49 53 55 65 67 69 78 94 95 96 permutations, at least: 5760 Runtime = 00:00:29.21 Initial sample: 50 43 39 64 59 18 81 16 44 12 15 77 Sorted sample: 12 15 16 18 39 43 44 50 59 64 77 81

ANALYSIS OF THE RESULTS

In all considered cases, testing fully confirmed the proposed general solution.

Finding the maximum / minimum value is a simple task, but it becomes somewhat more complicated if the MAX / MIN is not to be found among all the values in the range, but only among those that satisfy a certain condition.

Let there be a table with two columns: text and number.

For the convenience of understanding the formulas, we will create two for each of the columns: Text (A 6: A 30 ) and Numbers (B6:B30 ). (see example file).

Let's consider a few tasks:

BUT. Find the maximum value among those numbers that correspond to the value Text1(criterion will be entered in the cell E6 ).
Those. we will look for the maximum value not among all values of the column Numeric values, but only among those who have in the same line in the column BUT text value is Text1. Let's write (do not forget to press when entering the formula CTRL+SHIFT+ENTER):
=LARGE(IF(A6:A30=E6,B6:B30,"");1)

or with named ranges:

=LARGE(IF(Text=E6,Numbers,"");1)

The Text=E6 part of the formula, will return (TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE: TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE:TRUE: FALSE:FALSE:FALSE:FALSE) (to view the result, highlight this part of the formula in and press the key). TRUE matches rows that have a column Text values contains a value Text1.

Part of the formula IF(Text=E6;Numbers;""), will return (10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"": "":"": 4:"":"":"":8:"":"":"":""), where the value from the numeric column is substituted for TRUE, and the value is substituted for FALSE. Instead of "" one could use any text character (letter) or omit it altogether (in this case, the array would look like this (10:FALSE:FALSE:FALSE:-66: FALSE:FALSE:FALSE:-37:FALSE: FALSE: FALSE :-5:FALSE:FALSE:FALSE:4: FALSE:FALSE:FALSE:8: FALSE:FALSE:FALSE:FALSE)).

The GREAT() function with the second parameter =1 is used instead of the MAX() function, because if none of the rows satisfies the criterion, then the formula = MAX(("":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"" :"":"": "":"":"":"":"":"":"")) will return 0!, which can be misleading. The GREAT() function in this case will return the error #NUMBER!

B. Let's find the maximum value only among numbers belonging to a certain range of values, for example, from 5 to 50. Borders can be entered in cells I 14 and J14 . The solution is:
=LARGE(IF((Numbers>=I14)*(Numbers<=J14);Числа);1)

AT. Find with help array formulas the minimum value among those that match the value Text3:
=MIN(IF((Text=E7),Numbers,"");1)

Those. if in column BUT value = Text3, then the value in the column is taken into account B if value<> Text3, then the maximum value +1 is taken into account, i.e. obviously NOT the minimum. Further, the MIN() function returns the minimum value from the resulting array, and it is clear that none of the values, where<> Text3, does not distort the result (see problem A).

Another solution using the DMIN() formula, which is not array formula.
=DMIN(A5:B30,B5,I8:I9)

G. Let's find the minimum value, among those that are greater than:
=DMIN(A5:B30;B5;I10:I11)
where in the range I10:I11 contains criteria =B6>AVERAGE(Numbers)

D. Let's find the maximum value modulo. You can see from the figure above that this is -99. To do this, use array formula:

IF(MAX(ABS(Numbers))=MAX(Numbers),MAX(Numbers),-MAX(ABS(Numbers)))

E. Let's find the minimum positive number:

=SMALL(Numbers;COUNTIF(Numbers;"<=0")+1) - the usual formula!

=SMALL(IF(Numbers>0, Numbers),1) - array formula.

ADVICE:

All of the above tasks can be solved without using the DMIN() function. To do this, you need to create an additional column in which only those values that meet the criteria will be displayed. Then, among the selected values, using the MAX() or MIN() functions, determine the maximum or minimum value, respectively (see the example file List without array formulas).

Multiple conditions

The above approach can be extended to cases where it is necessary to find the maximum or minimum for several text conditions.

In this case, you will have to write a more complex array formula:

=SMALL(IF(($A$6:$A$16=E6)*($B$6:$B$16=F6),$C$6:$C$16,"");1)

In the example file, for clarity, the . In addition, criteria are used for selection (see yellow cells).

Similarly, you can set up formulas to find the minimum value for rows whose values fall within a certain range.