by The tutorial shows how to create multiple IF statements in Excel with AND as well as OR logic. Also, you will learn how to use IF together with other Excel functions.
In the first part of our Excel IF tutorial, we looked at how to construct a simple IF statement with one condition for text, numbers, dates, blanks and non-blanks. For powerful data analysis, however, you may often need to evaluate multiple conditions at a time. The below formula examples will show you the most effective ways to do this.
How to use IF function with multiple conditions
In essence, there are two types of the IF formula with multiple criteria based on the AND / OR logic. Consequently, in the logical test of your IF formula, you should use one of these functions:
- AND function - returns TRUE if all the conditions are met; FALSE otherwise.
- OR function - returns TRUE if any single condition is met; FALSE otherwise.
To better illustrate the point, let's investigate some real-life formulas examples.
Excel IF statement with multiple conditions (AND logic)
The generic formula of Excel IF with two or more conditions is this:
Translated into a human language, the formula says: If condition 1 is true AND condition 2 is true, return value_if_true; else return value_if_false.
Suppose you have a table listing the scores of two tests in columns B and C. To pass the final exam, a student must have both scores greater than 50.
For the logical test, you use the following AND statement: AND(B2>50, C2>50)
If both conditions are true, the formula will return "Pass"; if any condition is false - "Fail".
=IF(AND(B2>50, B2>50), "Pass", "Fail")
Easy, isn't it? The screenshot below proves that our Excel IF /AND formula works right:
In a similar manner, you can use the Excel IF function with multiple text conditions.
For instance, to output "Good" if both B2 and C2 are greater than 50, "Bad" otherwise, the formula is:
=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")
Important note! The AND function checks all the conditions, even if the already tested one(s) evaluated to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.
In practice, a seemingly correct IF statement may result in an error because of this specificity. For example, the below formula would return #DIV/0! ("divide by zero" error) if cell A2 is equal to 0:
=IF(AND(A2<>0, (1/A2)>0.5),"Good", "Bad")
The avoid this, you should use a nested IF function:
=IF(A2<>0, IF((1/A2)>0.5, "Good", "Bad"), "Bad")
For more information, please see IF AND formula in Excel.
Excel IF function with multiple conditions (OR logic)
To do one thing if any condition is met, otherwise do something else, use this combination of the IF and OR functions:
The difference from the IF / AND formula discussed above is that Excel returns TRUE if any of the specified conditions is true.
So, if in the previous formula, we use OR instead of AND:
=IF(OR(B2>50, B2>50), "Pass", "Fail")
Then anyone who has more than 50 points in either exam will get "Pass" in column D. With such conditions, our students have a better chance to pass the final exam (Yvette being particularly unlucky failing by just 1 point :)
Tip. In case you are creating a multiple IF statement with text and testing a value in one cell with the OR logic (i.e. a cell can be "this" or "that"), then you can build a more compact formula using an array constant.
For example, to mark a sale as "closed" if cell B2 is either "delivered" or "paid", the formula is:
=IF(OR(B2={"delivered", "paid"}), "Closed", "")
More formula examples can be found in Excel IF OR function.
IF with multiple AND & OR statements
If your task requires evaluating several sets of multiple conditions, you will have to utilize both AND & OR functions at a time.
In our sample table, suppose you have the following criteria for checking the exam results:
- Condition 1: exam1>50 and exam2>50
- Condition 2: exam1>40 and exam2>60
If either of the conditions is met, the final exam is deemed passed.
At first sight, the formula seems a little tricky, but in fact it is not! You just express each of the above conditions as an AND statement and nest them in the OR function (since it's not necessary to meet both conditions, either will suffice):
OR(AND(B2>50, C2>50), AND(B2>40, C2>60)
Then, use the OR function for the logical test of IF and supply the desired value_if_true and value_if_false values. As the result, you get the following IF formula with multiple AND / OR conditions:
=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")
The screenshot below indicates that we've done the formula right:
Naturally, you are not limited to using only two AND/OR functions in your IF formulas. You can use as many of them as your business logic requires, provided that:
- In Excel 2007 and higher, you have no more than 255 arguments, and the total length of the IF formula does not exceed 8,192 characters.
- In Excel 2003 and lower, there are no more than 30 arguments, and the total length of your IF formula does not exceed 1,024 characters.
Nested IF statement to check multiple logical tests
If you want to evaluate multiple logical tests within a single formula, then you can nest several functions one into another. Such functions are called nested IF functions. They prove particularly useful when you wish to return different values depending on the logical tests' results.
Here's a typical example: suppose you want to qualify the students' achievements as "Good", "Satisfactory" and "Poor" based on the following scores:
- Good: 60 or more (>=60)
- Satisfactory: between 40 and 60 (>40 and <60)
- Poor: 40 or less (<=40)
Before writing a formula, consider the order of functions you are going to nest. Excel will evaluate the logical tests in the order they appear in the formula. Once a condition evaluates to TRUE, the subsequent conditions are not tested, meaning the formula stops after the first TRUE result.
In our case, the functions are arranged from largest to smallest:
=IF(B2>=60, "Good", IF(B2>40, "Satisfactory", "Poor"))
Naturally, you can nest more functions if needed (up to 64 in modern versions).
For more information, please see How to use multiple nested IF statements in Excel.
Excel IF array formula with multiple conditions
Another way to get an Excel IF to test multiple conditions is by using an array formula.
To evaluate conditions with the AND logic, use the asterisk:
To test conditions with the OR logic, use the plus sign:
To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.
For example, to get "Pass" if both B2 and C2 are greater than 50, the formula is:
=IF((B2>50) * (C2>50), "Pass", "Fail")
In my Excel 365, a normal formula works just fine (as you can see in the screenshots above). In Excel 2019 and lower, remember to make it an array formula by using the Ctrl + Shift + Enter shortcut.
To evaluate multiple conditions with the OR logic, the formula is:
=IF((B2>50) + (C2>50), "Pass", "Fail")
Using IF together with other functions
This section explains how to use IF in combination with other Excel functions and what benefits this gives to you.
Example 1. If #N/A error in VLOOKUP
When VLOOKUP or other lookup function cannot find something, it returns a #N/A error. To make your tables look nicer, you can return zero, blank, or specific text if #N/A. For this, use this generic formula:
For example:
If #N/A return 0:
If the lookup value in E1 is not found, the formula returns zero.
=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))
If #N/A return blank:
If the lookup value is not found, the formula returns nothing (an empty string).
=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))
If #N/A return certain text:
If the lookup value is not found, the formula returns specific text.
=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "Not found", VLOOKUP(E1, A2:B10, 2, FALSE))
For more formula examples, please see VLOOKUP with IF statement in Excel.
Example 2. IF with SUM, AVERAGE, MIN and MAX functions
To sum cell values based on certain criteria, Excel provides the SUMIF and SUMIFS functions.
In some situations, your business logic may require including the SUM function in the logical test of IF. For example, to return different text labels depending on the sum of the values in B2 and C2, the formula is:
=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Satisfactory", "Poor"))
If the sum is greater than 130, the result is "good"; if greater than 110 – "satisfactory', if 110 or lower – "poor".
In a similar fashion, you can embed the AVERAGE function in the logical test of IF and return different labels based on the average score:
=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Poor"))
Assuming the total score is in column D, you can identify the highest and lowest values with the help of the MAX and MIN functions:
=IF(D2=MAX($D$2:$D$10), "Best result", "")
=IF(D2=MAX($D$2:$D$10), "Best result", "")
To have both labels in one column, nest the above functions one into another:
=IF(D2=MAX($D$2:$D$10), "Best result", IF(D2=MIN($D$2:$D$10), "Worst result", ""))
Likewise, you can use IF together with your custom functions. For example, you can combine it with GetCellColor or GetCellFontColor to return different results based on a cell color.
In addition, Excel provides a number of functions to calculate data based on conditions. For detailed formula examples, please check out the following tutorials:
Example 3. IF with ISNUMBER, ISTEXT and ISBLANK
To identify text, numbers and blank cells, Microsoft Excel provides special functions such as ISTEXT, ISNUMBER and ISBLANK. By placing them in the logical tests of three nested IF statements, you can identify all different data types in one go:
=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", "")))
Example 4. IF and CONCATENATE
To output the result of IF and some text into one cell, use the CONCATENATE or CONCAT (in Excel 2016 - 365) and IF functions together. For example:
=CONCATENATE("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))
=CONCAT("You performed ", IF(B1>100,"fantastic!", IF(B1>50, "well", "poor")))
Looking at the screenshot below, you'll hardly need any explanation of what the formula does:
IF ISERROR / ISNA formula in Excel
The modern versions of Excel have special functions to trap errors and replace them with another calculation or predefined value - IFERROR (in Excel 2007 and later) and IFNA (in Excel 2013 and later). In earlier Excel versions, you can use the IF ISERROR and IF ISNA combinations instead.
The difference is that IFERROR and ISERROR handle all possible Excel errors, including #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While IFNA and ISNA specialize solely in #N/A errors.
For example, to replace the "divide by zero" error (#DIV/0!) with your custom text, you can use the following formula:
=IF(ISERROR(A2/B2), "N/A", A2/B2)
And that's all I have to say about using the IF function in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel IF multiple criteria - examples (.xlsx file)
4538 comments
I want the reason for result of IF formula. That if result came was PASS then why? Is it due to column 2 or 3.
Hello SHRIKRISHNA!
To control how the formula is executed, you can use the Evaluate Formula tool. It is located in the menu on the Formula tab.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thanks sir, evaluate formula shows it but I want that this evaluation in column next to result of formula.
Kindly help me to get percentage in multiple amount different percentage ratio
For example:
60000 to 100000 = 5.5%
100001 to 150000 = 8%
150001 to 200000 = 12%
200001 and above 15%
How to calculate if i have many column in different amount and need to get percentage as per above how to use formula, kindly help urgently. Appreciate any can help me in this regards urgently.
Thank you
Pravin Rupapara
Hello Pravin!
You may use the FREQUENCY function to calculate the number of values in the particular range:
=FREQUENCY(B2:B100,F2:F5)
Where B2:B100 - your data range and F2:F5 - the cells that contain your lower bounds (i.e. 60000, 100000, 150000, 200000)
Select the range of empty cells (G2:G6, for instance) that has one cell more than F2:F5. Paste =FREQUENCY(B2:B100,F2:F5) in the formula bar and apply it as an array function by pressing Ctrl+Shift+Enter. Then divide every resulting numbers by the total of values (the formula for the last one would be =COUNTA(B2:B100)
Enter these formulas into H2:H6 and you'll get the percentage you need.
Ok so I'm trying to compile a file that shows if a particular person has been called during the week.
Each Day has a sheet that populates a persons detail from a unique identifier (Tech Id) and can be marked as YES or NO as to whether they have been called that day.
Tech ID, Name, Called?
I then have a weekly review sheet that i need to populate. same column lay out
So if the Tech ID in A2=FS999 and the corresponding tech ID = YES on any of the daily sheets then it should fill in on the weekly tracker. I Just cant for the life of me get my head around the complexity of so many IF's and OR's .
Can Anyone Advise?
Hello Mike!
If I understand your task correctly, the following formula should work for you:
1. To calculate data for several conditions on several sheets, you can use the formula
=SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"),"FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes"))
2. Use this formula in your condition
=IF(SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"), "FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes")) > 0,"Yes","No")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi All,
I need an excel formula to TEXTJOIN of multipel cells with a matching condition of two column vaues. For example.
Column 1 has Managername Manager1, Manager2, Manager3, Manager4, Manager5
Column 2 has Employees EMP1, EMP2, EMP3, EMP4,EMP5, EMP6 etc
column 3 has OnBench, InProject,InProject,InProject,OnBench
I need to list this into other sheet of the same workbook, with the below condition.
List of all employees, with join text separated by comma into single cell against that Manager name who are OnBench.
Can anybody help with this?
Thanks a lot in advance.
Hello Surya!
I recommend using function Vlookup for multiple values/ Read more here.
I hope this will help, otherwise please do not hesitate to contact me anytime.
I need a formula that looks at a date range on 1 sheet but returns the value on a different sheet, looking at values on the original sheet but only IF a different column on the original sheet shows certain TEXT.
I'm doing the formula on a sheet named 'April!'
Data is on 'Ian!' sheet
So column B6:B500 on Ian! will have the date
Column J6:J500 has the income but it is dependant on the value in column C whether it shows as 'New' or 'Renewal'
Please help, thank you
I currently have this formula that works looking at the total but I now need it to refer to a date range on the same sheet for Ian!
=SUMIF(Ian!$B$6:$B$500,April!$A$1(Ian!$C$6:$C$500,April!$B$1,Ian!$J$6:$J$500))
Hello Rachel!
I’m sorry but your task is not entirely clear to me. I need more details to help you. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Write down what specific criteria must be met so that the value of sheet 2 is written to sheet1. Thank you.
If column A is the equally weighted value of a score and column B is the score (0-5 or N/A), and a score of N/A is entered on a row, I want the weight to be removed from the row and equally redistributed across all scored rows, thereby increasing the weights in column A.
Is this possible?
Said another way, if all rows had a numeric score, then all weights would be 3%, for example. However, if there were several rows that got an N/A, for each N/A, the 3% value of the weight would be equally redistributed across column A to show what the final, equally weighted values would be.
Thanks for your help! I hope this question makes sense!
Hello Lisa!
If N/A is a text, not an error value #N/A, then use the following formula to calculate the weight:
= IFERROR (B1/SUM($B$1:$B$50), 0)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Please help me correct these formulas
=(F32); IF(F16:F21="0",) - H47 = F32 but if cells F16:F21 = 0 then H47 Should = 0%
Also I require a formular If C11="good",15; IF C11="Satisfactory",9; IF C11="Poor",0; IF C11="N/A",N/A)
Thank You
Hello Mona!
I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. What you wrote is not a formula.
This formula may be suitable for you, but your explanations are very inaccurate.
=IF(C11="good",15, IF(C11="Satisfactory",9, IF(C11="Poor",0, IF(C11="N/A",#N/A, "" ) ) ) )
Hi
Yes, I figured i messed that up really bad and managed to correct it to this
=IF(C11="Good";15;IF(C11="Satisfactory";9;IF(C11="Poor";0;"N/A"))) which works well,
the only problem is i dont know how to go about having a formula that ignores a N/A and still work out at 100%. I tried yours now and this just leaves the score as a positive.
Hello Mona!
Excel uses the IFERROR function to handle errors. Read more here https://www.ablebits.com/office-addins-blog/excel-iferror-function-formula-examples/
I am looking for help. My worksheet tracks work as well as issues. I need a formula for conditional formatting that will allow for a visual quick identification. This is what I think the formula should look like but I get an error. =IF(G22="Y","CORROSION",""(AND(H22="X","CLEANED"))); or =IF(G22="Y","CORROSION","",IF(G22="Y"(AND(H22="X"),"CLEANED"))
The first part of the Formula works it is trying to add the second half that causes the error. Anyone have a suggestion?
Hello Jesse!
Your formula contains errors. 1. You cannot use the = sign inside a formula. 2. The conditions AND and OR are incorrectly described. You have not explained how the formula should work, so I can’t fix it. To do it yourself, read the instructions in this article above.
I hope this will help, otherwise please do not hesitate to contact me anytime.
On my spread sheet I have criteria that represent if an item is corroded or not, block 1 "item", block 2 is "inspected", block 3 "corroded", block 4 'cleaned/primed", and block 5 "notes". If it is corroded I put a "Y" in the corroded block. In my note block I have a formula, =IF(G4="Y","CORROSION",""), if this block has a "Y" it will show the word "CORROSION" this works perfectly. What I need to do is add to the formula that considers the corroded block with a "Y" and the clean primer block with a "X" to return with the word "REPAIRED". Is there a way to do this? I need this as a quick reference so at a visual glance the item can be easily identified as repaired.
Hello Jesse!
If I understand your task correctly, the following formula should work for you:
=IF(G4="Y", IF(H4="X","Repaired","CORROSION"),"")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thank you sir works perfectly.
I am trying to figure out how to return one value in a cell by evaluating 6 other cells. For instance, if cell C2 has a valid value, return C2. If "NA" I need it to look at D2 and do the same evaluation and return the valid value if not "NA". So, column c2 = CAR1, columns d2 through h2 have NA. I want column B2 to say CAR1. If column d2 = CAR2 and column c2, e2 through h2 have NA. I want column b2 to say CAR2. I am guessing I need a string but cannot figure it out. Thanks!
Hello Frank!
If I understand your task correctly, the following formula should work for you:
=INDEX(C1:H1,1,MATCH("NA",C1:H1,0)-1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
=IF(O23="Inactive",0,IF(OR(L23=$L$3,L23=$L$4)=TRUE,$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))
The last if functions works when on its own, but when added to the previous functions it does not work. I do not see where the issue is: only adding one extra agruement to the formula to say if additional something is "Yes" then please add 150 to the amount.
Hello!
I'm sorry, I don't quite understand your case based on the details you provided. As for your formula, it should look like the one below:
=IF(O23=”Inactive”,0,IF(OR(L23=$L$3,L23=$L$4),$U$1*0%,IF(S23>0,IF(S23<AI23,$U$1*50%,0),IF(AK23="Yes",U1*50%+U3,U1*50%))))
If the formula doesn't work the way you'd like it to, please give me some more details about its conditions, their priority and specify what result you need to get. Maybe although the formula you created works fine, it doesn't match these criteria. If you give me more information about your task and the expected result, I'll be able to find the right solution for you. Thank you.
Hi I need a formula to state this
In there will be Yes or No. If its Yes then use cell A1 if its No then use cell A2
Hello,
Please try the following formula:
=IF([condition],A1,A2)
You can learn more about Excel IF statement in this article on our blog.
Hope this is what you need.
Sir i want if ((1 to 2 = 2 , 2 to 4 = 3, 5 to 9 = 5 ))how to make that on if logic in single cell if any argument Parameter in between 1 to 2 must showing 2 , argument Parameter in between 3 to 5 = 3
Hello!
If I understand your task correctly, please try the following formula:
=IF(AND(A1 >= 1,A1<= 2),2,IF(AND(A1 > 2,A1 <= 4),3,IF(AND(A1 >= 5,A1 <= 9),5,"")))
I hope it’ll be helpful.
Hello, I need your help. I have a formula that is trying to figure out over and under numbers to meet goals.
=IF($B$4<$C$1,"")
actual B4=20 and C1=15(goal)
If the number on B4 is less than 15, I have it giving us a blank, which works fine. But I cant figure out how to add another IF formula that will tell it to give me the number we are over our goal by. In this case, total is 20, goal is 15, answer should be 5. So how do I add an if to my formula?
=IF($B$415, XXX)
Hello Maria!
If I understand your task correctly, please try the following formula:
=IF($B$4 < 15,"",$B$4-$C$1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
WOW! Thank you. Here I thought I needed a second IF statement. Thanks so much for your help. Maria.
I am very new to excel but I have a very detailed comparison I do manually on an excel sheet currently and I think you maybe able to help me.
I need to compare three columns and depending the message in those columns I need an out come.
Data:
A1 will contain update needed or blank
B2 will contain coordinator needed or blank
C2 will contain licensed or blank
Anytime column C2 equals Not Licensed no matter what any of the other columns say then I need column D2 to say Ineligible
If C2 is blank and b2 is coordinator needed and A1 is update needed the d2 needs to say coordinator and update needed
If c2 is blank and b2 is blank but A1 is update needed then I need D2 to say Eligible update
If all are blank then I need D2 to say No Action Needed
Thanks for any help !
Hello Diane!
If I understand your task correctly, please try the following formula:
=IF(C2="Not Licensed","Ineligible", IF(AND(C2="",B2="coordinator needed",A1="update needed"),"coordinator and update needed", IF(AND(C2="",B2="",A1="update needed"),"Eligible update", IF(AND(C2="",B2="",A1=""),"No Action Needed", "" ) ) ))
Hope this is what you need.
In same sheet read the 3 column V to x and match the condition of 25> cases in AC column and return the gift he qualified in AE column.
hi,
my requirement is, I have 3 columns. If column 1 value meets my requirement and the columen 2 text is "x", extract column 2 text. Else no. could you please help how I can write formula?
Hello!
You can learn more about about functions IF and AND in Excel in this article on our blog.
Hope you’ll find this information helpful.
Hi
I am using a history database and would like to make it a little more efficient. Its purpose is to code surnames using four characters but when there are fewer than four characters, to fill in any gaps with a full stop; for example KIM would become KIM. If I have a column of surnames, is there a formula that would allow me to identify those with fewer than four code letters and which would add the full stop for me. The current process requires me to check each entry and for a large database it takes an age.
Hello Pail!
If I understand your task correctly, please try the following formula:
=IF(LEN(TRIM(A1)) < 4,"less than 4",A1)
For me to be able to help you better, please describe your task in more detail. It’ll help me understand it better and find a solution for you. Thank you.
if AH1=Conus and D1=12, then 17
if AH1=Conus and D1<12, then 15
if AH1=Oconus, then 28
Hello Jessica!
If I understand your task correctly, please try the following formula:
=IF(AH1="Conus",IF(D1=12,17,IF(D1 < 12,15,"")),IF(AH1="Oconus",28,""))
Thank you so much!
ok, i need to throw another variable in - can this be done?
if AH1=Conus and D1=12, AND G1=9820580 or 159384 ....then 17
if AH1=Conus and D1=12, AND G1=6620363 ....then 15
if AH1=Conus and D1<12, then 15
if AH1=Oconus, then 28
Hello Jessica!
Please try the following formula:
=IF(AH1="Conus",IF(AND(D1=12, SUM(IF(G1={9820580,159384},1,0))=1),17, IF(OR(D1 < 12,AND(D1=12,G1=6620363)),15,"")), IF(AH1="Oconus",28,""))
I hope it’ll be helpful.
WOW. thank you so much
Thank you sir for your attention, if B1"", C1"", & D1"", it should give me the value of both K1, L1 & M1 & so on.
"Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of M1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
Thanks.
Hello Enity!
Unfortunately, you did not give me detailed explanations. What formula did you use? Are you checking the condition in 15 cells at the same time? In which cell do you want to write the result? What should this result look like? How is the sum of the values of K1, L1 & M1 and so on? What are 15 arguments if there are only 10 columns between columns B and K? I can assume the following formula:
=IF(SUMPRODUCT(--(B1=""),--(C1=""), --(D1=""),--(E1=""),--(F1=""),--(G1=""), --(H1=""),--(I1=""),--(J1=""))=1, K1,B1)
But this is just a guess. And I'm not a telepath.
I have two columns of data with the number set at 2 decimal places. column A for example in Cell A2 could read '10.2' and Cell B2 could read '11.3'. I want to be able to have cell C3 to state 'out of tolerance' if the range is outside of 10%. Is that possible?
Hello Shane!
You did not explain what it means "the range is outside of 10%". Maybe the following formula should work for you:
=IF((B2-A2)/A2>0.1,"out of tolerance","ok")
Expression (B2-A2)/A2>0.1 can be replaced with another.
I hope it’ll be helpful.