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; 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) Maximum 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:
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 Maximum 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 41 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 Best permutation: 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:
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 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); 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; 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:
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: 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 Original 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 Best permutations : 28800 Calculation Largest amount: 463 Best permutations, no less: 28800 Runtime = 00:06:26.81
ANALYSIS OF RESULTS
In all considered cases, testing completely confirmed the proposed general solution.
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.
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.
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.
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
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.
Excel has a function for determining the minimum value. However, finding the minimum value by condition is problematic. The function from the add-in can cope with this task =MINSIF(similar to the standard Excel SUMIF function).
In Excel version 2016 and above there is a built-in function MINESLEY, you can use it. If your Excel is an earlier 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 cells to be checked.
You can specify values and logical expressions as criteria:
In the following example, the parameter SEARCH_RANGE is not specified, so the minimum value is determined among the cells specified in the parameter RANGE.
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.
Here's how to find the maximum value in Excel:
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".
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:
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.
The maximum, minimum and average can be found in other ways.
The Excel AutoFunctions tool will help you find and calculate.
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.
Initial data
The source data can be any numeric data set, 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 either an additional column with an intermediate formula or one array formula
1. Solution with an additional column
In B1 write the formula
IF(A1<>0;A1;"")
In C1 write the formula
It will return the desired result - a number 3
IF(A1<>0;A1;"")
IF(A1>0;A1;"")
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), after which using the IF() function we transform the array into the following: (5:7:LIE:LIE:3). Since the function MIN() ignores text and logical values, then in the end we get the desired number - a number 3
If there are negative numbers in the range, then instead of the formula
MIN(IF(A1:A5<>0;A1:A5))
MIN(IF(A1:A5>0,A1:A5))
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
