Continue our highlight of yesterday: Some Funny Formulas in Excel (1). Today's second article is mainly about three functions and a formula. The general contents are as follows:

  • What is an if function? What's the usage?
  • What is the mod function? What can it be used for?
  • In addition to picking up numbers, what kind of sparks can mid make with other functions?
  • Is there any way to calculate the child's age on the specified date through the ID card number? For example, how old is the child on December 31, 2020?

The title says "Formula". Why are all functions here?

Quite simply, you can combine the following interesting formulas only after you have the appropriate function.

Of course, what we have said today will be related to ID cards. After all, we often have something to do with our daily work.

1. If function

The IF function is generally used to judge "true" (TRUE) and "false" (FALSE).

In Excel, the usage is as follows:

 =If ("judging conditions", "results meeting conditions", "results not meeting conditions")

For example, in Excel, A3 is 100. We need to output the judgment results in B3. If the number is greater than 100, the output is "good student". If the number is less than 100, the output is "not bad".

So how to write it? Enter the following directly in B3:

 =If (A3>100, "good student", "not bad")

Can I only play the if function here? Of course not. Remember the formula of "judging gender by ID number" mentioned in some fun formulas in Excel (1) yesterday?

 =If (mod (mid ("cell of ID number", 17,1), 2), "male", "female")

ad locum, Mod (mid ("cell of ID number", 17,1), 2) It is the judgment condition. So the question is, what is the mod function?

2. Mod function

In Excel, mod function is a remainder function.

First say how to use it, then how to use it.

 =Mod ("dividend", "divisor")

The result returned is the remainder.

For example, if we divide 4 by 2, the remainder is 0. If we divide 3 by 2, the remainder is 1.

What if it is 1 divided by 2?

In the mod function of Excel, the integer value rule that the dividend is less than or equal to the divisor:

mod(1,3)=1

mod(2,3)=2

So, where can it be applied in the office?

Remember the front? Where do we use it?

Extract gender by ID number 。

So, what is the gender of the 18 digit ID card number? 17th, odd number is male, even number is female.

How to extract? That's the mid function.

3. Mid function

In Excel, the mid function is used to extract a specified number of characters from a string (that is, from the contents of a cell).

For example, we can specify to start from the 16th word in a cell and extract the last 32 words. How to use it?

 =Mid ("the content you extracted", "starting from the first word", "how many extracted")

How can we use it according to the above requirements? Suppose we extract 32 words from the 16th word in cell A3. How can we use them?

 =mid(A3,16,32)

What if, starting from the 0.1 word?

Mid will tell you directly that my wife and I can't do it. This is the error value returned by my wife and I.

What would happen if you extracted from the 15th cell in a cell with only 5 words?

Mid will say "No" with a sad face, and then return a blank value to you.

Back to the previous topic, how to extract the gender from the ID number?

If A3 is the ID number, then we can directly:

 =mid(A3,17,1)

It is OK to extract 1 bit from the 17th bit.

4. Combination of functions

Let's combine the three functions we talked about today.

If function is to judge whether a value does not meet the requirements;

The mod function is a direct complement.

The mid function extracts the content directly.

Then, after we see the ID number (assuming it is A3), it is simple to know the gender:

 =If (mod (mid (A3,17,1), 2), "male", "female")

What do you mean? Extract the 17th digit of A3 (ID card number). If there is a remainder (how can an odd number be divided by 2 to get a clean 0?), then it is a man; If there is no remainder (an even number divided by 2 has no remainder), then it is female.

It's easy, isn't it? Come on, take a form to practice your hands?

Yesterday, I said that I would also like to share a formula, which is to obtain the age on the specified date through the ID number. (one year old)

For example, calculate the age of the child until today, or until December 23, 2035.

Assuming A3 is the ID number, the formula is as follows:

 #This is a comment, not a formula. #Calculate the age of the child to today =datedif(text(mid(A3,7,8),"0000-00-00"),today(),"Y") #Calculate the child's age on December 23, 2035 =datedif(text(mid(A3,7,8),"0000-00-00"),"2035-12-23","Y")

In the formula, there are new functions, datedif function and text function. We will continue tomorrow.

Although today's article has only a few points, there will be more fun tomorrow.

  • Is the datedif function delicious? What can I do?
  • What does the text function do?
  • How to verify the authenticity of ID card number through Excel.

Why are you talking about this?

It is not necessary to record the new student status, but it is also afraid that the head teacher may mistake the ID number of students and parents, which will lead to the failure of uploading the system and rework.

So I'm going to share this good formula with you.

End of full text[ Like this article, reward the author! ]