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
Hi Alexander,
I hope all is well
I am trying to come up with a formula that reads a calculation whereby if the month is may and the year is more than and equal to 2027 and less than and equal to 2046 (The range of years is between 2027 and 2046) and month is may
If the 3 conditions above are correct then I want it to multiply two numbers (lets say 4*5) in every May of the period between 2027 and 2046.
I succeeded in the month part but not the range of years all together
Highly Appreciated
Hello!
With the MONTH function, you can determine the month, and with the YEAR function, you can determine the year from the date. Define the month and year and use these conditions in the IF function.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Hi Please solve this. We have two cell in row A and B.
A B
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
where A and B matched with above number then result should be below.
A B Result should be
1 1 A
1 2 B
1 3 C
2 1 D
2 2 E
2 3 F
3 1 H
3 2 I
3 3 J
Hello!
Since you have a lot of conditions, I recommend using the IFS function.
=IFS(A1&B1="11","A",A1&B1="12","B", .......)
HI there,
I am trying to do the following -
A1 = Target date
B1 = Completion date
C1 = State (Open / OVERDUE / CLOSED)
If the target date from column A is met column C will state OPEN - =IF(A1=TODAY,"OPEN"
and if it has gone past the target date it states OVERDUE - =IF(A1<TODAY,"OVERDUE"
however I also need the formula to consider column B which is the date of completion
IF 'TODAY' has hit/gone past completion date column C will state CLOSED and ignore the previous part of the formula.
If no dates have been entered column C will remain black
Please help ?
Hi!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
Your explanations are not very clear, but I guess the formula could be something like this:
=IF(A1=TODAY(),"Open", IF(AND(A1 < TODAY(),B1 > TODAY()),"Overdue", IF(AND(A1 < TODAY(),B1 < = TODAY()),"Closed", "")))
If this is not what you wanted, please describe the problem in more detail.
I am looking for something similar, but different:
A1: ETA
B1: Quantity due by ETA
C1: Late if not received by today
=IF(AND([@ETA]<[@[Late if not received by this date]],([@[Due In]]=0)),"Closed","Late")
I am using this formula, it is *almost* working. For those dates that are before today, it is fine, but for future dats it is labeling Late. I am unsure how to add to the formula to read Future.
Could you assist?
Thank you.
Hello!
It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work.
The simplest formula looks like this:
=IF(A1>=TODAY(),"Close","Late")
=IF(C3="n", B3-A3,IF(AND(C3="y",SQRT((B3-A3)/2)=0,(B3-A3)/2,(B3-A3)/2+0.5)))
I'm trying to make it so that if a cell equals "n", it'll just subtract one cell from the other and if the cell equal "y", it will then check if one cell subtracted from the other equals zero. If it does equal zero, it'll divide the cells by two, and if not, it'll divide the cells by two and then add .5. I can't figure out what is wrong with my formula, but every time I hit enter, it tells me that there is. Can anyone tell me what I did wrong?
if the cell equals "y", it will check if the square root of one cell subtracted from the other equals 0*
Hello!
I don't really understand your terms. But this formula will work.
=IF(C3="n",B3-A3,IF(AND(C3="y",(B3-A3)=0),(B3-A3)/2,(B3-A3)/2+0.5))
Hi,
I need a formula for below:
Current Period Start Date 1-Jan-21
Current Period End Date 31-Dec-21
Payment Periods (sample) below:
Start Date End Date
1. 10-Mar-19 10-Mar-21
2. 9-Apr-21 08-Nov-21
3. 15-May-20 15-Feb-23
I need IF formula for A,B and C as respective columns after End date column:
A=Period (in days) for Previous Years, i.e Before 31-Dec-2020
B=Period (in days) for Current Year, i.e 01-Jan-2021 to 31-Dec-2021
C=Prepaid period (in days) from, i.e From 31-Dec 2021 onwards
Kindly request your help.
Hello!
To calculate the difference between dates in days, use the DATEDIF function as described in this manual.
Hi Sir, Thanks. DATEDIF is helpful to calculate difference between two dates. However I need to segregate the results to the days for "previous year", "current year" and "future years"?
Below is my problem:
Current Period Start Date 1-Jan-21
Current Period End Date 31-Dec-21
Payment Periods (sample) below:
Start Date End Date (Samples)
1. 10-Mar-19 10-Mar-21
2. 9-Apr-21 08-Nov-21
3. 15-May-20 15-Feb-23
I need IF formula for A,B and C as respective columns after End date column:
A=Period (in days) for Previous Years, i.e Before 31-Dec-2020
B=Period (in days) for Current Year, i.e 01-Jan-2021 to 31-Dec-2021
C=Prepaid period (in days) from, i.e From 31-Dec 2021 onwards
Hello!
For example #1, use these two formulas:
=IF(DATE(YEAR(B1),1,1)>A1,DATEDIF(A1,DATE(YEAR(B1),1,1),"d"),"")
=IF(DATE(YEAR(B1),1,1)>A1,DATEDIF(DATE(YEAR(B1),1,1),B1,"d"),DATEDIF(A1,B1,"d"))
A1 is 10-Mar-19
B1 is 10-Mar-21
Thank you Sir.
I'm trying to get an if function to cooperate with me.
if(a17=12, then c17=c16). so basically if there is the number 12 is cell a17 then cell c17 will equal c16
Hello!
The formula in cell C17 below will do the trick for you:
=IF(A17=12,C16,"")
You can learn more about IF function in this article: How to use IF function in Excel
tomorrow is my assignment test so do you send me dum excel file to practice all advance formula i.e, multiplr if,sumifs,index,power query,time and dates subtraction,pivot table...and more,,,
which will help me to build my confidence....please help me ???
I'm attempting to do a nested If/or to populate cells based on text contents of another cell, regardless of case of the words in the source cell.
Basic summary: If A1 contains the word "Normal" or "normal" in the text, A2 = "Normal"
If "abnormal" or "Abnormal", A2 = "Abnormal"
If "no call" or "No Call", A2 = "No Call"
What I have currently is below, but I can't find the error.
=IF(OR(((ISNUMBER(SEARCH("abnormal",a3)),"Abnormal"),ISNUMBER(SEARCH("no call",a3)),"No Call"),ISNUMBER(SEARCH("normal",a3)),"Normal"))
Hello!
Please re-check the article above since it covers your case.
=IF(ISNUMBER(SEARCH("abnormal",A3)),"Abnormal", IF(ISNUMBER(SEARCH("no call",A3)),"No Call", IF(ISNUMBER(SEARCH("normal",A3)),"Normal","")))
Thank you for your help! I hadn't realized I was missing that set of quotation marks. I overcomplicated it for what I needed, it seems.
I am trying to create an IF/AND Statement with 2 criteria. The criteria are below. Each point value needs to have both the start and spread value achieved to hit that point value.
Point Value Starts/Spread
24 8 / $3,300
18 7 / $2,900
12 6 / $2,500
6 5 / $2,100
Hi!
Unfortunately, your criteria are not clear. Explain them in detail.
I have a formula I am trying to come up with and have multiple ranges of numbers involved.
Is converting the numbers below into a formula so when I enter my blood pressure readings into my data sheet so the status column changes to the appropriate status when the following conditions are met for each status.
NORMAL less than 120 and less than 80
ELEVATED between 120 129 and less than 80
HYPERTENSION STAGE 1 between 130 139 or between 80 89
HYPERTENSION STAGE 2 140 or Higher 140 or 90 or Higher 90
HYPERTENSIVE CRYSIS Higher than 180 180 and/or Higher than 120 120
For example when I enter 118/75 the status changes to Normal
138/65 the status changes to HYPERTENSION STAGE 1
Hello!
Use substring functions in Excel to extract numbers from text:
=IF(AND(--LEFT(A1,SEARCH("/",A1)-1) < 120,--MID(A1,SEARCH("/",A1)+1,10) < 80),"Normal",
IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 120,--LEFT(A1,SEARCH("/",A1)-1) < = 129,--MID(A1,SEARCH("/",A1)+1,10) < 80),"ELEVATED",
IF(AND(--LEFT(A1,SEARCH("/",A1)-1) >=130,--LEFT(A1,SEARCH("/",A1)-1) < = 139, --MID(A1,SEARCH("/",A1)+1,10) > = 80,--MID(A1,SEARCH("/",A1)+1,10) < = 89),"HYPERTENSION STAGE 1",
IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 140,--LEFT(A1,SEARCH("/",A1)-1) < = 179, --MID(A1,SEARCH("/",A1)+1,10) > = 90,--MID(A1,SEARCH("/",A1)+1,10) < = 119),"HYPERTENSION STAGE 2",
IF(AND(--LEFT(A1,SEARCH("/",A1)-1) > = 180,--MID(A1,SEARCH("/",A1)+1,10) > = 120),"HYPERTENSIVE CRYSIS","")))))
This should solve your task.
The help in the formula is appreciated, but I am still not getting the result I needed.
The table I have to record my data has 8 columns on it and uses columns B-I the data starts at B6 to I6.
I want to use the following information to use as the criteria in a formula for when I enter my BP Data into (D) and (E), (I) Changes status based on the data entered into (D) and (E).
NORMAL less than 120 and less than 80
ELEVATED between 120 129 and less than 80
HYPERTENSION STAGE 1 between 130 139 or between 80 89
HYPERTENSION STAGE 2 140 or Higher 140 or 90 or Higher 90
HYPERTENSIVE CRYSIS Higher than 180 180 and/or Higher than 120 120
B C D E F G H I Date Weight Systolic Diastolic Pulse BMI Weight Status BP Status
What I need is a formula that uses the information above for when you put your BP data into column D and column E, column I changes the status to Normal, Elevated, HYPERTENSION STAGE 1, HYPERTENSION STAGE 2, or HYPERTENSIVE CRYSIS based on the data numbers put in Column D and Column E.
For example:
If you put in 119 in (D) and 79 in (E), (I) Would have Normal as status
If you put in 119 in (D) and 85 in (E), (I) would read Elevated Status
and so on....
Hi!
The formula doesn’t work since it has been created based on the details you provided in your first query. I see from your subsequent comment that your task differs from the one you originally described. Time was wasted. I think that using this formula and the recommendations of the article above, you can find the solution you need.
Based on my latter inquiry what would the formula be? I really need help with this. Unable to come up with the entire formula on my own.
Thank you for this, it really helped me a lot on my excel in OPERATIONA MANAGEMENT AND TQM if some may ask you could also do this.
=IF(OR(AND([@Item]="Pencil",[@Units]>9,[@Total]>1000,[@Region]="Central"),AND([@Item]="Binder",[@Units]>3,[@Total]>1000,[@Region]="Central"),AND([@Item]="Pen",[@Units]>4,[@Total]>1000,[@Region]="Central"),AND([@Item]="Desk",[@Units]>1,[@Total]>1000,[@Region]="Central"),AND([@Item]="Pen Set",[@Units]>2,[@Total]>1000,[@Region]="Central")),"Yes","No")
or
=IF(AND([@Item]="Desk",[@Units]>1),"5%",IF(OR(AND([@Item]="Pencil",[@Units]>9),AND([@Item]="Binder",[@Units]>3),AND([@Item]="Pen",[@Units]>4),AND([@Item]="Pen Set",[@Units]>2)),"2%","0%"))
"as an example"
Hi. I am working on a file that looks like this.
Date submitted Quarter Deadline Remarks
9/7/2021 4th 9/01/2021 Late/On-time
The result that I wanted is like this:
a. If the time of submission is beyond three days after the date reference (deadline with 3-day extension), remarks should be LATE. Excluding weekends from the 3-day extension.
Example, deadline is on 9/1/2021, output is submitted on 9/7/2021, remark is LATE.
b. If submitted within the deadline with 3-day extension, remarks will reflect ONTIME. Excluding weekends from the 3-day extension.
Example, deadline is on 9/1/2021, output is submitted within the deadline or within 3 days after the deadline (9/1-7/2021, remark is ONTIME.
c. If the submitted is from previous quarter or before 9/01/2021, remarks is FOR VERIFICATION
Hoping for your answer ?
Thank you in advance ?
Hello!
To calculate the number of working days, use the NETWORKDAYS.INTL function
=IF(NETWORKDAYS.INTL(C1,A1,1)<0,"For verification",IF(NETWORKDAYS.INTL(C1,A1,1)>3,"Late", "Ontime"))
Hope this is what you need.
Thank you very much.
This is so much helpful.
Im trying to Combine two IF Functions into a single Cell:
=IF(G11>=M13,"SUBJECT-MAJOR",IF(G11=0,"SUBJECT-NO",IF(G111, "SUBJECT-MINOR")))
and
=IF(I11>=4,"SUBJECT-MAJOR",IF(I11=0,"SUBJECT-NO",IF(I110,"SUBJECT-MINOR")))
Can you help me given the scenario
Score is 100
20% of 100 is 20
Scenario :
0 and 0count is Subject No
<20 and 1 count is Subject Minor
<20 and 2 counts is Subject Minor
<20 and 3 counts is Subject Minor
= to 20 and >=1 is Subject Major
>20 and >1 is Subject Major
Pls Help how to combine two IF Functions in one single Cell
Hi!
Please re-check the article above since it covers your case. I also recommend that you study: Excel nested IF statement - multiple conditions in a single formula.
Your conditions overlap, but the formula might be something like this:
=IF(AND(G11=0,I11=0),"Subject No",IF(AND(G11<20,I11<=3),"Subject Minor",IF(AND(G11>=20,I11>=4),"Subject Major","")))
I Want Formula
0 to 10 = 01
11 to 20 = 02
21 to 30 = 03
31 to 60 = 04
61 to 100 and above = 05
please suggest.....Thanks in advance
Hi!
Please note the paragraph above "Using multiple IF statements in Excel (nested IF functions)". See this comment for the answer to the same question.
Examples I have same description in A1 and different quantity in B1, the question is how i can capture all the quantity using the A1 description only. What formula should I used?
Thanks for the answer
Hi!
Sorry, it's not quite clear what you are trying to achieve.
Hello, I have created a formula that has both the IF and IF(AND) included, based on the various scenarios, but it is coming back with #Value error. Please see below. Appreciate your help. What alternative formula may I use?
=IF(P12>=4,100%),IF(P12=3.5,P12=3,P12<3.5),50%)
Thanks much.
Hi!
The formula is written incorrectly and will not work. But I can’t give you any advice, because I don’t know what you want to calculate. Read the article carefully. I think this will help. Or describe the task in detail.
In the column 'L', I have values between 0-100. In column M, i want to fix decile class based on corresponding values in L column. Ex. If L2 value is between 0.00-1.00, i have to get 1 in M column, If L2 value is between 1.01-2, I have to get 2 in M column and so on. I tried the below formula, but error occurs,Pls help me out.
=IF(L2>0 AND L21 AND L22 AND L2<=3,'3','0')))
Hello!
Try using the ROUND function or other rounding functions.
please help me and give me " =if " formula to paste student marks range(0 to 34, 35 to 50,51 to 70....etc) as per their marks
...please help me.....
Marks 0 to 34 35 to 50 51 to 70 71 to 90 91 and Above
A B C
Name Marks find marks belongs to which range(0 to 34,35 to 50…etc)
Alan 80
Bob 50
Carol 60
David 95
Eric 20
Fred 40
Gail 10
Harry 80
Ian 30
Janice 10
Alan 75
David 85
Hi!
Questions like yours are often asked. You can find examples of answers in the comments. For example, this comment will be helpful.
sorry but i didn't get , i didn't find anything from the comment section, please help me because i got stuck...only this last time give me formula ,
column A (Name) column B ( Marks) Colmn C Range
Sam 80 71- 80 < -- --- how to paste this text by
using =If Formula, Cuase i just
want to paste range(71-80)
Im trying this formula ,
=if(B2>=0,B2=36,B2=51,B2=71,B2<=80,"71-80")))
but i did not get the actual rang(0-35...etc)
please help me i have to solve this assignment question ....and tomorrow is my last day pls help
Hi!
I gave you a link to the comment you want. Please note the paragraph above "Using multiple IF statements in Excel (nested IF functions)"
i've try this formula, want to past TEXTVALUE OF ( "0-34",''35-50''OR 50-71 SON ON..) NEXT TO A COLOUMN
A B
35 0-35
55 51-71
91 90 AND ABOVE
=if(A2=35,A2==51,A2<=70,"51-70",..so on) But i did'nt get ans in B Column text range (0-34,35-71,....so on
JUST NEED RIGHT FORMULA TO PAST in cloumn B , " 0-35" or, "35-51", or "51-71", as per their Marks column " A"
thanks i got this formula,
'=IF(A2<=34,"0-34",IF(A2<=50,"35-50",IF(A2<=70,"51-70",IF(A2<=90," 71-90",IF(A2<=100,"91-100",IF(A2=131,"131 and above")))))))
if A2>B2, MAKE A2-B2, OR B2-A2
Hi,
I need help, if I sell every 10cartons i will get 1 carton free
For eg:
sell 29ctns free 2ctns
sell 59ctns free 5ctns
sell 10ctns free 1ctn
Is there any formula I could use for this condition?
Appreciate any help to solve this. Thanks!
Hi!
Use an IF function with multiple conditions.