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 standard Excel functions SUMIF).

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:

1. 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.
2. 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.

If you have to work with large tables, you will definitely find duplicate amounts scattered along the entire column. At the same time, you may need to select data from the table with the first smallest numeric value that has its own duplicates. We need automatic selection of data by condition. In Excel, you can successfully use an array formula for this purpose.

How to make a selection in Excel by condition

To determine the corresponding value for the first smallest number, a selection from the table is needed by condition. Suppose we want to know the first most cheap goods on the market from this price list:

Automatic sampling is implemented by a formula that will have the following structure:

INDEX(data_range_for_selection, MIN(IF(range=MIN(range),ROW(range)-ROW(column_header),"")))

In the place "data_range_for_selection" you should specify the range of values ​​A6:A18 for a selection from a table (for example, text), from which the INDEX function will select one resulting value. The "range" argument means the area of ​​cells with numeric values ​​from which the first smallest number should be selected. In the "column_header" argument for the second ROW function, you must specify a cell reference with a column header that contains a range of numeric values.

Naturally, this formula should be performed in an array. Therefore, to confirm its entry, you should press not just the Enter key, but the whole key combination CTRL + SHIFT + Enter. If everything is done correctly, curly braces will appear in the formula bar.

Pay attention to the figure below, where this formula in the array was entered into cell B3:

Selecting the corresponding value with the first smallest number:

With this formula, we were able to choose the minimum value relative to numbers. Next, we will analyze the principle of the formula and analyze the entire order of all calculations step by step.

How selection by condition works

The INDEX function plays a key role here. Its nominal task is to select from the source table (specified in the first argument - A6:A18) values ​​corresponding to certain numbers. INDEX works according to the criteria defined in the second (row number inside the table) and third (column number in the table) arguments. Since our source table A6:A18 has only 1 column, we do not specify the third argument in the INDEX function.

Several calculation functions are used to calculate the table row number opposite the smallest number in the adjacent range B6:B18 and use it as the value for the second argument.

The IF function allows you to select a value from a list by condition. Its first argument specifies where each cell in the range B6:B18 is checked for the smallest numeric value: IFB6:B18=MINB6:B18. In this way, an array is created in the program memory from booleans TRUE and FALSE. In our case, 3 array elements will contain TRUE, since the minimum value of 8 contains 2 more duplicates in column B6:B18.

The next step is to determine which rows in the range contain each minimum value. We need this because of the definition of the first smallest value. This task is implemented using the LINE function, it fills the array elements in the program memory with the line numbers of the sheet. But first, from all these numbers, the number is subtracted against the first row of the table - B5, that is, the number 5. This is done because the INDEX function works with numbers inside the table, and not with worker numbers Excel sheet. At the same time, the ROW function can only return the line numbers of the sheet. In order not to get an offset, it is necessary to compare the order of the sheet and table row numbers by subtracting the difference. For example, if the table is on the 5th row of the sheet, then each row of the table will be 5 less than the corresponding row of the sheet.

After all minimum values ​​have been selected and all row numbers of the table have been matched, the MIN function will select the smallest row number. The same row will contain the first smallest number that occurs in column B6:B18. Based on this line number, the INDEX function will select the appropriate value from table A6:A18. As a result, the formula returns this value in cell B3 as the result of the calculation.

How to select the value with the largest number in Excel

Having understood the principle of the formula, now you can easily modify it and adjust it to other conditions. For example, the formula can be modified to select the first maximum value in Excel:

If you need to change the conditions of the formula so that in Excel you can select the first maximum, but less than 70:

How to select the first minimum value other than zero in Excel:

As you can easily see, these formulas differ only in the MIN and MAX functions and their arguments.

Now nothing limits you. Once you understand the principles of operation of formulas in an array, you can easily modify them for many conditions and quickly solve many computational problems.

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:

1. 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.
2. 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: 27 34 16 45 18 41 24 35 25 33 Sum: 150

In this way:

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

GENERAL DECISION (10.02.2018)

Let
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 equal to

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 elements with indices greater than k are staggered, and the median element 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, min: 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, not 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 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.

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:

1. Place the cell cursor anywhere.
2. Go to the "Formulas" menu.
3. Click Insert Function.
4. Select "MAX" from the list. Or write this word in the "Search" field and click "Find".
5. 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.
6. Instead of an address, you can write several numbers. Then the system will show the largest of them.
7. 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".

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:

1. Place the cell cursor in any free space in the table.
2. Go to the "Formulas" tab.
3. Click on "Insert Function".
4. Select AVERAGE.
5. If this item is not in the list, open it using the "Find" option.
6. In the Number1 area, enter the address of the range. Or write several numbers in different fields "Number2", "Number3".
7. 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.

1. Find the function bar labeled "Fx". It is above the main work area of ​​the table.
2. Place the cursor in any cell.
3. Enter an argument in the "Fx" field. It starts with an equal sign. Then comes the formula and the address of the range/cell.
4. You should get something like "=MAX(B8:B11)" (maximum), "=MIN(F7:V11)" (minimum), "=AVERAGE(D14:W15)" (average).
5. Click on the "tick" next to the function field. Or just press Enter. The desired value will appear in the selected cell.
6. 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.

1. Place the cursor in the cell.
2. Find the button whose name starts with "Auto". This depends on the default option selected in Excel (AutoSum, AutoNumber, AutoOffset, AutoIndex).
3. Click on the black arrow below it.
4. Select MIN (minimum), MAX (maximum), or AVERAGE (average).
5. 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.
6. 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.