Shulou(Shulou.com)11/24 Report--

The original title: "I was really convinced when I saw him use the MOD function like this." "

Next door Xiao Wang, Xiao Shuang and I are good friends, and we often discuss Excel-related issues together.

One day, Xiao Shuang pointed to the apple on the table and asked Xiao Wang and me:

If you divide the 12 apples on the table among five children, how many are left?

Xiao Wang immediately said: isn't it just begging for surplus? It can be done with a MOD function! The formula is as follows.

The MOD (12BI 5) = 2MOD function looks very basic and simple, but it doesn't reveal it!

With the MOD function, you can also solve so many common problems:

❶ extracts time from date

❷ calculates the cross-day working hours

❸ judges gender by ID card number.

❹ judges the week by date

❺ fills colors according to conditions

……

Do you really want to learn?

Before we officially start school, let's take a look at the syntax rules of the MOD function.

After all, you know yourself and you win a hundred battles!

The MOD function is a remainder function that returns the remainder of the division of two numbers.

However, it is different from the remainder in the mathematical sense, in the mathematical sense, the remainder is a non-negative number, and the remainder obtained by this function can be negative, and the symbol is the same as the divisor.

The syntax of the MOD function:

= MOD (number,divisor) that is: = MOD (divisor, divisor)

Note: the divisor cannot be 0, as to why. Ask your primary school teacher!

1. Calculate the time with the MOD function ▌ extract the time from the date in the following figure, we need to extract the time from the date in column B.

Enter the formula in the C3 cell:

= MOD (B3prime1) then drop down and fill it, and it's done!

In Excel, the essence of a date is a numeric value. The date is the integer part and the time is the decimal part.

For example, enter "12:00 on 2020-4-20" in the cell, change the cell format to normal, and you can get the number 43941.5.

43941 is the date part and 0.5 is the time part.

When we use the MOD function to find the remainder:

= MOD (43941.5) gets a result of 0.5. if you set the cell to time format, you get 12:00, which is the time part.

Hit the main point:

Formula: = MOD (date time, 1) when the second parameter of the MOD function is 1, the remainder of the date time is obtained, and the result is the time in the date.

▌ calculates the cross-day time as shown in the following figure, and calculates the number of hours worked according to the commuting time.

Enter the formula in cell D3:

= MOD (C3Musi B3jue 1) * 24

23:00-24:00 of that day (1 hour) + 0: 00-9: 00 (9 hours) of the next day = 10 hours. So the result is 10 hours.

Let's see how it comes from using the MOD function.

We knew earlier that the MOD function can get the time in the date time, so it can also get the time in the time interval.

The formula is:

= MOD (date time, 1) use the formula of MOD function:

= MOD (C3murB3Power1) * 24=MOD (C3murB3Power1) calculates the time difference between the two, which is a decimal.

Because 1 day equals 24 hours, we need to multiply it by 24 later to convert it into hours.

2. Use the MOD function to judge the gender as shown below, how to know the gender according to the ID card number.

Enter the formula in the C3 cell:

= IF (MOD (MID (B3jor17) 1), 2), "male", "female") and then drop down to fill it.

The 17th digit of the ID card is an odd number indicating a male and an even number indicating a female.

Mini resolution:

First use the MID function to extract the 17th digit of the gender

Then use the MOD function to determine the parity of the number:

If the number is divided by 2, the remainder is 0, it is even; if the remainder is 1, it is odd.

Finally, the IF function is used to judge that if it is odd, it is male, otherwise it is female.

3. Use the MOD function to judge whether the date in column B in the following figure is a weekend, and return "yes" if it is Saturday and Saturday, otherwise an empty character is returned.

Enter the formula in the C3 cell:

= IFMOD (B3pd7) 2, "Yes", "and then drop down the fill.

First, let's list a set of consecutive dates, as shown in column B below.

Then use the MOD function to divide the date by 7 to get the remainder as a set of integers that cycle from 0 to 6, as shown in the following figure C column.

Customize the date format of column B to "aaa" to display the day of the week of the date, such as column D.

As can be seen, the number 0-6 corresponds to Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday and Friday in turn.

= IFMOD (B3P7) 2, "Yes", "use the IF function to determine whether the cyclic value is less than 2, and if it is less than 2 (0 and 1), it is the weekend, otherwise null characters are returned.

4, using the MOD function to fill the color of this case is a bit difficult, will involve the array formula, we can as an understanding.

As shown in the following figure, when we beautify the table, we may need to fill the same department with color:

If you set the color manually one by one, only a few departments will be fine.

If there are many departments and forms, this operation is very inefficient.

In fact, there is no need to be so tedious, please see the following operation!

The focus of the operation is the conditional format formula:

= MOD (SUM (N ($B$2:$B2 $B$3:$B3)), 2)

N ($B$2:$B2 $B$3:$B3) Mini Resolution:

❶ judges whether the next line of the department's line is the same as the upward line, and accumulates once if it is different; the N function converts the logical value True or False into the value 1 or 0

❷ then accumulates the sum with the SUM function

Finally, ❸ uses the Mod function to determine the parity of the accumulated sum, and fill in the color if it is odd.

Finally, I turned around and asked Xiao Wang: do you still think the MOD function is simple?

Xiao Wang said in surprise: unexpectedly, the small MOD looks very simple, but in fact it is really not simple!

5. Summarize ❶ when the divisor of MOD function is 1, the time can be extracted when the date and time exist at the same time, and the cross-day time difference can be calculated.

❷ when the divisor of the MOD function is 2, you can judge the parity of the number.

❸ the week of the judging date can be calculated when the divisor of the MOD function is 7.

Of course, in addition to the above functions, the use of the MOD function is much more than these.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.