SPECIAL CASES

In special cases, the solution can be obtained by searching through 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;< fact; i++) { if ((sum = SumAbs(perm = PermsGenerator(sorted, i, fact))) >result[i] = newval;< 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); } }

) return result;

Original sample: 1 2 3 4 Sorted sample: 1 2 3 4 Best permutation: 2 4 1 3 Maximum sum: 7 Heuristic algorithm: 2 4 1 3 Sum: 7 Original sample: 1 2 3 4 5 Sorted sample: 1 2 3 4 5 Best permutation: 2 4 1 5 3 Maximum 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 Maximum sum: 99 Heuristic algorithm: 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 Maximum sum: 97 Heuristic algorithm: 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 th amount: 128 Heuristic algorithm: 29 34 3 39 18 38 27 31 Sum: 128 Original 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 1 27 35 Maximum sum: 223 Heuristic algorithm: 27 35 2 41 4 40 9 39 32 Sum: 221 Original sample: 41 35 45 27 34 33 18 24 16 25 Sorted sample: 16 18 24 25 27 33 34 35 41 45 setting: 27 34 16 35 18 41 24 45 25 33 Maximum amount: 150 Heuristic algorithm: 27 34 16 45 18 41 24 35 25 33 Amount: 150

Thus:

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

GENERAL SOLUTION (02/10/2018)

Let
a = (a 0 = M 1, a 1 = M 2, ..., a n-2 = M n-1, a n-1 = M n) - original 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 ) is the required sequence.

Let us 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 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 of the 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 cases where the sequence c contains the largest elements in a checkerboard order, with the median elements b k-1 and b k being 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 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 cases where elements with indices greater than k are in a checkerboard order, and the median and the element closest to it are at the edges of the sequence.
The number of such permutations for pairwise different a i is not less than (k-1)!k! (if the median differs from its nearest neighbors by the same amount, 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;< 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 >for (j = newind; j > i; j--) result[j] = result;< fact; i++) { perm = PermsGenerator(arr, i, fact); sum = SumAbs(perm); if (sum >result[i] = newval;< 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); } }

) return result;

Original 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 Largest total: 17 Best permutations: 8 Calculation Largest sum: 17 Best permutations, no less: 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 Largest sum: 24 Best permutations: 16 Calculation Largest sum: 24 Best permutations , not less than: 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 sample: 0 1 2 3 4 Fact Highest total: Top 11 permutations: 8 Calculation Largest sum: 11 Best permutations, no less: 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 Largest sum: 7 Best permutations: 2 Calculation Largest sum: 7 Best permutations, no less: 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 Fact Largest sum: 3 Best permutations: 4 Calculation Largest sum: 3 Best permutations, no less: 2 Runtime = 00:00:00.01 Initial sample: 25 51 87 Sorted sample: 25 51 87 Fact Largest sum: 98 Best permutations: 2 Calculation Largest sum: 98 Best permutations, no less: 2 Runtime = 00:00:00.00 Initial sample: 78 10 34 64 Sorted sample: 10 34 64 78 Fact Largest sum: 166 Best permutations: 2 Calculation Largest sum: 166 Best permutations, no less: 2 Runtime = 00:00:00.00 Original sample: 23 26 93 16 45 Sorted sample: 16 23 26 45 93 Fact Largest sum: 195 Best permutations: 4 Calculation Largest sum: 195 Best permutations, no less: 4 Runtime = 00:00:00.00 Initial sample: 85 12 11 13 90 88 Sorted sample: 11 12 13 85 88 90 Fact Largest sum: 382 Best permutations: 8 Calculation Largest sum: 382 Best permutations, no less: 8 Runtime = 00:00:00.00 Original sample: 82 43 28 55 29 39 18 Sorted sample: 18 28 29 39 43 55 82 Fact Largest sum: 206 Best permutations: 24 Calculation Largest sum : 206 Best permutations, no less: 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 Largest sum: 300 Best permutations: 72 Calculation Largest sum: 300 Best permutations, no less: 72 Runtime = 00:00: 00.02 Original sample: 45 53 81 17 76 97 26 50 51 Sorted sample: 17 26 45 50 51 53 76 81 97 Fact Largest sum: 337 Best permutations: 288 Calculation Largest sum: 337 Best permutations, no less: 288 Runtime = 00: 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 Fact Largest total: 420 Best permutations: 1152 Calculation Highest total: 420 Best permutations, no less than: 1152 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 Fact Largest sum: 348 Best permutations: 11520 Calculation Largest total: 348 Best permutations, no less than: 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 Fact Largest amount: 463 Lu better permutations : 28800 Calculation Largest amount: 463 Best permutations, no less: 28800 Runtime = 00:06:26.81

) 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

0) sum += Math.Max(brr - brr[k], brr[k] - brr);

In the process of preparing various kinds of rankings or analyzing success indicators, it is very convenient if you highlight the best or worst result in color. How to highlight the maximum value in Excel with color? This is where conditional formatting comes in handy. The advantage of this approach is that the automatic highlighting of the best/worst indicators remains operational even when adding or removing data from the table.

How to highlight the maximum value in Excel

For example, take the expense table:

To quickly identify the highest and lowest expenses, do the following:

As a result, we highlighted the cell with the maximum numerical value.

How to select minimum value in Excel

To highlight the minimum value in Excel, repeat the entire procedure described above. Only instead of the MAX function, there should be a MIN function. This function allows you to select the smallest value in an Excel table. And instead of green fill color, choose red.

There must be 2 conditional formatting rules applied to the same range. To check, select the tool: “HOME” - “Styles” - “Conditional Formatting” - “Manage Rules”

The final effect of applying two conditional formatting rules to highlight the largest and smallest values ​​in column B (Expenses):

The operating principle of both rules is the same. They differ only in the =MAX() and =MIN() functions. Moreover, the function arguments are identical. The first function =MAX() searches the immutable range with absolute references $B$2:$B$10 for the maximum value. After the function there is a comparison operator with the current cell being changed which has a relative reference starting from B2. The conditional formatting rule reads this formula as follows: if the number is the largest, then highlight it in color. As soon as the maximum number (returned by the MAX function) matches the value of the current cell, the formula returns the Boolean value TRUE and the format with the corresponding fill color is immediately applied. The rule for smart formatting of column cells also works with the MIN function.

Highlight the three smallest values ​​in a row

In various situations, automatic highlighting of the three cells with the lowest values ​​is useful. Conditional formatting with appropriate formulas does a great job of this. Example data table:

Let's complicate the task a little. The cell with the lowest value should automatically have a red fill color. The next cell with the second lowest value will have an orange fill color. And the third lowest value is yellow.

To achieve this effect, follow these steps:

The three smallest values ​​are automatically highlighted in different colors.

Attention! If the range being viewed contains several identical minimum values, all of them will be highlighted in color.

In a similar way, you can highlight the largest values ​​in color. Simply change the function SMALL to MAXIMUM in the formula. For example: =LARGEST($B$2:$B$9,3)=B2

Least and greatest function in Excel

The function =SMALL() searches for the next smallest value in the range specified (in the first argument). The queue is specified in the second argument of the function. That is, if we specified the number 3 in the second argument, then the function returns the third smallest value in the given range $B$2:$B$9. If the result of a function calculation is equal to the current cell, then the corresponding format (fill color) is assigned to it. It is important to note that in the formula for viewing a range we use absolute reference addresses, and after the comparison operator =B2 we use relative ones, since the value of the function is compared with each cell of the selected range. And the LARGE function works in inverse proportion.

Helpful advice! If there is no need to split the 3 smallest values ​​into different colors, then it is not necessary to create 3 conditional formatting rules for the same range. It is enough to simply change the formula a little by adding just one operator symbol: =SMALL($B$2:$B$9;3)>=B2. That is, greater than or equal to.

All the methods described are good because when you change the values ​​in the cells, they will still work automatically.

Finding the maximum/minimum value is a simple task, but it becomes somewhat more complicated if the MAX/MIN need to be found not 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 numeric.

To make it easier to understand the formulas, let’s create two for each column: Text (A 6: A 30 ) And Numbers (B6:B30 ). (see example file).

Let's consider several tasks:

A. Let's find the maximum value among those numbers that correspond to the value Text1(enter the criterion 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 row in the column A the text value is Text1. Let's write (do not forget to press when entering the formula CTRL+SHIFT+ENTER):
=HIGHEST(IF(A6:A30=E6,B6:B30,"");1)

or with Named ranges:

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

Part of the formula Text=E6 , 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, select this part of the formula in and press the key). TRUE matches rows that have Text values contains 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 "" you could use any text symbol(letter) or omit it altogether (in this case the array will 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 LARGE() function with the second parameter =1 is used instead of the MAX() function, because if not a single line satisfies the criterion, then the formula = MAX(("":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"" :"":"": "":"":"":"":"":"":"")) will return 0!, which can be misleading. The LARGE() function in this case will return the error #NUM!

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

IN. We'll find it with help array formulas the minimum value among those that match the value Text3:
=MIN(IF((Text=E7);Numbers;"");1)

Those. if in column A 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. certainly NOT minimal. Next, the MIN() function returns the minimum value from the resulting array, and it is clear that none of the values, where<> Text3, will not distort the result (see problem A).

Another solution is 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:
=DMIN(A5:B30,B5,I10:I11)
where in the range I10:I11 contains the criterion =B6>AVERAGE(Numbers)

D. Let's find the maximum absolute value. From the picture above you can see that it 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 problems can be solved without using the DMIN() function. To do this, you will 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 Sheet without array formulas).

Multiple Conditions

The above approach can be extended to cases when 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, . In addition, criteria are used to select (see yellow cells).

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

If you work with large tables, you will definitely find duplicate amounts scattered along an entire column. At the same time, you may need to select data from a table with the first smallest numeric value, which has its own duplicates. We need automatic data sampling based on 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 sample from the table is needed according to the condition. Let’s say we want to find out the first cheapest product on the market from a given price list:

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

INDEX(data_range_for_sampling, MIN(IF(range=MIN(range),ROW(range)-ROW(column_header);””)))

In the place “data_range_for_sampling” you should specify the range of values ​​A6:A18 for sampling from a table (for example, text), from which the INDEX function will select one resulting value. The range argument refers to 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 should specify a reference to a cell with a column header that contains a range of numeric values.

Naturally, this formula should be executed in an array. Therefore, to confirm its entry, you should press not just the Enter key, but the entire 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 was entered into cell B3 in the array:

Sampling the corresponding value with the smallest number first:

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

How conditional sampling works

The INDEX function plays a key role here. Its nominal task is to select from the source table (indicated in the first argument - A6:A18) values ​​corresponding to certain numbers. INDEX works taking into account the criteria defined in the second (row number within 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.

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, several calculation functions are used.

The IF function allows you to select a value from a list based on a 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 of logical values ​​TRUE and FALSE is created in the program memory. In our case, 3 array elements will contain the value TRUE, since the minimum value of 8 contains 2 more duplicates in column B6:B18.

The next step is to determine which rows of the range contain each minimum value. We need this because the first smallest value is determined. This task is implemented using the ROW function; it fills the elements of the array in the program memory with the line numbers of the sheet. But first, from all these numbers, the number on opposite the first row of the table is subtracted - B5, that is, the number 5. This is done because the INDEX function works with numbers inside the table, and not with numbers in the Excel worksheet. At the same time, the ROW function can only return sheet line numbers. To avoid a shift, it is necessary to compare the order of the row numbers of the sheet and the table 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 the minimum values ​​have been selected and all row numbers in the table have been compared, the MIN function will select the smallest row number. This same row will contain the first smallest number that appears in column B6:B18. Based on this row number, the INDEX function will select the corresponding value from table A6:A18. As a result, the formula returns this value to 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, you can now 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 formula conditions so that you can select the first maximum in Excel, but less than 70:

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

As is easy to see, these formulas differ from each other 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 to suit many conditions and quickly solve many computational problems.

This spreadsheet processor can handle almost all calculations. It is ideal for accounting. There are special tools for calculations - 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, you don’t have to look for them yourself. It is better to use the options provided for this. It will also be useful to understand how to calculate the average in Excel.

This is especially true in tables with a large amount of data. If the column, for example, shows prices for products from a 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's 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 need 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 in which the cursor was located.

Next step - specify the 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. Just replace "MAX" with "MIN".

Average

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

Here's how to find the average in Excel:

1. Place the cell cursor anywhere free place tables.
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 range address. Or write several numbers in different fields “Number2”, “Number3”.
7. Click OK. The required value will appear in the cell.

This way you can carry out calculations not only with positions in the table, but also with arbitrary sets. Excel essentially 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 check mark next to the functions 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.

The Excel tool “Autofunctions” will help you find and calculate.

1. Place the cursor in a cell.
2. Find a button whose name begins 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 value), 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. “Stretch” the box around it to cover the range. Or press and hold the click on the grid Ctrl key to select one element at a time.
6. When finished, press Enter. The result will be displayed in the cell.

In Excel, calculating the average is quite easy. There is 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. This is much easier than counting by hand or looking for numbers in a huge table. Therefore, Excel is popular in many areas of activity where accuracy is required: business, auditing, human resources, finance, trade, mathematics, physics, astronomy, economics, science.