Ms excel: if function (WS)
Yüklə 456,44 Kb.
|
 MS Excel: IF Function (WS)
This Excel tutorial explains how to use the Excel IF function with syntax and examples. Description The Microsoft Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE. Syntax The syntax for the IF function in Microsoft Excel is: IF( condition, [value_if_true], [value_if_false] ) Parameters or Arguments condition The value that you want to test. value_if_true Optional. It is the value that is returned if condition evaluates to TRUE. value_if_false Optional. It is the value that is return if condition evaluates to FALSE. Applies To The IF function can be used in the following versions of Microsoft Excel:
Type of Excel Function The IF function can be used in Microsoft Excel as the following type of function:
Example (as Worksheet Function) Let's look at some Excel IF function examples and explore how to use the IF function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following IF examples would return: =IF(A1>10, "Larger", "Smaller")
=IF(A1=20, "Equal", "Not Equal") Result: "Not Equal" =IF(A2="Tech on the Net", 12, 0) Result: 12 Combining the IF function with Other Logical Functions Quite often, you will need to specify more complex conditions when writing your formula in Excel. You can combine the IF function with other logical functions such as AND, OR, etc. Let's explore this further. AND function The IF function can be combined with the AND function to allow you to test for multiple conditions. When using the AND function, all conditions within the AND function must be TRUE for the condition to be met. This comes in very handy in Excel formulas. 
Based on the spreadsheet above, you can combine the IF function with the AND function as follows: =IF(AND(A2="Anderson",B2>80), "MVP", "regular")
=IF(AND(B2>=80,B2<=100), "Great Score", "Not Bad") Result: "Great Score" =IF(AND(B3>=80,B3<=100), "Great Score", "Not Bad") Result: "Not Bad" =IF(AND(A2="Anderson",A3="Smith",A4="Johnson"), 100, 50) Result: 100 =IF(AND(A2="Anderson",A3="Smith",A4="Parker"), 100, 50) Result: 50 In the examples above, all conditions within the AND function must be TRUE for the condition to be met. OR function The IF function can be combined with the OR function to allow you to test for multiple conditions. But in this case, only one or more of the conditions within the OR function needs to be TRUE for the condition to be met. 
Based on the spreadsheet above, you can combine the IF function with the OR function as follows: =IF(OR(A2="Apples",A2="Oranges"), "Fruit", "Other")
=IF(OR(A4="Apples",A4="Oranges"),"Fruit","Other") Result: "Other" =IF(OR(A4="Bananas",B4>=100), 999, "N/A") Result: 999 =IF(OR(A2="Apples",A3="Apples",A4="Apples"), "Fruit", "Other") Result: "Fruit" In the examples above, only one of the conditions within the OR function must be TRUE for the condition to be met. Let's take a look at one more example that involves ranges of percentages.
Based on the spreadsheet above, we would have the following formula in cell D2: =IF(OR(B2>=5%,B2<=-5%),"investigate","")
This IF function would return "investigate" if the value in cell B2 was either below -5% or above 5%. Since -6% is below -5%, it will return "investigate" as the result. We have copied this formula into cells D3 through D9 to show you the results that would be returned. For example, in cell D3, we would have the following formula: =IF(OR(B3>=5%,B3<=-5%),"investigate","") Result: "investigate" This formula would also return "investigate" but this time, it is because the value in cell B3 is greater than 5%. Note: Other useful tutorials on the IF function:
Frequently Asked Questions Question: In Microsoft Excel, I'd like to use the IF function to create the following logic: if C11>=620, and C10="F"or"S", and C4<=$1,000,000, and C4<=$500,000, and C7<=85%, and C8<=90%, and C12<=50, and C14<=2, and C15="OO", and C16="N", and C19<=48, and C21="Y", then reference cell A148 on Sheet2. Otherwise, return an empty string. Answer: The following formula would accomplish what you are trying to do: =IF(AND(C11>=620, OR(C10="F",C10="S"), C4<=1000000, C4<=500000, C7<=0.85, C8<=0.9, C12<=50, C14<=2, C15="OO", C16="N", C19<=48, C21="Y"), Sheet2!A148, "") Question: In Microsoft Excel, I'm trying to use the IF function to return 0 if cell A1 is either < 150,000 or > 250,000. Otherwise, it should return A1. Answer: You can use the OR function to perform an OR condition in the IF function as follows: =IF(OR(A1<150000,A1>250000),0,A1) In this example, the formula will return 0 if cell A1 was either less than 150,000 or greater than 250,000. Otherwise, it will return the value in cell A1.
=IF(AND(A1>100,B1<200),25,0) In this example, the formula will return 25 if cell A1 is greater than 100 and cell B1 is less than 200. Otherwise, it will return 0.
IF (cell A1) is less than 20, then times it by 1, IF it is greater than or equal to 20 but less than 50, then times it by 2 IF its is greater than or equal to 50 and less than 100, then times it by 3 And if it is great or equal to than 100, then times it by 4 Answer: You can write a nested IF statement to handle this. For example: =IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4))) Question: In Microsoft Excel, I need a formula in cell C5 that does the following: IF A1+B1 <= 4, return $20 IF A1+B1 > 4 but <= 9, return $35 IF A1+B1 > 9 but <= 14, return $50 IF A1+B1 > 15, return $75 Answer: In cell C5, you can write a nested IF statement that uses the AND function as follows: =IF((A1+B1)<=4,20,IF(AND((A1+B1)>4,(A1+B1)<=9),35,IF(AND((A1+B1)>9,(A1+B1)<=14),50,75))) Question: In Microsoft Excel, I need a formula that does the following: IF the value in cell A1 is BLANK, then return "BLANK" IF the value in cell A1 is TEXT, then return "TEXT" IF the value in cell A1 is NUMERIC, then return "NUM" Answer: You can write a nested IF statement that uses the ISBLANK function, the ISTEXT function, and the ISNUMBER function as follows: =IF(ISBLANK(A1)=TRUE,"BLANK",IF(ISTEXT(A1)=TRUE,"TEXT",IF(ISNUMBER(A1)=TRUE,"NUM",""))) Question: In Microsoft Excel, I want to write a formula for the following logic: IF R1<0.3 AND R2<0.3 AND R3<0.42 THEN "OK" OTHERWISE "NOT OK" Answer: You can write an IF statement that uses the AND function as follows: =IF(AND(R1<0.3,R2<0.3,R3<0.42),"OK","NOT OK") Question: In Microsoft Excel, I need a formula for the following: IF cell A1= PRADIP then value will be 100 IF cell A1= PRAVIN then value will be 200 IF cell A1= PARTHA then value will be 300 IF cell A1= PAVAN then value will be 400 Answer: You can write an IF statement as follows: =IF(A1="PRADIP",100,IF(A1="PRAVIN",200,IF(A1="PARTHA",300,IF(A1="PAVAN",400,"")))) Question: In Microsoft Excel, I want to calculate following using an "if" formula: if A1<100,000 then A1*.1% but minimum 25 and if A1>1,000,000 then A1*.01% but maximum 5000 Answer: You can write a nested IF statement that uses the MAX function and the MIN function as follows: =IF(A1<100000,MAX(25,A1*0.1%),IF(A1>1000000,MIN(5000,A1*0.01%),"")) Question: In Microsoft Excel, I am trying to create an IF statement that will repopulate the data from a particular cell if the data from the formula in the current cell equals 0. Below is my attempt at creating an IF statement that would populate the data; however, I was unsuccessful. =IF(IF(ISERROR(M24+((L24-S24)/AA24)),"0",M24+((L24-S24)/AA24)))=0,L24) The initial part of the formula calculates the EAC (Estimate At completion = AC+(BAC-EV)/CPI); however if the current EV (Earned Value) is zero, the EAC will equal zero. IF the outcome is zero, I would like the BAC (Budget At Completion), currently recorded in another cell (L24), to be repopulated in the current cell as the EAC.
=IF(OR(S24=0,ISERROR(M24+((L24-S24)/AA24))),L24,M24+((L24-S24)/AA24)) Question: I have been looking at your Excel IF, AND and OR sections and found this very helpful, however I cannot find the right way to write a formula to express if C2 is either 1,2,3,4,5,6,7,8,9 and F2 is F and F3 is either D,F,B,L,R,C then give a value of 1 if not then 0. I have tried many formulas but just can't get it right, can you help please? Answer: You can write an IF statement that uses the AND function and the OR function as follows: =IF(AND(C2>=1,C2<=9, F2="F",OR(F3="D",F3="F",F3="B",F3="L",F3="R",F3="C")),1,0) Question:In Excel, I have a roadspeed of a car in m/s in cell A1 and a drop down menu of different units in C1 (which unclude mph and kmh). I have used the following IF function in B1 to convert the number to the unit selected from the dropdown box: =IF(C1="mph","=A1*2.23693629",IF(C1="kmh","A1*3.6")) However say if kmh was selected B1 literally just shows A1*3.6 and does not actually calculate it. Is there away to get it to calculate it instead of just showing the text message?
Try the following: =IF(C1="mph",A1*2.23693629,IF(C1="kmh",A1*3.6))
For example, I want to put an equation for work hours and pay. IF I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour). I tried the following: =IF(A2<0,"I owe boss" abs(A2) "Hours","Boss owes me" abs(A2)*15 "dollars") Is it possible or do I have to do it in 2 separate cells? (one for text and one for the value)
=IF(A2<0,"I owe boss " & ABS(A2) & " Hours","Boss owes me " & ABS(A2)*15 & " dollars") Or the second method is to use the CONCATENATE function: =IF(A2<0,CONCATENATE("I owe boss ", ABS(A2)," Hours"), CONCATENATE("Boss owes me ", ABS(A2)*15, " dollars")) Question:I have Excel 2000. IF cell A2 is greater than or equal to 0 then add to C1. IF cell B2 is greater than or equal to 0 then subtract from C1. IF both A2 and B2 are blank then equals C1. Can you help me with the IF function on this one? Answer: You can write a nested IF statement that uses the AND function and the ISBLANK function as follows: =IF(AND(ISBLANK(A2)=FALSE,A2>=0),C1+A2, IF(AND(ISBLANK(B2)=FALSE,B2>=0),C1-B2, IF(AND(ISBLANK(A2)=TRUE, ISBLANK(B2)=TRUE),C1,""))) Question:How would I write this equation in Excel? IF D12<=0 then D12*L12, IF D12 is > 0 but <=600 then D12*F12, IF D12 is >600 then ((600*F12)+((D12-600)*E12)) Answer: You can write a nested IF statement as follows: =IF(D12<=0,D12*L12,IF(D12>600,((600*F12)+((D12-600)*E12)),D12*F12)) Question:In Excel, I have this formula currently: =IF(OR(A1>=40, B1>=40, C1>=40), "20", (A1+B1+C1)-20) If one of my salesman does sale for $40-$49, then his commission is $20; however if his/her sale is less (for example $35) then the commission is that amount minus $20 ($35-$20=$15). I have 3 columns that are needed based on the type of sale. Only one column per row will be needed. The problem is that, when left blank, the total in the formula cell is -20. I need help setting up this formula so that when the 3 columns are left blank, the cell with the formula is left blank as well.
=IF(AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)),"",IF(OR(A1>40, B1>40, C1>40), "20", (A1+B1+C1)-20)) In this formula, we are using the ISBLANK function to check if all 3 cells A1, B1, and C1 are blank, and if they are return a blank value (""). Then the rest is the formula that you originally wrote.
"A1" = allows person to input date booked out "A2" =allows person to input date booked back in "A3"= shows status of product, eg, booked out, overdue return etc. I can automate A3 with the following IF function: =IF(ISBLANK(A2),"booked out","returned") But what I cant get to work is if the product is out for 10 days or more, I would like the cell to say "send email" Can you assist? Answer: Using the TODAY function and adding an additional IF function, you can write your formula as follows: =IF(ISBLANK(A2),IF(TODAY()-A1>10,"send email","booked out"),"returned") Question:Using Microsoft Excel, I need a formula in cell U2 that does the following: IF the date in E2<=12/31/2010, return T2*0.75 IF the date in E2>12/31/2010 but <=12/31/2011, return T2*0.5 IF the date in E2>12/31/2011, return T2*0 I tried using the following formula, but it gives me “#VALUE!” =IF(E2<=DATE(2010,12,31),T2*0.75), IF(AND(E2>DATE(2010,12,31),E2<=DATE(2011,12,31)),T2*0.5,T2*0) Can someone please help? Thanks. Answer: You were very close...you just need to adjust your round brackets as follows: =IF(E2<=DATE(2010,12,31),T2*0.75, IF(AND(E2>DATE(2010,12,31),E2<=DATE(2011,12,31)),T2*0.5,T2*0)) Question:In Excel, I would like to add 60 days if grade is 'A', 45 days if grade is 'B' and 30 days if grade is 'C'. It would roughly look something like this, but I'm struggling with commas, brackets, etc. (IF C5=A)=DATE(YEAR(B5)+0,MONTH(B5)+0,DAY(B5)+60), (IF C5=B)=DATE(YEAR(B5)+0,MONTH(B5)+0,DAY(B5)+45), (IF C5=C)=DATE(YEAR(B5)+0,MONTH(B5)+0,DAY(B5)+30) Answer:You should be able to achieve your date calculations with the following formula: =IF(C5="A",B5+60,IF(C5="B",B5+45,IF(C5="C",B5+30))) Question:In Excel, I am trying to write a function and can't seem to figure it out. Could you help? IF D3 is < 31, then 1.51 IF D3 is between 31-90, then 3.40 IF D3 is between 91-120, then 4.60 IF D3 is > 121, then 5.44 Answer:You can write your formula as follows: =IF(D3>121,5.44,IF(D3>=91,4.6,IF(D3>=31,3.4,1.51))) Question:I would like ask a question regarding the IF statement. How would I write in Excel this problem? I have to check if cell A1 is empty and if not, check if the value is less than equal to 5. Then multiply the amount entered in cell A1 by .60. The answer will be displayed on Cell A2. Answer:You can write your formula in cell A2 using the IF function and ISBLANK function as follows: =IF(AND(ISBLANK(A1)=FALSE,A1<=5),A1*0.6,"") Question:In Excel, I'm trying to nest an OR command and I can't find the proper way to write it. I want the spreadsheet to do the following: If D6 equals "HOUSE" and C6 equals either "MOUSE" or "CAT", I want to return the value in cell B6. Otherwise, the formula should return the value "BLANK". I tried the following: =IF((D6="HOUSE")*(C6="MOUSE")*OR(C6="CAT"));B6;"BLANK") If I only ask for HOUSE and MOUSE or HOUSE and CAT, it works, but as soon as I ask for MOUSE OR CAT, it doesn't work.
=IF(AND(D6="HOUSE",OR(C6="MOUSE",C6="CAT")),B6,"BLANK") This will return the value in B6 if D6 equals "HOUSE" and C6 equals either "MOUSE" or "CAT". If those conditions are not met, the formula will return the text value of "BLANK".
If cell A1 equals "jaipur", "udaipur" or "jodhpur", then cell A2 should display "rajasthan" If cell A1 equals "bangalore", "mysore" or "belgum", then cell A2 should display "karnataka" Please help. Answer:You can write your formula using the OR function as follows: =IF(OR(A1="jaipur",A1="udaipur",A1="jodhpur"),"rajasthan", IF(OR(A1="bangalore",A1="mysore",A1="belgum"),"karnataka")) This will return "rajasthan" if A1 equals either "jaipur", "udaipur" or "jodhpur" and it will return "karnataka" if A1 equals either "bangalore", "mysore" or "belgum".
If a value in any cell in column F is "food" then add the value of its corresponding cell in column G (eg a corresponding cell for F3 is G3). The IF function is performed in another cell altogether. I can do it for a single pair of cells but I don't know how to do it for an entire column. Could you help? At the moment, I've got this: =IF(F3="food"; G3; 0) Answer:This formula can be created using the SUMIF formula instead of using the IF function: =SUMIF(F1:F10,"=food",G1:G10) This will evaluate the first 10 rows of data in your spreadsheet. You may need to adjust the ranges accordingly. I notice that you separate your parameters with semi-colons, so you might need to replace the commas in the formula above with semi-colons. Question:I’m looking for an Exel formula that says: If F3 is "H" and E3 is "H", return 1 If F3 is "A" and E3 is "A", return 2 If F3 is "d" and E3 is "d", return 3 Appreciate if you can help. Answer:This Excel formula can be created using the AND formula in combination with the IF function: =IF(AND(F3="H",E3="H"),1,IF(AND(F3="A",E3="A"),2,IF(AND(F3="d",E3="d"),3,""))) We've defaulted the formula to return a blank if none of the conditions above are met.
=IF(OR(ISBLANK(J2),ISBLANK(M2),ISBLANK(R2),ISBLANK (AA2),ISBLANK (AB2)),"Not Complete","") Answer:First, you are correct in using the ISBLANK function, however, you have a space between ISBLANK and (AA2), as well as ISBLANK and (AB2). This might seem insignificant, but Excel can be very picky and will return a #NAME? error. So first you need to eliminate those spaces. Next, you need to change the ELSE condition of your IF function to return "Complete". You should be able to modify your formula as follows: =IF(OR(ISBLANK(J2),ISBLANK(M2),ISBLANK(R2),ISBLANK(AA2),ISBLANK(AB2)), "Not Complete", "Complete") Now if any of the cell J2, M2, R2, AA2, or AB2 are blank, the formula will return "Not Complete". If all 5 cells have a value, the formula will return "Complete".
What I'm trying for is: If B2's value is 1 to 5, then multiply E2 by .77
I've tried a few different things thinking I was on the right track based on the IF, and AND function tutorials here, but I can't seem to get it right. Answer:To write your IF formula, you need to nest multiple IF functions together in combination with the AND function. The following formula should work for what you are trying to do: =IF(AND(B2>=1, B2<=5), E2*0.77, IF(AND(B2>=6, B2<=10), E2*0.735, IF(AND(B2>=11, B2<=19), E2*0.7, IF(AND(B2>=20, B2<=29), E2*0.675, IF(AND(B2>=30, B2<=39), E2*0.65,""))))) As one final component of your formula, you need to decide what to do when none of the conditions are met. In this example, we have returned "" when the value in B2 does not meet any of the IF conditions above. Question:Here is the Excel formula that has me between a rock and a hard place. If E45 <= 50, return 44.55 If E45 > 50 and E45 < 100, return 42 If E45 >=200, return 39.6 Again thank you very much. Answer:You should be able to write this Excel formula using a combination of the IF function and the AND function. The following formula should work: =IF(E45<=50, 44.55, IF(AND(E45>50, E45<100), 42, IF(E45>=200, 39.6, ""))) Please note that if none of the conditions are met, the Excel formula will return "" as the result. Question:I have a nesting OR function problem: My nonworking formula is: =IF(C9=1,K9/J7,IF(C9=2,K9/J7,IF(C9=3,K9/L7,IF(C9=4,0,K9/N7)))) In Cell C9, I can have an input of 1, 2, 3, 4 or 0. The problem is on how to write the "or" condition when a "4 or 0" exists in Column C. If the "4 or 0" conditions exists in Column C I want Column K divided by Column N and the answer to be placed in Column M and associated row Answer:You should be able to use the OR function within your IF function to test for C9=4 OR C9=0 as follows: =IF(C9=1,K9/J7,IF(C9=2,K9/J7,IF(C9=3,K9/L7,IF(OR(C9=4,C9=0),K9/N7)))) This formula will return K9/N7 if cell C9 is either 4 or 0.
If column B = Ross and column C = 8 then in cell AB of that row I want it to show 2013, If column B = Block and column C = 9 then in cell AB of that row I want it to show 2012. Answer:You can create your Excel formula using nested IF functions with the AND function. =IF(AND(B1="Ross",C1=8),2013,IF(AND(B1="Block",C1=9),2012,"")) This formula will return 2013 as a numeric value if B1 is "Ross" and C1 is 8, or 2012 as a numeric value if B1 is "Block" and C1 is 9. Otherwise, it will return blank, as denoted by "".
If B1=0, C1 is equal to A1/2 If B1=1, C1 is equal to A1/2 times 20% If D1=1, C1 is equal to A1/2-5 I've been trying to look for any same expressions in your site. Please help me fix this. Answer:In cell C1, you can use the following Excel formula with 3 nested IF functions: =IF(B1=0,A1/2, IF(B1=1,(A1/2)*0.2, IF(D1=1,(A1/2)-5,""))) Please note that if none of the conditions are met, the Excel formula will return "" as the result.
=IF(OR(AND(A1>=3,B1>=3),C1>=1),"yes","") Please note that if none of the conditions are met, the Excel formula will return "" as the result.
=IF(OR(ISBLANK(C9),ISBLANK(B9)),"",IF(ISBLANK(C9),D9-TODAY(), "Reactivated")) I want to make an event that if B9 and C9 is empty, the value would be empty. If only C9 is empty, then the output would be the remaining days left between the two dates, and if the two cells are not empty, the output should be the string 'Reactivated'. The problem with this code is that IF(ISBLANK(C9),D9-TODAY() is not working. Answer:First of all, you might want to replace your OR function with the AND function, so that your Excel IF formula looks like this: =IF(AND(ISBLANK(C9),ISBLANK(B9)),"",IF(ISBLANK(C9),D9-TODAY(),"Reactivated")) Next, make sure that you don't have any abnormal formatting in the cell that contains the results. To be safe, right click on the cell that contains the formula and choose Format Cells from the popup menu. When the Format Cells window appears, select the Number tab. Choose General as the format and click on the OK button.
A customer is eligible for a discount if the customer’s 2016 sales greater than or equal to 100000 OR if the customers First Order was placed in 2016. If the customer qualifies for a discount, return a value of Y If the customer does not qualify for a discount, return a value of N. Here is the formula I've entered: =IF(OR([2014 Sales]=0,[2015 Sales]=0,[2016 Sales]>=100000),"Y","N") I only have 2 cells wrong. Can you help me please? I am very lost and confused. Answer:You are very close with your IF formula, however, it looks like you need to add the AND function to your formula as follows: =IF(OR([2016 Sales]>=100000,AND([2014 Sales]=0,[2015 Sales]=0),C8>=100000),"Y","N") This formula should return Y if 2016 sales are greater than or equal to 100000, or if both 2014 sales and 2015 sales are 0. Otherwise, the formula will return N. You will also notice that we switched the order of your conditions in the formula so that it is easier to understand the formula based on your instructions above.
=IF(C6>=0<=150,150000,IF(C6>=151<=160,158400)) Here is what I need the formula to do: IF C6 IS >=0 OR <=150 THEN ASSIGN $150000 IF C6 IS >=151 OR <=160 THEN ASSIGN $158400
=IF(AND(ISBLANK(C6)=FALSE,C6>=0,C6<=150),150000,IF(AND(C6>=151,C6<=160),158400,"")) Notice that we first use the ISBLANK function to test C6 to make sure that it is not blank. This is because if C6 if blank, it will evalulate to greater than 0 and thus return 150000. To avoid this, we include ISBLANK(C6)=FALSE as one of the conditions in addition to C6>=0 and C6<=150. That way, you won't return any false results if C6 is blank.
=IF(e5="N",((AND(AH5-AG5<456, AH5-S5<822)), "Compliant", "not Compliant"),((AH5-S5<822), "Compliant", "not Compliant")) Any help would be greatly appreciated.
=IF(E5="N", IF((AND(AH5-AG5<456, AH5-S5<822)), "Compliant", "not Compliant"), IF((AH5-S5<822), "Compliant", "not Compliant")) If E5="N", the first nested IF function will be executed: IF((AND(AH5-AG5<456, AH5-S5<822)), "Compliant", "not Compliant") Otherwise,the second nested IF function will be executed: IF((AH5-S5<822), "Compliant", "not Compliant")) MS Excel: Nested IF Functions (WS) This Excel tutorial explains how to nest the Excel IF function with syntax and examples. Description It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement. Syntax The syntax for the nesting the IF function is: IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 )) This would be equivalent to the following IF THEN ELSE statement: IF condition1 THEN value_if_true1 ELSEIF condition2 THEN value_if_true2 ELSE value_if_false2 END IF Parameters or Arguments condition The value that you want to test. value_if_true The value that is returned if condition evaluates to TRUE. value_if_false The value that is return if condition evaluates to FALSE. Note: This Nested IF function syntax demonstrates how to nest two IF functions. You can nest up to 7 IF functions. Applies To The Nested IF function can be used in the following versions of Microsoft Excel:
Type of Excel Function The Nested IF function can be used in Microsoft Excel as the following type of function:
Example (as Worksheet Function) Let's look at an example to see how you would use a nested IF and explore how to use the nested IF function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following Nested IF examples would return: =IF(A1="10x12",120,IF(A1="8x8",64,IF(A1="6x6",36)))
=IF(A2="10x12",120,IF(A2="8x8",64,IF(A2="6x6",36))) Result: 64 =IF(A3="10x12",120,IF(A3="8x8",64,IF(A3="6x6",36))) Result: 36 Frequently Asked Questions Question: In Microsoft Excel, I need to write a formula that works this way: If (cell A1) is less than 20, then multiply by 1, If it is greater than or equal to 20 but less than 50, then multiply by 2 If its is greater than or equal to 50 and less than 100, then multiply by 3 And if it is great or equal to than 100, then multiply by 4 Answer: You can write a nested IF statement to handle this. For example: =IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4))) Question:In Excel, I need a formula in cell C5 that does the following: IF A1+B1 <= 4, return $20 IF A1+B1 > 4 but <= 9, return $35 IF A1+B1 > 9 but <= 14, return $50 IF A1+B1 > 15, return $75 Answer:In cell C5, you can write a nested IF statement that uses the AND function as follows: =IF((A1+B1)<=4,20,IF(AND((A1+B1)>4,(A1+B1)<=9),35,IF(AND((A1+B1)>9,(A1+B1)<=14),50,75))) Question: In Microsoft Excel, I need a formula for the following: IF cell A1= PRADIP then value will be 100 IF cell A1= PRAVIN then value will be 200 IF cell A1= PARTHA then value will be 300 IF cell A1= PAVAN then value will be 400 Answer: You can write an IF statement as follows: =IF(A1="PRADIP",100,IF(A1="PRAVIN",200,IF(A1="PARTHA",300,IF(A1="PAVAN",400,"")))) Question: In Microsoft Excel, I want to calculate following using an "if" formula: if A1<100,000 then A1*.1% but minimum 25 and if A1>1,000,000 then A1*.01% but maximum 5000 Answer: You can write a nested IF statement that uses the MAX function and the MIN function as follows: =IF(A1<100000,MAX(25,A1*0.1%),IF(A1>1000000,MIN(5000,A1*0.01%),"")) Question:I have Excel 2000. If cell A2 is greater than or equal to 0 then add to C1. If cell B2 is greater than or equal to 0 then subtract from C1. If both A2 and B2 are blank then equals C1. Can you help me with the IF function on this one? Answer: You can write a nested IF statement that uses the AND function and the ISBLANK function as follows: =IF(AND(ISBLANK(A2)=FALSE,A2>=0),C1+A2, IF(AND(ISBLANK(B2)=FALSE,B2>=0),C1-B2, IF(AND(ISBLANK(A2)=TRUE, ISBLANK(B2)=TRUE),C1,""))) Question:How would I write this equation in Excel? If D12<=0 then D12*L12, If D12 is > 0 but <=600 then D12*F12, If D12 is >600 then ((600*F12)+((D12-600)*E12)) Answer: You can write a nested IF statement as follows: =IF(D12<=0,D12*L12,IF(D12>600,((600*F12)+((D12-600)*E12)),D12*F12)) Question:I have read your piece on nested IFs in Excel, but I still cannot work out what is wrong with my formula please could you help? Here is what I have: =IF(63<=A2<80,1,IF(80<=A2<95,2,IF(A2=>95,3,0))) Answer: The simplest way to write your nested IF statement based on the logic you describe above is: =IF(A2>=95,3,IF(A2>=80,2,IF(A2>=63,1,0))) This formula will do the following: If A2 >= 95, the formula will return 3 (first IF function) If A2 < 95 and A2 >= 80, the formula will return 2 (second IF function) If A2 < 80 and A2 >= 63, the formula will return 1 (third IF function) If A2 < 63, the formula will return 0 Question:I'm very new to the Excel world, and I'm trying to figure out how to set up the proper formula for an If/then cell. What I'm trying for is: If B2's value is 1 to 5, then multiply E2 by .77
I've tried a few different things thinking I was on the right track based on the IF, and AND function tutorials here, but I can't seem to get it right. Answer:To write your IF formula, you need to nest multiple IF functions together in combination with the AND function. The following formula should work for what you are trying to do: =IF(AND(B2>=1, B2<=5), E2*0.77, IF(AND(B2>=6, B2<=10), E2*0.735, IF(AND(B2>=11, B2<=19), E2*0.7, IF(AND(B2>=20, B2<=29), E2*0.675, IF(AND(B2>=30, B2<=39), E2*0.65,""))))) As one final component of your formula, you need to decide what to do when none of the conditions are met. In this example, we have returned "" when the value in B2 does not meet any of the IF conditions above. Question:I have a nesting OR function problem: My nonworking formula is: =IF(C9=1,K9/J7,IF(C9=2,K9/J7,IF(C9=3,K9/L7,IF(C9=4,0,K9/N7)))) In Cell C9, I can have an input of 1, 2, 3, 4 or 0. The problem is on how to write the "or" condition when a "4 or 0" exists in Column C. If the "4 or 0" conditions exists in Column C I want Column K divided by Column N and the answer to be placed in Column M and associated row Answer:You should be able to use the OR function within your IF function to test for C9=4 OR C9=0 as follows: =IF(C9=1,K9/J7,IF(C9=2,K9/J7,IF(C9=3,K9/L7,IF(OR(C9=4,C9=0),K9/N7)))) This formula will return K9/N7 if cell C9 is either 4 or 0.
If column B = Ross and column C = 8 then in cell AB of that row I want it to show 2013, If column B = Block and column C = 9 then in cell AB of that row I want it to show 2012. Answer:You can create your Excel formula using nested IF functions with the AND function. =IF(AND(B1="Ross",C1=8),2013,IF(AND(B1="Block",C1=9),2012,"")) This formula will return 2013 as a numeric value if B1 is "Ross" and C1 is 8, or 2012 as a numeric value if B1 is "Block" and C1 is 9. Otherwise, it will return blank, as denoted by "".
If B1=0, C1 is equal to A1/2 If B1=1, C1 is equal to A1/2 times 20% If D1=1, C1 is equal to A1/2-5 I've been trying to look for any same expressions in your site. Please help me fix this. Answer:In cell C1, you can use the following Excel formula with 3 nested IF functions: =IF(B1=0,A1/2, IF(B1=1,(A1/2)*0.2, IF(D1=1,(A1/2)-5,""))) Please note that if none of the conditions are met, the Excel formula will return "" as the result.
=IF(OR(ISBLANK(C9),ISBLANK(B9)),"",IF(ISBLANK(C9),D9-TODAY(), "Reactivated")) I want to make an event that if B9 and C9 is empty, the value would be empty. If only C9 is empty, then the output would be the remaining days left between the two dates, and if the two cells are not empty, the output should be the string 'Reactivated'. The problem with this code is that IF(ISBLANK(C9),D9-TODAY() is not working. Answer:First of all, you might want to replace your OR function with the AND function, so that your Excel IF formula looks like this: =IF(AND(ISBLANK(C9),ISBLANK(B9)),"",IF(ISBLANK(C9),D9-TODAY(),"Reactivated")) Next, make sure that you don't have any abnormal formatting in the cell that contains the results. To be safe, right click on the cell that contains the formula and choose Format Cells from the popup menu. When the Format Cells window appears, select the Number tab. Choose General as the format and click on the OK button.
0-125000 =0% 125001-250000 =2% 250001-975000 =5% 975001-1500000 =10% >1500000 =12% I realise it's probably an 'IF(AND)' function but I appear to require too many arguments. Can you help? Answer:You can create this formula using nested IF functions. We will assume that your number 'n' resides in cell B1. You can create your formula as follows: =IF(B1>1500000,B1*0.12, IF(B1>=975001,B1*0.1, IF(B1>=250001,B1*0.05, IF(B1>=125001,B1*0.02,0)))) Since your IF conditions will cover all numbers in the range of 0 to >1500000, it is easiest to work backwards starting with the >1500000 condition. Excel will evaluate each condition and stop when a condition is TRUE. This is why we can simplify the formulas within the nested IF functions, instead of testing ranges using two comparisons such as AND(B1>=125001, B1<=250000).
0-125000 =0% 125001-250000 =2% 250001-975000 =5% 975001-1500000 =10% >1500000 =12% Say I enter 1,000,000 in B1. The first 125,000 attracts 0%, the next 125,000 to 250,000 attracts 2%, and so on. Answer:This adds a level of complexity to our formula since we have to calculate each range of the number using a different percentage. We can create this solution with the following formula: =IF(B1<=125000,0, IF(B1<=250000,(B1-125000)*0.02, IF(B1<=975000,(125000*0.02)+((B1-250000)*0.05), IF(B1<=1500000,(125000*0.02)+(725000*0.05)+((B1-975000)*0.1), (125000*0.02)+(725000*0.05)+(525000*0.1)+((B1-1500000)*0.12))))) If the value was below 125,000, the formula would return 0. If the value is between 125,001 and 250,000, it would calculate 0% on the first 125,000 and 2% on the remainder. If the value is between 250,001 and 250,001, it would calculate 0% on the first 125,000, 2% on the next 125,000 and 5% on the remainder. And so on....
This Excel tutorial explains how to use the Excel VLOOKUP function with syntax and examples. How to handle errors such as #N/A and retrieve the correct results is also discussed. Description The VLOOKUP function performs a vertical lookup by searching for a value in the left-most column of the table and returning the value in the same row in the index_number position. Syntax The syntax for the VLOOKUP function in Microsoft Excel is: VLOOKUP( value, table, index_number, [not_exact_match] ) Parameters or Arguments value The value to search for in the first column of the table. table Two or more columns of data that is sorted in ascending order. index_number The column number in table from which the matching value must be returned. The first column is 1. not_exact_match Optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, the VLOOKUP function returns an approximate match. Note:
Applies To The VLOOKUP function can be used in the following versions of Microsoft Excel:
Type of Excel Function The VLOOKUP function can be used in Microsoft Excel as the following type of function:
Example (as Worksheet Function) Let's look at some Excel VLOOKUP function examples and explore how to use the VLOOKUP function as a worksheet function in Microsoft Excel: 
Based on the spreadsheet above: =VLOOKUP(10251, A1:B6, 2, FALSE)
This VLOOKUP example would return the value of Pears. Let's take a closer look why. First Parameter The first parameter in the VLOOKUP function is the value to search for. So in this example, the VLOOKUP is searching for the value of 10251. Second Parameter The second parameter in the VLOOKUP function is the table which is set to the range of A1:B6. The VLOOKUP uses the first column in this range (ie: A1:A6) to search for the value of 10251. Third Parameter The third parameter is the index_number which is set to 2. This means that the second column in the table is where we will find the value to return. Since the table is set to A1:B6, the corresponding return value will be in B1:B6 (ie: second column as specified by the index_number of 2). Fourth Parameter Finally and most importantly is the fourth or last parameter in the VLOOKUP. In our example, it is set to FALSE. This means that you need to find an EXACT match for the value of 10251. We do not want to find a "close" match, but an EXACT match!! So if 10251 is not found in the range of A1:A6, then the VLOOKUP function should return #N/A. Since the VLOOKUP is able to find the value of 10251 in the range A1:A6, it returns the corresponding value from B1:B6 which is Pears. Importance of Fourth Parameter Let's further explore the importance of specifying TRUE vs FALSE for the last parameter in the VLOOKUP function. So say we are looking for the Order ID of 10248, but as you can see, it is not in the range of A1:A6 in the spreadsheet above. Let's write our VLOOKUP formula with FALSE as the final parameter and another VLOOKUP formula with TRUE as the final parameter and see what happens. =VLOOKUP(10248, A1:B6, 2, FALSE)
=VLOOKUP(10248, A1:B6, 2, TRUE) Result: "Apples" The first VLOOKUP formula has FALSE specified as the final parameter. This means that the VLOOKUP is looking for an exact match for 10248. Since the value 10248 does not exist in the range A1:A6, the VLOOKUP function returns #N/A. The second VLOOKUP formula has TRUE specified as the final parameter. This means that if an exact match if not found, the VLOOKUP function will look for the next largest value that is less than 10248. Now what does this mean to us? First of all, it means that the data in A1:A6 MUST BE SORTED IN ASCENDING ORDER because the VLOOKUP is going to return the next largest value for 10248 and then stop searching. So if your data is not sorted in ascending order, you are going to get some really strange results. Secondly, it means that the VLOOKUP function will find order 10247 as the approximate match. And therefore, return Apples as the result (the corresponding value from B1:B6). Table in another sheet Quite often we are asked the question, "What is an example of a VLOOKUP when the table is on another sheet?" To answer that question, let's modify our example above and assume that the table is on Sheet2 in the range A1:B6. We could rewrite our example as follows: =VLOOKUP(10248, Sheet2!A1:B6, 2, FALSE) By preceding the table range with the sheet name and an exclamation mark, we can update our VLOOKUP to reference a table on another sheet. Table in another sheet with spaces in sheet name Let's throw in one more complication, what happens if your Sheet name contains spaces, then you will need to change the formula further. So let's take a look at this case... Let's assume that the table is on a Sheet called "Test Sheet" in the range A1:B6, we would need to modify our formula as follows: =VLOOKUP(10248, 'Test Sheet'!A1:B6, 2, FALSE) By placing the sheet name within single quotes, we can accommodate a sheet name with spaces in our VLOOKUP function. Absolute Referencing Now it is important for us to mention one more mistake that is commonly made. When people use the VLOOKUP function, they commonly use relative referencing for the table range like we did in our examples above. This will return the right answer, but what happens when you copy the formula to another cell? The table range will be adjusted by Excel and change relative to where you paste the new formula. Let's explain further... So if you had the following formula in cell G1: =VLOOKUP(10248, A1:B6, 2, FALSE) And then you copied this formula from cell G1 to cell H2, it would modify the VLOOKUP formula to this: =VLOOKUP(10248, B2:C7, 2, FALSE) Since your table is found in the range A1:B6 and not B2:C7, your formula would return erroneous results in cell H2. To ensure that your range is not changed, try referencing your table range using absolute referencing as follows: =VLOOKUP(10248, $A$1:$B$6, 2, FALSE) Now if you copy this formula to another cell, your table range will remain $A$1:$B$6. How to Handle #N/A Errors Finally, let's look at how to handle instances where the VLOOKUP function does not find a match and returns the #N/A error. In most cases, you don't want to see #N/A but would rather display a more user-friendly result. For example, if you had the following formula: =VLOOKUP(10248, $A$1:$B$6, 2, FALSE) Instead of displaying #N/A error if you do not find a match, you could return the value "Not Found". To do this, you could modify your VLOOKUP formula as follows: =IF(ISNA(VLOOKUP(10248, $A$1:$B$6, 2, FALSE)), "Not Found", VLOOKUP(10248, $A$1:$B$6, 2, FALSE)) This new formula will use the ISNA function to test if the VLOOKUP returns a #N/A error. If the VLOOKUP returns #N/A, then the formula will output "Not Found". Otherwise, it will perform the VLOOKUP as before. This is a great way to spruce up your spreadsheet so that you don't see traditional Excel errors.
Based on the spreadsheet above: =IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)), 0, VLOOKUP(E2,$A$2:$C$5,3,FALSE))
First, you need to enter a FALSE in the last parameter of the VLOOKUP function. This will ensure that the VLOOKUP will test for an exact match. If the VLOOKUP function does not find an exact match, it will return the #N/A error. By using the IF and ISNA functions, you can return the Unit Price value if an exact match is found. Otherwise, a 0 value is returned. This allows you to perform mathematical operations on your VLOOKUP results.
Based on the spreadsheet above: =IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)), "No", "Yes")
=IF(ISNA(VLOOKUP(A5,$D$2:$D$185,1,FALSE)), "No", "Yes") Result: "Yes" First, you need to enter a FALSE in the last parameter of the VLOOKUP function. This will ensure that the VLOOKUP will test for an exact match. If the VLOOKUP function does not find an exact match, it will return the #N/A error. By using the IF and ISNA functions, you can return a "Yes" value if an exact match is found. Otherwise, a "No" value is returned.
If you wanted to return the quantity value for the second occurrence of apple, you would use the following array formula: =INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),2) When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows: {=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),2)} If you wanted to return the quantity value for the third occurrence of apple, you would use the following array formula: =INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),2) When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows: {=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),3),2)} If you wanted to return the bin # for the second occurrence of apple, you would use the following array formula: =INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),3) When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows: {=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),3)}
=VLOOKUP(D14, $Q$1:$S$10, 3, FALSE) In this VLOOKUP example, the rock type that you want to look up is in cell D14, the lookup data is found in the range of $Q$1:$S$10. We've absolutely referenced the lookup range so that you can copy the formula to other cells without the range changing. The third parameter is set to 3 because we want the value returned from column S which is the third column in the range of $Q$1:$S$10. And the final parameter in the VLOOKUP is FALSE because we are only looking for an exact match. With this formula if a match is not found, the VLOOKUP will return #N/A. If you would like to return a different value when there is no match, say "Not Found", then you could modify your VLOOKUP as follows: =IF(ISNA(VLOOKUP(D14, $Q$1:$S$10, 3, FALSE)), "Not Found", VLOOKUP(D14, $Q$1:$S$10, 3, FALSE)) This formula would return "Not Found" if there was not a match. Otherwise, it would return the appropriate value from S1 to S10. Question:I want to do a VLOOKUP if a statement is true. Example:
If cell A2 = cell F9, I want to do a virtual lookup depending on what is in cell E34, the function would return corresponding data from cells defined as "TEAM". This is what I came up with: =If(A2=F9),VLOOKUP(+E34,TEAM,1+1)
=IF(A2=F9,VLOOKUP(E34,TEAM,2,FALSE)) In this example, VLOOKUP will be performed only if A2 is equal to F9. The first parameter of E34 specifies the value to lookup. The second parameter uses the named range called "TEAM" which is where the lookup data is found. The third parameter of 2 will return corresponding data from the second column in the "TEAM" named range. The final parameter of FALSE means that we are only looking for an exact match. Question:I have a question about how to nest a MATCH function within the INDEX function. The question is: I want to create a formula using the MATCH function nested within the INDEX function to retrieve the Class that was selected (by the x) in E4:F10. The MATCH function should find the row where the x is located and should be used within the INDEX function to retrieve the associated Class value from the same row within F4:F10. Answer:Even though you can write this formula using a combination of the MATCH and INDEX functions, it is much easier and faster to perform this search using the VLOOKUP function, as follows: =VLOOKUP("x",$E$4:$F$10,2,FALSE) In this example, we are searching for the value "x" within the range E4:E10. When the value of "x" is found, it will return the corresponding value from F4:F10.
Yüklə 456,44 Kb. Dostları ilə paylaş:
|
