Office hacks: half the work with a good MAX function

When you see the MAX function, don’t think it’s just as simple as finding the maximum number. In fact, it can also prevent incorrect values when calculating, enter consecutive serial numbers in intervals, add serial numbers to merged cells, perform multi-conditional lookups, etc. If you use the MAX function, in the office when half the work is not a dream.

1. to prevent the occurrence of incorrect values when calculating

In the use of multiplication of numerical values, if the value of the cell is non-numerical, using “*” to multiply the number and non-numerical together, then the results will certainly show the wrong value. But if you use the MAX function and then add the “*” sign, you can avoid the appearance of the error value. The operation is very simple, just need to refer to the cell plus the MAX function can be (Figure 1).



MAX function will ignore the text, spaces.

2. interlinear input continuous serial number

If you need to automatically fill the next line to enter the serial number, just enter the formula “= IF (B2 <> “”, MAX ($ A $ 1: A1) + 1, “”)” in cell A2, and down the continuous filling on it (Figure 2).


Formula explanation.

First determine whether B2 is empty, if not empty, then compare the value of A1 and A1, and take the maximum, as MAX will ignore the text, so get the maximum value of 0, and then add 1, so the value of A2 becomes 1; then compare the value of A1 and A2, take the maximum, after adding 1, the value of A3 becomes 2; and so on.

3. add serial numbers to the merged cells

For the case of merging cells into a continuous serial number, first select all the merged cells, enter “=MAX($A$1:A1)+1” in the formula editing bar, and then press Ctrl+Enter at the same time on it (Figure 3).


4. multi-conditions to find the extraction of the latest business date

For multi-conditional search, MAX function is also handy. In cell F2, enter the formula “= MAX (($A$2: $A$12 = E2) * $B$2: $B$12)”, as this is a composite formula, so you need to hold down Ctrl + Shift + Enter three keys, and then fill down (Figure 4).


Formula explanation.

First determine whether the data in A2: A12 and E2 values are equal (equal to 1, not equal to 0, take the maximum), that is, take out A2: A12 and E2 equal values, while obtaining these equal values corresponding to the maximum value in column B.

Similarly, in accordance with this formula, you can also extend the use of more scenarios, such as finding the group according to the name, you can enter the formula in the corresponding cell, while holding down Ctrl + Shift + Enter three keys, and fill down (Figure 5).


Leave a Comment