EXECLE conditional functions, what are conditional functions in excel How to use conditional functio

Assumption: The raw data in Table 1 starts from A2 and the raw data in Table 2 starts from A1.

Enter the formula in B2 in Table 1:

if(a2>=95, Table 2! a$1, if(a2>=91, Table 2! b$1, if(a2>=86, Table 2!

c$1, if(a2>=78, Table 2! d$1, if(a2>=72, Table 2! E$1, Table 2!

f$1)))

Select b2 and pull down the copy until the last line.

Suppose the data to be judged is in A2:

if(a2<100,"",if(a2<=599,"one",if(a2<=4999,"II. II. II","Three")))

1. The conditional function is the if function.

2. The if function is a function that determines true and false according to the conditions you give.

3. True means that the conditions are met, and false means that they are not met.

4. The second and third items can show what you want to meet or do not meet the conditions, for example, if true, then you only need to enter the second item to meet (note that the quotation marks should be English input method).

5. The if function can also be set into the function, for example, when the minimum value is selected in a group of data, and then the minimum value is compared with the condition, it is if(min (range) condition).

1. Add a column auxiliary column to the GL column, G2 formula = IF(A2<>0,A2,G1), copy to other cells in the G column.

2. First cancel the F column to merge the cells, add conditional formatting to the F2 cell, the formula =if(and(f2="Returned",countif(g:g,g2)=countifs(g:g,g2,b:

b,"<0,1), click "Format" and change the fill color to "Red".

3. Use the format painter to flash the format of F2 to other cells in column F, and then adjust and merge cells.

If it's just to change the color, just use conditional formatting.

In addition, if you only have 2 pieces of data, it is more direct to **. But if it's a lot of data, you have a lot of merged cells in between, and it's not so convenient to use conditional formatting.

The 2 conditions you said, first select f2, enter (corresponding cells) in the conditional formatting, and the second one is the same method, my cells are locked, and they are used in one cell, you can change them according to your specific situation:

As a reminder, merging cells (such as your e2, f2) only have content if the cell has content before the merge, and there is still no content after merging. For example, before merging F2F3, you fill in "not returned" in F3, and although F2 is shown to have content after the merger, there is actually no content.

=ifs(and(b2=""b3=""e2=""The deposit can be refunded","Don't make changes")

2 conditions, according to the above formula, change the conditions in and(), as for the display of red, you need to set the conditional formatting,

The if condition function in the excel function can open excle**, add data, enter =if(), and press the enter key to do nothing, as follows:

1. First of all, let's open an excle**.

2. In excle**, we have added some data, about the summary of Chinese and mathematics results, the last column is the statistics of the grade, now we want to divide the grade, Chinese and mathematics are greater than 80 points are rated as excellent, otherwise it is rated as poor.

3. We select the cell e2, which is the grade of the student with serial number 1.

4. Then in the input area of the function, we first enter =if(), which is the beginning of writing all if functions, and the if function uses the format of multiple conditions: =if(condition, the value when the condition is satisfied, and the value when the condition is not met).

5. Then let's write the conditions first, and we use the and function to combine multiple conditions. and(b2>=80,c2>=80) indicates that both conditions need to be met.

6. Then we write the value when satisfied and when not satisfied, when the conditions are satisfied, the value is "excellent", and when it is not satisfied, the value is "poor".

7. Finally, we press the enter key, at this time, e2 shows a "poor" grade, because neither Chinese nor mathematics is greater than or equal to 80, so the conditions are not met, and the grade is "poor".

8. We don't have to recalculate the level of the last remaining students, select the cell e2 with the mouse, and pull it down when the mouse presents the word "+", at this time, the level of the other students will come out.

Precautions:

In the if function, in addition to using and to concatenate multiple conditions, it is also possible to use or to concatenate them.

There are so many tips for using Excel, have you learned all the conditional functions?

What does the let function do?The official explanation given is this.

If your formula is long and hard to remember, you can give the formula a name and replace the long formula with a name.

Actually, this concept is not new, this is the concept of variables in programming!

Let's take a simple example.

For example, now, we want to calculate 5+1.

The ordinary formula is like this, which is not professional at all.

a2+b2 we can save the number 5 in x, then calculate sum(x,1), and then substitute 5 into the sum function, and calculate the result =6.

let(x,5,sum(x,1))

So the above formula can be divided into two steps to understand.

The first step is to save the 5 in x.

In the second step, replace the write with 5 to perform the sum calculation.

Isn't it amazing!

But I guess there must be someone who is jealous of my talent, and although he has long admired it in his heart, he still shows a look of disdain on the surface.

Let's look at another example.

Now there are two **, one is the list and the other is the transcript.

Now you have to fill in the list of results from the transcript, and then:

A pass in remarks with a score of 60 or more;

Less than 60 points show the original number.