How to find the minimum value in Excel. Minimum value excluding zeros Maximum and minimum in Excel

Create a new file by clicking the New button on the Quick Access Toolbar. Rename Sheet 1 to Cost Analysis. We will develop a table for the analysis of Investment costs.

Figure 23. Analysis of investment costs

Exercise: Calculate the column cost using the formula. For the "Unit Price" and "Cost" columns, use the currency format.

In cell F12, calculate the total investment cost. This can be done in 2 ways:

Method 1:

Activate cell F12;

Enter the formula in it: = F5 + F6 + F7 + F8 + F9 + F10 + F11 and press Enter.

2 Method using the function:

A function in MS Excel is a formula in which certain mathematical operations are replaced by the names of functions. For example, to add multiple values, use the SUM function.

Let's replace the usual formula in cell F12 with the formula using SUM:

Exercise: Determine the maximum value of your investment costs. For this:

Activate the cell in which you want to get the result (F13);

To the right of the Amount button in the Editing group on the tab home click the arrow with the dropdown list of functions and select the function Maximum(selects the largest value from the selected range);

The formula = MAX (F5: F11) appears in cell F13, but F12 must be excluded from this range. Place the mouse pointer in the form of a white bold cross in the middle of the first cell of the range (F5), extend the selection to cell F11;

Hit Enter.

Exercise: Determine the minimum and average value of the cost of investment costs.

Function Minimum (= MIN)- reveals the smallest value of the range;

Function Average (= AVERAGE)- calculate the average value of the range, i.e. adds all the elements and divides by the number of elements in the addition range.

Function "If"

The management of the company, having considered the analysis of investment costs, decided this month to pay only those positions, the cost of which is less than 45,000 rubles.

Let's add the column "Payment" in the table, in which it is necessary to indicate:

If the cost is more than 45,000 rubles. - "Postpone";

If the cost is less than 45,000 rubles. - "In payment".

Activate cell G5, open the tab Formulas, press the button Insert function.

In the dialog box, select a category brain teaser, in the list of functions - IF.

Figure 24. IF function

Pull the value in cell G5 to the end of the table (to G11).

Using multiple conditions when applying the "IF" function

Go to the second sheet of your book and rename it Salary. Enter the following details:

Figure 25. An example of calculating a premium for sales

The IF function consists of the following elements: function name, condition, values if the condition is true, values if the condition is false. The function entry in the cell looks like this:

If (condition; value if conditions are true; value if conditions are false)

Consider an example when there are two conditions: if the sales volume of an employee is more than 50,000 rubles, then the bonus will be 10% of the sales volume, otherwise 5%.

This record will look like this for our example:

Figure 26: Example of calculating a premium for sales with one condition

Consider a second example, where the employee bonus for sales volume will be calculated as follows:

If an employee sold goods for an amount greater than or equal to 90,000 rubles, his bonus will be 10% of the sales volume, if the amount of goods sold is more than or equal to 30,000 rubles, the bonus will be 5% of the sales volume, if the amount is less than 30,000 rubles, a bonus will amount to 3%.

Figure 27. An example of calculating a premium for sales with multiple conditions

In this case, there are several conditions and the function record will look like this:

Conditional formatting

Conditional formatting allows you to set a cell different formats under different conditions. For example, filling cells with sales of more than 30,000 rubles. should be red, less than 30,000 rubles. - yellow.

Select the range C2: C5;

In the tab Home in group styles click on the button Conditional formatting and choose a team Cell selection rules ... Other rules.

Set the format, if the value of the cells is more than 30,000, click OK.

Figure 28. Conditional Formatting

Perform the same action and set the format for cells with a value less than 30,000 rubles.

Try the commands: Histograms, Color Bars, and Icon Sets from point "Conditional Formatting".

In order to remove the format that was applied in the conditional formatting, you must click the button Conditional formatting ... Remove rules.

Calculation of loan payment

Let's calculate the amount of the loan payment using the PMT function.

Go to Sheet 2 and rename it "Credit". Enter the initial data for the calculation. Please note that in cell B2 - the annual amount of interest on the loan is displayed. Cell B5 contains the formula.

Figure 29. Calculation of loan payment

To calculate the monthly loan payment you need:

Activate cell B6;

In the tab Formulas push the button Insert function... Select in the category Complete alphabetical listing, and below find the function PMT.

Figure 30. Inserting PMT function

In field Bid specify the monthly rate, i.e. B2 / B4;

In field Nper- it is necessary to indicate the total number of periods, i.e. AT 5;

In field Ps (Present Value)- it is necessary to indicate the requested loan amount;

Bs - cash balance, which needs to be reached after the last payment, in our case it is 0;

A type- 0 - payments are made at the end of the period, 1 - payments are made at the beginning of the period.

The result that you get should be with a minus sign, because these are payments, not cash receipts.

Absolute and relative addressing

Go to Sheet 3 in the book and name it Water consumption... Enter meter reading data by building a table:

Figure 31. Example of a table

Attention! The values of the cost of m3 of cold and hot water must be placed in cells separate from the text, since they will be used in formulas for calculating the amount to be paid.

Water consumption is calculated as the difference between the meter readings of the current month and the previous one:

Activate D9, enter the formula: = B9-B8 and press Enter.

Use the auto-fill point to calculate the cold water consumption for the remaining months.

Calculate in the same way Hot water consumption.

Amount to pay for cold water in January is calculated as the product of the amount of consumed water by the cost of m 3. The formula for our example:

Activate cell F9;

Enter the formula: = D9 * E4 and press Enter%

Let's try to stretch the formula using the autocomplete point:

Return to cell F9, move the mouse over the lower right corner of the cell, catch the mouse pointer in the form of a black cross, hold down the left mouse, drag the formula to the end of the table.

Figure 32. The need to use absolute addressing

Make sure autocomplete didn't work, let's see why:

Activate cell F9, the formula should appear in the formula bar: = D9 * E4;

Activate cell F10. Excel, using autocomplete, changed the formula and now calculate the value of cell F10 as = D10 * E5. That is, all cells that are used in the formula when the formula is copied by autocomplete down are also shifted down. In our case, moving down the column D is necessary, because for each month there is a different value of water consumption, but movement along column E is not necessary, because you need to multiply by E4.

Solution:

Back in cell F9, look at the formula bar: = D9 * E4.

In the formula, you need to find a cell that should not change when copied. This is E4.

In the formula bar, place the cursor in the formula between E and 4. And then press the F4 key on the keyboard, the formula will look like = D * $ E $ 4. The $ signs for Excel mean the fixing of this cell or absolute addressing, when you copy this formula with an autocomplete point, this cell will not change.

Press Enter, go back to cell F9 and drag down the autocomplete point. Select cell F10 - notice the formula.

Exercise: Fill in the column Amount to be paid for GW using absolute addressing and column Total payable- as the sum over columns F and G.

Save the file in your folder under the name "Functions".

In the process of preparing various kinds of ranking or analyzing success indicators, it is very convenient to highlight the best or worst result in color. How to highlight maximum value in Excel with color? This is where conditional formatting helps a lot. The advantage of this approach is that the automatic highlighting of the best / worst indicators remains functional even when data is added or removed from the table.

How to highlight with color the maximum value in Excel

Let's take a table of expenses for example:

To highlight the highest and lowest costs at lightning speed, do the following:

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

How to choose the minimum value in Excel

To highlight with color 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 Excel spreadsheet... Choose red instead of green.

2 conditional formatting rules must be 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 high and low values in column B (Expenses) is:

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

Highlighting the three lowest values in a row

In different situations, it is useful to automatically highlight the three cells with the lowest values. Conditional formatting with appropriate formulas does an excellent job of doing this. Example data table:

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

To achieve this effect, follow these steps:

The three lowest values are automatically highlighted in different colors.

Attention! If the range you are viewing contains several identical minimum values, they will all be highlighted in color.

Similarly, you can highlight the highest values with color. Just change the function SMALL to LARGE in the formula. For example: = LARGE ($ B $ 2: $ B $ 9,3) = B2

Smallest and largest function in Excel

Function = SMALL () searches in the given (in the first argument) range for the next smallest value. The queue is specified in the second argument to the function. That is, if in the second argument we specified the number 3, then the function returns the third in turn smallest value of the specified range $ B $ 2: $ B $ 9. If the result of calculating the function 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 the range, we use absolute reference addresses, and after the comparison operator = B2 - relative, since the value of the function is compared with each cell of the selected range. And the LARGE function works in inverse proportion.

Useful 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 just to slightly change the formula by adding just one operator symbol: = SMALL ($ B $ 2: $ B $ 9; 3)> = B2. That is, more or equal.

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

If you have to work with large tables, you will definitely find duplicate amounts in them scattered along an entire column. At the same time, you may need to select data from the table with the first smallest numerical value, which has its own duplicates. We need automatic data selection 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, the first smallest number needs a selection from the table by condition. Let's say we want to know the first cheapest product on the market from this price list:

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

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

In the place "data_range_for_fetch", 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. Range refers to the range of numeric cells from which to select the first lowest number. In the column_heading argument for the second ROW function, you must specify a cell reference with a column heading 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 was entered in an array into cell B3:

Sampling the corresponding value with the first smallest number:

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

How conditional fetching works

The INDEX function plays a key role here. Its nominal task is to choose 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 inside the table) and third (column number in the table) arguments. Since our original table A6: A18 has only 1 column, we do not specify the third argument in the INDEX function.

Several computational functions are used to compute the table row number against the smallest number in the contiguous range B6: B18 and use that as the value for the second argument.

The IF function allows you to select a value from a list conditionally. Its first argument specifies where each cell in the range B6: B18 is checked for the smallest numeric value: IF B6: B18 = MIN B6: B18. In this way, an array of logical values TRUE and FALSE is created in the program memory. In our case, 3 elements of the array 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 in which lines of the range each minimum value is located. We need this because of the determination of the first smallest value. Implemented given task using the LINE function, it fills the array elements in the program memory with the sheet line numbers. But first, the number on opposite to the first row of the table is subtracted from all these numbers - B5, that is, the number 5. This is done because the INDEX function works with numbers inside the table, and not with numbers in an Excel worksheet. At the same time, the LINE function can return only the row numbers of a sheet. In order not to get an offset, it is necessary to compare the order of the line numbers of the sheet and the table using the subtraction of 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 are selected and all the row numbers of the table are compared, the MIN function will select the smallest row number. This same line 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 and customize it for other conditions. For example, the formula can be changed to select the first maximum value in Excel:

If you need to change the conditions of the formula 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:

It is easy to see that these formulas differ from each other only by 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 for determining the minimum value. However, finding the minimum value by condition is problematic. A function from the add-in can cope with this task. = MINSESLI

(similar to standard Excel functions SUMIF).

V Excel versions 2016 and above there is a built-in function MINESLEY, you can use it. If your Excel is earlier version, then this function can be used by installing the add-in VBA-Excel.

The function has the following arguments = MINESLY ( RANGE; CRITERION; [RANGE_SEARCH])

RANGE- The range of cells to be checked.

CRITERION- A condition in the format of a number, expression, or text that determines whether the minimum value is checked.
[RANGE_SEARCH]- Actual range for determining the minimum value. If this parameter not specified, the cells specified by the parameter will be used RANGE.

Example 1

Values and logical expressions can be specified as a criterion:

Consider the following example in which the minimum score for the literature is determined. To do this, in the parameter CRITERION the value "Literature" is specified, and the parameter RANGE- list of items.
If you specify as a criterion boolean expression «<>Russian", then the minimum mark in all subjects with the exception of the Russian language will be determined.

Example 2

In the following example, the parameter RANGE_SEARCH not specified, therefore 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 bookkeeping. 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, it is not necessary to search for them yourself. Better to use the options provided for this. It will also be useful to figure out how to calculate the average in Excel.

This is especially true in tables with a large amount of data. If, for example, a column contains product prices 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 it 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:

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 box and click Find.
In the "Arguments" window, enter the addresses of the range, the maximum value of which 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 it includes.
Instead of an address, you can write several numbers. Then the system will show at most of them.
Click OK. The result will appear in the cell where the cursor was.

The next step is to 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 choose 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 the amounts, find out how many cells are in a line, and so on. But this is too complicated and time-consuming. 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's how to find the average in Excel:

Place the cell cursor anywhere 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 appears in the cell.

So you can carry out calculations not only with positions in the table, but also with arbitrary sets. Excel is essentially 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 working 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 checkmark next to the function field. Or just hit Enter. The desired value appears in the selected cell.
The formula can be copied directly into the cell itself. The effect will be the same.

The Excel AutoFunctions tool will help you find and calculate.

Place the cursor in a cell.
Find the button whose name begins with "Auto". It depends on the default option selected in Excel ("AutoSum", "Autonumber", "Autoshift", "Autoindex").
Click on the black arrow below it.
Select MIN (minimum), MAX (maximum), or AVERAGE (average).
A formula appears in the marked cell. Click on any other cell - it will be added to the function. Drag a box around it to cover the range. Or click on the grid while holding down the Ctrl key to select one element at a time.
Press Enter when done. The result will be displayed in the cell.

In Excel, calculating the average is easy enough. 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 digging up numbers in a huge spreadsheet. Therefore, Excel is popular in many areas of activity where accuracy is required: business, audit, HR administration, finance, trade, mathematics, physics, astronomy, economics, science.