A CONDITION SETTING IN AN EXCEL MULTI CONDITION FUNCTION

Use. if function.

Insert the original formula to directly exclude the case where the calculation result is 0.

For example, enter the formula in C1.

if (original formula = 0,1, original formula).

If only to "one does not use."

Circular references. conditions".

Please don't ask me what the answer is.

If you feel that these are not the answers you want, you have two solutions.

1 If you don't have to display the result on C1: or you can use another box instead of the formula for C1 itself.

Type the formula into whatever box you want, 1,a1+b1)2: You can only put the original formula of c1 into another if function, then you have to provide more details to solve it, assuming the formula for c1 = a1+b1

Add =(if(a1+b1=0,1 to c1, e.g. d1d1a1+b1

The second method of c1if(d1=0, then point to that cell with the if function at c1, d1) allows you to call d1 in other formulas without having to enter its original formula.

Let's say your data is in a2:c2000, enter the formula at the first question mark.

sumproduct(($a$2:$a$2000=f$1)*(b$2:$c$2000=$e2)*(c$2:$c$2000))

Copy this formula to other cells.

The If function in excel uses multiple strips of silver to sing the following method:

Tools Raw Materials: Lenovo Xiaoxin, Windows7,.

1. Enter a set of data in column A cells in Excel**, which is used to use the if function to input five conditions for data operation.

2. Enter the if formula in cell B2: =if(a2<60,"1",if(a2<100,"2",if(a2<200,"3",if(a2<280,"4",if(a2<350,"5"It means that if the data is less than 60 in the A2 cell, the number 1 is displayed, if the data is less than 100, the number 2 is displayed, and so on.

3. Click the Enter key to generate the formula calculation result, and the displayed result is the number "1", which means that the data in cell A2 is less than 60.

4. Drag the formula of column B2 to fill it down to calculate the interval of the data entered in column A in batches.

Use the if function to judge:

1. Open Excel, open your data file, click "Insert" on the toolbar in the cell you want to output data - Function - Logic - IF, and then click OK.

2. After clicking OK, a box will pop up, the first one represents the condition, and the last two are the results of the judgment condition. Click on the marked place to select the data, generally do not need to select all the data, just select one of them.

Choose A2 to use IF to judge math score and failure. If the condition < 60 points, it means that it is not true, and it will be shown as a failing grade, otherwise the rest will be a passing score.

3. After clicking OK, you can see the result of the judgment, put the mouse behind the first result, when the mouse becomes [+], double click it to get all the results.

4. Set the display pass score of 60-70 points, and then in the same way, in the third row, click if again to nest.

5. The same method, set up good and excellent, if you want to continue to score later, you can also continue to nest, then don't write excellent in the third line.

6. Similarly, put the mouse in the lower right corner of the first result [+] and double-click to display all the results.

Precautions. The above methods are limited to novices, if you are proficient, you can directly write your judgment formula in the formula column.

The steps to set multiple conditions for the excelif function are as follows:

1.For example, if the amount of sales is greater than or equal to, the reward is; Sales greater than or equal to the bonus amount; The sales are less than and there is no incentive payment, so the incentive payment is.

Select the cell you want to enter a function formula to start typing formulas. Enter [=if], the equal sign must be entered, and all the punctuation marks of the input formula need to be entered with the English input method.

2.Then enter parentheses, [(b2>=4000,"500",] Cell B2 is the sales volume of the employee numbered, and the conditions to be filled in should be from large to small, so you should enter "=4000" instead of "=3500" first. When the sales volume is greater than or equal to 4000, this should be entered in English double quotation marks in the formula, and then separated by English commas, and continue to write the next condition.

3.Heavy wheel slag new input a condition, you need to enter if and parentheses, the format is the same as the previous step, that is, [if(b2>=3500,"300",] are also separated by commas.

4.Enter the last condition if(b2<3500,"0"After entering, you don't need to enter a comma, just enter the parentheses on the right.

5.Because there are three if conditions, you need to keep the three right brackets at the end, hit the enter key to determine the formula, and exit the cell Lapei quietly.

6.Select the cell, you can see the complete formula above **, put the mouse in the lower right corner of the cell, drag the mouse when the cross symbol appears, and copy the formula to the cell below. This solves the problem of setting multiple conditions for the excelif function.

Usage of the if function = if (condition, the value of the condition that is true, the value when the condition is not true).

Suppose the A1 cell returns "no value" when the content of the A1 cell is empty, and the B1 cell returns "with value" if the A1 cell has any content. Then you can write the formula =if(a1=) in cell b1""There is no value","There is value")

If there are multiple conditions, then the formula can be written as follows (take the three if conditions as an example) = if(the value of condition 1, condition 1 when it is true, if (the value of condition 2 and condition 2 when it is true, if (the value of condition 3 and condition 3 when it is true, and the value to be returned when all of the above conditions are not successful))).

For example, if the value of cell A1 is less than 60 points, the return value of cell B1 is "fail", if the value of cell A1 is less than 70 points, the value of cell B1 is "pass", if the value of cell A1 is less than 90 points, the return value of cell B1 is "good", and if the value of cell A1 is above 80 points (including 80), the value of cell B1 is "excellent". Then the formula for cell b1 can be written as =if(a1<60,"Fail",if(a1<70,"Pass",if(a1<90,"Good","Excellent"))

Microsoft Excel is one of the components of Microsoft Office, Microsoft's office software, which is a spreadsheet software written and run by Microsoft for Windows and Apple Macintosh operating system computers. Excel is an important part of Microsoft Office suite, which can carry out various data processing, statistical analysis and auxiliary decision-making operations, and is widely used in management, statistical finance, finance and many other fields.

1. Open the excel data table that needs to be used for data statistics, and select Bisui in the selected cell to insert the formula Repentance.

2. In the box selection of the formula, we first select the if function.

3. What we need to note here is that if you add a formula in this way, you don't support the multi-condition judgment of the if function.

4. So we need to change a way of thinking. We make judgments by directly entering the formula in the cell, for example, typing in the formula is as follows=if(d2="","LED products",(if(d2="","DLP products","LCD products"))

5. After entering the formula in the first row of cells, drag and drop it to fill all the cells, and you can quickly judge the result.