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)
4500 comments
Adams:
It's not clear from the wording of your question exactly what you want to accomplish, but in general questions like your's are asking about this situation:
You enter a value in a cell, let's say cell A221 and when the value is entered you want to display a grade like "Good" in a different cell, say B221. If this is what you want the formula you should enter in the display cell (B221) is:
=IF(A221<=34,"Fail",IF(A221<=44,"Pass",IF(A221<=54,"Pass",IF(A221<=75,"Credit",IF(A221<=84,"GOOD",IF(A22194,"Excellent")))))))
If your version of Excel supports IFS statements, look up that term here on AbleBits and see how to build a cleaner looking formula.
please i need help how do i find a formula to this
i have to create a school report with a score mark and grade
A1 100-95 EXCELLENT
B2 94-85 VERY GOOD
B3 84-75 GOOD
C4 74-65 CREDIT
C6 64-55 CREDIT
D7 54-45 PASS
E8 44-35 PASS
F9 34-0 FAIL
please i need the formula to this HELP
I have Two columns one for Ps1 and k12 , I want to calculate the value of k12 to comply with these three conditions:
1- if Ps1<=10 then k12=1
2- if 10<=ps1=20 then k12=200/(ps1)^2
Thanks in advance for any help
Nazar:
The first condition looks to be something Excel can determine.
The second condition is unclear.
If PS1 is equal to 20 it can't be equal to 10 at the same time.
Do you mean "IF PS1 is between 10 and 20"?
Can you clarify the conditions?
I have to columns , one for ps1 and another for k12, and I want to calculate k12 to comply with this three conditions:
1- for ps1<=10
use k12=1
2- for 10<=1.5-0.05ps1=20
use k12=200/(ps1)^2
So confused and need this to come together....
Hi all, I'm hoping someone can help. I'm working on a large report for our department and need to filter what is a mispicked item and what is a genuine short-supplied item but I've tried multiple variances and still can't clear this up as per table below.... I need to match the invoice numbers but then check if Credit Code is a 4 and a 5 from that invoice to show "Mispick" but if the invoice numbers don't match and there are two "Short" credits then the filter needs to show "Short" as per table:
My current code and results below but I am still getting false "short" results....... all help would be massively appreciated.
=IF(OR(AND(G2=5,G3=4),AND(G3=5,G2=4),AND(OR(C2=C3,OR(C3=C2)))),"Mispick","Short")
Inv # Code Slot Cases Credit Code Reason Results:
738527 136890 513211 1 5 Over Mispick
738527 45599 513222 1 4 Short Short (wrong)
735549 141191 C10912 2 4 Short Mispick
735549 593377 C11212 2 5 Over Short (wrong)
761848 156729 402831 1 5 Over Mispick
761848 134880 403021 1 4 Short Mispick
761848 649776 740421 1 5 Over Mispick
761848 637876 741011 1 4 Short Short
762648 278210 CD0813 1 4 Short Short
765184 104500 290511 1 4 Short Mispick
765184 283622 290531 1 5 Over Mispick
764902 104500 290511 1 4 Short Short
762670 104500 290511 1 4 Short Short
758860 49658 C30111 1 4 Short Short
764905 662505 294132 1 4 Short Short
Very useful information!. Thank you.
dear all
pls advise for the excel formula for below condition
i have two rate for multiple currency ,if amount is greater then eur 10000 the rate 71 other wise 72, if amount is greater then USD 10000 then rate 65 other wise 66.
formula required for below task
Currency 10 lacs
USD rate 67.50 67.2
Euro rate 82.01 81.65
Pound rate 93.49 93.07
Currency Amount Rate
USD 100000.00 ?????
Parkash:
If this is the condition where currency in in column A the rates are in columns B and C, then USD dollar amount is in column B and the final amount is in column C this is what this looks like:
Currency
USD rate 67.5 67.2
Euro rate 82.01 81.65
Pound rate 93.49 93.07
USD $100,000.00 $6,750,000.00
The formula to achieve this is:
=IF(B6>=10000,(B6*B2),(C2*B6))
Would you help me remedy this formula?
=IF(T2=3)AND(I2>1)AND((M2-AC2)>(I2*.7)),"N","Y")
the second qualifier is my issue. T2 greater equal to or greater than 3 AND I2 greater than 1 AND Value of M2-AC2 is greater than I2*7, return N, if not return Y.
Tim:
I believe this needs to be an IF AND statement like this:
=IF((AND(T2>=3,I2>1,M2-AC2>I2*0.7)),"N","Y")
Notice the T2 >=3 per your logic.
Inder:
I would solve this problem with VLOOKUP. AbleBits has a good description for beginners. You should read it so that you'll better understand what I'm recommending.
This is how I'd solve your issue.
In a workbook called "Tolls 2018" I would create two worksheets. One named "TollAmt" and a second sheet named "AprilToll". Incidentally, whenever I do this for a client I create one sheet for each month of the year in a book titled for that year. This keeps the data organized by date, which is how they eventually need it.
"TollAmt" has three columns. First column is PlaceNum, second is PlaceName and the third column is TollAmount. Enter the appropriate data in each column. For example, PlaceNum is 1 thru 100, PlaceName is AAA thru ZZZ and TollAmount contains the toll amounts for each toll place.
Now, in the "AprilToll" sheet create five columns. The first column is named Date, the second is named TRK #, the third is named Toll Place, the fourth is named Place Name and the fifth and last column is named Toll Amount.
I'm assuming you're tracking some kind of vehicle's toll expenses, so I named the second column TRK # for truck number, but you can call it whatever you want. You'll enter data into the first three columns manually. The fourth and fifth columns are where the magic happens.
Enter this into AprilToll cell D2. =VLOOKUP(C2,TollAmt!$A$2:$B$100,2,FALSE)
Enter this into AprilToll cell E2. =VLOOKUP(C2,TollAmt!$A$2:$C$100,3,FALSE)
Now watch the magic!! When you enter the Toll Place, Place Name and Toll Amount are populated automatically.
If you need the PlaceNum, PlaceName, and TollAmount in one cell just concatenate them like this: =CONCATENATE(C1&E1&D1)
However, if you keep the data separated you can more easily analyze it by date, truck, place name, etc.
Hi,
Really thanks for your help. I have tried your formula in different way and it works !! many thanks again ..
Hi,
I have a big list (almost of 100 places with different toll amounts) of toll names and amounts. Every month I have to prepare all the toll list manually.I want to make a sheet where I put the place name in a particular column and the other cell (in column) take value of its toll amount automatically. I have tried to put multiple if formulas in a single cell but it doesn't works or showing error.
Can you please help me on this.
Are each of the place names unique?
yes !!
for an example.
place 1 ABC , amount - 100
place b BBB , amount - 200
place c, BBD , amount - 700
Place d, e ,f,g,h........
I need all the condition in a single cell. I just want to put the place name in a cell and the another parallel cell should take the amount automatically !!
I want to use astrick (*)in If formula eg coloumn A1to A5 equalor greater than 35 pass, less than 35 fail and A1 to A5 any cell 35* promoted. For these three conditions please suggest formula.
=IF(OR(AND(E5="Critical",F5="OK"),1), AND((E5="Critical",F5="NOK"),0),"")
What is error here
Thanks in Advance
Hello, Reza,
It looks like the problem is in the usage of the logical AND / OR functions. Please read how to correctly use these functions here.
Or if you describe the conditions for your formula in detail, we'll be able to specify what is wrong with the formula.
HELP PLEASE :)
I am trying to create an "IF" function with several variables. Below is my formula, the first two functions seem to work correctly, but the second two do not.
Any HELP would be greatly appreciated!
B19 C19
1 Dollar Amount
2 Dollar Amount
3 Dollar Amount
4 Dollar Amount
5 Dollar Amount
6 Dollar Amount
=IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))
the formula did not copy identically
=IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))
The formula will not copy correctly.
it should be
"=IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))"
UGGGGGG it will not copy correctly!
=IF((AND(B19>=1,C19>=5000)),"Full Servicing",IF((AND(B19>=1,C19=1000)),"Modified Servicing",
IF((AND(B19>4,C19=500)),"CLAR",IF((AND(B19>=1,C19<500)),"N/A"))))
I'm getting a #NAME error with the following equation:
=IF(OR(AND(H3=“Exceptional”,H3=“Acceptable”),AND(I3=“Exceptional”,I3=“Acceptable”),AND(J3=“Exceptional”,J3=“Acceptable”),AND(K3=“Exceptional”,K3=“Acceptable”),AND(L3=“Exceptional”,L3=“Acceptable”)),“Yes”,“No”)
What am I doing wrong?
if formula want any one of three value is true then 1 any of 2 values true then 4 and all three values true then 5 Marks
Greetings;
I was working as Office Admin in 24/7 Operation in UAE. I want so set my workbook to easily track the status of our personnel with the code "IN/T" with flight ticket, "IN" no flight ticket but still in the country and "OUT" if personnel is on leave. I've been try several formula but cannot get the right solution.
in line with this, can you help me to get the right formula to tract movement status of our personnel base on the following.
status Outbound Inbound
OUT 02-Mar-18 30-Apr-18
IN/T 20-Apr-18 19-May-18
IN -- --
I'm looking forward for your kind response.
Thank you.
=IF(E5=1,A,0),IF(E5=1.2,B,0),IF(E5=1.5,C,0)
Hello I need some assistance with creating a match formula..
cell A1 i have a name, date and punch times-- (bchestnut04/01/201810:50AM02:00PM)
cell B1 i have a name, date and punch times from a separate data base--(bchestnut04/01/201810:50AM02:02PM)
I need to match the two cells based on name, date and punch times but because the punch times may differ in as little as 2 minutes. I am receiving an N/A, no match. I would like to somehow consider this a match even though there is a 2 minute difference. thanks for your help!
If C column is todays date (11 April 2018). D column is Equal to or grater than 90, and E column is 'Yes' then F column is 1 year from now (11 April 2019). But if C column is todays date (11 April 2018). D column is Equal to or grater than 90, and E column is 'No' then F column is 6 months from now (11 October 2019).
Dear Sir,
Please help me. how can solve the below attached problem.
PAR Target Actual PAR Result
100 1000 10%
100 200 50%
100 500 20%
100 20 120%
100 50 120%
100 25 120%
100 150 67%
0 1 0% 10%
=IF(C4=0,"120%",IF(C4B4,B4/C4,))))
=IF((OR(C11>0,B11=0)),C11/1000%,)
Hello,
i need a formula to find a specific article code in a sheet, after display the latest date of purchase order in the next sheet(all columns related to this date(invoice no,article cost ect)), since an article code might be bought more that two times....
can you help
thanks
Dear,
i have 2 columns . i need to show the column which is higher and greater than 20% of another column
please everyone help me with this problem, thank you:
I want to form a formula in one cell with this conditions
1) 1.0:FG<2.7
2) DP:ABSENT=10
3) INC:LACKING
where: FG= final grade at AB11
ABSENT = No. of absences at Z11
INC=incomplete at AC11
i have my formulas below but its error,
=IF(AB11<2.7,"1.0",IF(Z11=10,"DP",IF(AC11LACKING,"INC")))
Cell A1: Name
Cell B1 to F1 has numbers.
Condition 1: All cells B1 to F1 are >0 Cell A1 to be highlighted in Yellow color
Condition 2: Cell F1>0, any three of cells B1 to E1 are>0 and one of the cells B1 to E1 is 0, any two of cells B1 to E1 are>0 and the two other cells (B1 to E1) is 0, any one of cells B1 to E1 is>0 and the three other cells (B1 to E1) is 0, and all other cells B1 to E1 are 0)*AND(E1>0)*AND(D1>0)*AND(C1>0)*AND(B1>0)
Condition 5: =(F1>0)*AND(E1<0)*AND(D1<0)*AND(C1<0)*AND(B1<0).
Can you help me with the formula for condition 2,3,&4
I need help with a formula please. I have 3 columns. First column(A1) can only contain values of 1, 2 or 3. Second column(B1) can only contain Grade 1, Grade 2 or Grade 3. In the 3rd column(C1), I want my if statement to display yes or no based on the following:
if A1 = 1 and B1 Grade 1 then C1 display Yes, otherwise display No
if A1 = 2 and B1 Grade 2 then C1 display Yes, otherwise display No
if A1 = 3 and B1 Grade 3 then C1 display Yes, otherwise display No
Is that possible?
Merit List
Decide the Eligibility of Student for the admission
Direct admission: if Total Marks is >600 & Sum of Maths Marks & science Mark is >=180
1st Merit List: If Total Marks is >=500 & Maths Marks >=55 & Science Mark >=55
2nd Merit List: If Total Marks is >=400 & Maths Marks >=55 & Science Mark >=55
3rd Merit List: If Total Marks is >=300 & Maths Marks >=50 & Science Mark >=45
Else not Eligible
I need a formula for a chart if B5:E5 has any figure greater or equal to $100.00 then print the word YES.
Hi Brainstrust, I am really hoping for some guidance here. I have a cell that will either say Buy or Sell. I have the 2 following formulas that work on there own but I need them combined somehow into 1 cell so that it determines what to do depending on the cell saying Buy or Sell, hope this makes sense, Thanks in advance
=IF(B5="BUY";(H5-E5)*100-1)
=IF(B5="SELL";(H5-E5)*100)*-1
Hi Cory,
You can nest one If inside another, like this:
=IF(B5="BUY"; (H5-E5)*100-1; IF(B5="SELL"; (H5-E5)*100*-1; ""))
Hi Everyone!
Appreciate if you could assist me. Basically I am looking to create a formula that has multiple conditions. I daily work in excel for Inventory Report where at present I am updating status manually. What I am looking for a excel formula which will enable auto update of remarks. My requirement is as below
IF R2=0 status should read as "IMPORT IN TRANSIT"
IF R2>0 status should read as "IMPORT LADEN"
IF R2>0 & S2>0 status should be "UC"
IF R2>0, S2>0 & U2>0 status should be "AV"
IF R2>0,S2>0, U2>0 & W2>0 status should be "D.O. ISSUED"
Please guide me, how and what formula will make excel work to have these conditions fulfilled and remarks updated automatically.
Thanks to assist.
Hi everyone! could one of you all help me? I'm trying to write a formula that has multiple conditions. This is what I have so far: =IF(K4="Item1",21.31,IF(K4="Item2",25.3,IF(K4="Item3",((0.77-0.58)*0.65),IF(K4="Item4",((0.68-0.49)*0.65),IF(K4="Item5",((0.77-0.58)*0.65))))))
and it works, but I am wanting it to only be true if I4=BrandA or Brand B... any suggestions on how to add this new condition?
Thank you! Very useful! :D
Can someone please help me ?
I am wanting a formula in D1, that if there is a "Y" or "N" in cells D2 to D12, but ALL cells are "Y" then, D1 shows "Y", OR, if ANY cells are "N", then D1 shows a "N".
thankyou so much for any assistance.
Hi All,
Could someone be kind enough to help me with this formula please.
This sheet is used to calculate commissions and i need help with figuring out how to issue an increase in percentage depending on the term chosen from drop down list.
So for example: if Product "A" has a "10yr" term, a "20%" commission is paid. For every additional yr, i need to issue a 2% increase. Minimum is 10yrs and Maximum is 20yrs.
Column D (drop down list with term)
Column H (Value which percentage will be calculated from)
Column K (Cell in which formula will be shown)
Eagerly awaiting a reply as this is driving me insane.
Thanks in advance
Hi All,
Could someone be kind enough to help me with this formula please.
This sheet is used to calculate commissions and i need help with figuring out how to issue an increase in percentage depending on the term chosen from drop down list.
So for example: if Product "A" has a "10yr" term, a "20%" commission is paid. For every additional yr, i need to issue a 2% increase. Minimum is 10yrs and Maximum is 20yrs.
Column D (drop down list with term)
Column H (
How can we create formula for If— D12 & G12is lesser than 1% than C12 will “0” but D12 & G12 greater than 1% than C12 will take value of C12
How can we create formula for If—D12 & G121% than C12 will take value of C12
I need to check the condition for Multiple months, if the month is in the past it needs to refer to that column, if the month is the current or future it should refer to a different column.
Requesting your help as it should check both the conditions and accordingly pick the data
For Example for Jan and Feb the column values are in B, and for the rest of the year the column values are in C how do i write the formula? The rows in which the datas for Jan and Feb would be jumbled. So it has to check the formula for all the months and pick values accordingly.
Thank you for your help in advance!
Hi, can anyone suggest how I can make cell G11 look in cell D11 to see if it contains TRAVIS if it does and the current contents of the cell is 5 or greater then the cell needs to turn red.
Maybe this (above question) is best solved using VLOOKUP??
Hello, Brantley,
You should use the nested IF function along with AND. The formula will look similar to the one below:
=IF(AND(D3="Single",E26="65 & Blind"),"$2600", IF(AND(D3="Single",E26="65 or >"),"$...", IF(AND(D3="MFJ",E26="65 & Blind"),"$...", IF(AND(D3="MFJ",E26="65 or >"),"$...",0))))
Please just specify the deduction amount for each pair of the selected values in cells D3 and E26.
Hope this will work for you.
Trying to make simplistic personal income tax calc program. 2 cells (D3 & E26) have drop-down data validation selectable options. Depending on each cell selection, a different deduction amount ($1300, $2600, $3200) will be returned. D3 can be either "SELECT", "Single" or "MFJ" (and kind of the master cell-choose 1st). E26 options are "SELECT", "65 or >" or "65 & Blind". Different deduction amounts are used depending on whether SINGLE or MFJ is selected AND 65 or > OR 65 & Blind.
Example: If "SINGLE" selected in D3 and "65 & Blind" selected in E26, value in F26 should be $2600. IF nothing is selected in D3 or E26, -0- should be value in F26. I think it's some kind of nested =IF(AND or =IF(AND(OR function but having validation options is killing me. Nothing I try works. help!!!
hi ,
I am trapped here, how to get such statement,
""Cell A= 12
Cell B= 21
Cell C= 34
if A is greater than B, then A is the good number else
c is good""
but I want get the value whatever I put in A,B,C cells, as we use in C or C++ in printf or scanf statement.
I am trying to write an if statement to achieve the following
If cell A1 is blank and cell B1 is not blank, vlookup cell B1 in (Table Name)
If cell A1 is not blank and cell B1 is blank, return the contents of cell A1
If cell A1 is blank and cell B1 is blank, maintain the blank
Is there a way to combine this all into one IF statement?
Hello. I've really been struggling with this with the proper placement of the "IF"s, "OR"s, and "AND"s, and would appreciate help. I am comparing letter values in 2 columns and, based on the combination of the 2 letters, want to return a value that is in one of 3 cells in a "key" at the top of the worksheet ($A$3, $A$4, or $A5).
If A9=B9, return $A$3
If A9="H" and B9="HM", return $A$4
If A9="HM" and B9="M", return $A$4
If A9="M" and B9="LM", return $A$4
If A9="LM" and B9="M", return $A$5
If A9="M" and B9="HM", return $A$5
If A9="HM" and B9="H", return $A$5
If A9 contains any value and B9 is blank, return "N/A"
While I'm here, is there a way to have the returned value from $A$3, $A$4 or $A$5 be the same color that are in those cells? ($A$3 is blue, $A$4 is green, and $A$5 is red.)
Thank you very much!
Need help with this please:
=IF(G4=0,H4=0,I4=0,10,IF(G4=1,H4=1,I4=1,0)
an addition to previous question, this formula works:
=IF(C2="C. 3-5 years","4",IF(C2="B. 1-2 years","1",IF(C2="A. 1 year or less","0")))
this doesn't:
=IF(C2="C. 3-5 years","4",IF(C2="B. 1-2 years","1",IF(c2="A. 1 year or less","0",IF(C2="D. 6-10 years","7",IF(c2="E. 11-15 years","12")))))"))))))"
Thank you
Hello,
Please help find an error:
The formula is supposed to assign a numeric value to a cell based on the response (specific text) placed into another cell (c2). I have five potential responses when adding more than three responses into the formula, receiving an error. The formula is:
=IF(C2="C. 3-5 years","4",IF(C2="B. 1-2 years","1",IF(c2="A. 1 year or less","0",IF(C2="D. 6-10 years","7",IF(c2="E. 11-15 years","12")))))"))))))"
Thank you in advance
Stuck on this one. Please help.
I have 2 (data validation) LISTS and based on the values selected in each list, I would like it to display the value of the coordinates (on the first page).
EX. PAGE 1
A1="APPLES"
A2="ORANGES"
A3="PEACHES"
A4="PEARS"
...
B1="WEEK 1"
C1="WEEK 2"
D1="WEEK 3"
E1="WEEK 4"
...
PAGE 2 should display the data in the COORDINATES where the 2 points meet from page 1
Trying to avoid hundreds of lines of IF formula.
im trying to make a table for stock movements on my property.
i need to use multiple if and or functions in 1 formula is this possible?
Eample
if A1="Steers" or "Heifers" and B1>0 than C1=X
but if A1 "Cows" or "Bulls and B1>0 than C1=Y
Hi, can someone help me please?
I need to use a formula with mutilple conditions but cannot make it work.
I have tried to follow one of the examples provided above and ended up with the following formula:
IF(A4="New York",C4+$H$2*12,IF(E4="Chicago",C4+$H$4*12,IF(E4="dallas",C4+$H$3*12,IF(E4+"LA",C4+$H$5*12))))
Thanks in advance
Hi,
How can i apply the formula for finding minimum value in alternate cells and not consider if cell value is Zero.
eg: g24,j24,,m24,p24 in these cells having different value include zero. How do i create the formula