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 am trying to formulate a spreadsheet for sales. we pay a referral fee at different values based on what the product is. I have a spreadsheet together and one column shows the amount to pay the referrer, however i would like it automated. the scenarios we pay away are on the fee we charge, the mortgage we have arranged and the life insurances we have arranged. for a fee, I need it to show IF a column shows (referrers name) and IF a column shows BROKER FEE (chosen from a dropdown of business type) and also IF a column shows the fee is £399 (M), this box (G) must show £127.50.
For the mortgage, i need it to be IF a column shows (referrers name), and IF a column shows mortgage type (chosen from drop down of purchase, remortgage etc.), then this box in the formulated column (G) must show 33% of the value in another column (N).
Hi! Unfortunately, your explanation is not very clear. I don't really understand which data in which columns determines the final result. I think you can use the recommendations in the article above, as well as these guidelines: Nested IF AND statements. If the information in the article is not enough, give an example of source data and desired result. It will help to understand what you want to do.
I'm trying to do a formula that stratisfies people into one of four categories based on a score. =IF((O3>12,"platinum"),(O3>9,"gold"),(O3>6,"silver"),(O3<6,"bronze")). what am i doing wrong?
Hi! Look for the example formulas here: Nested IF in Excel – formula with multiple conditions.
I have a very long nested IF statement that provides me with the most of my desired results. For cells that should be blank, the result is "0". I would like for these to be blank cells. I have tried including another nested IF statement in my original "IF(C4=0,"") but this still provides me with a result of "0". I cannot figure out where I making my mistake.
If you can write such a long formula into a cell, add another nested IF function. It might look something like this:
=IF([your formula]=0,"",[your formula])
If you have a lot of conditions, I recommend using the IFS function. Read more: Use Excel IFS function instead of nested IF. I hope it’ll be helpful.
NORMAL DAY SUN/REST DAY/PH OT HOURS
START END START END
0700 1900
0700 1900
HI. my problem is how can i make it calculating the OT(overtime) hours automatically by just filling in the time. tq so much.
Hi! The answer to your question can be found in this article: Calculate time in Excel: time difference, add, subtract and sum times. I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Hi,
Thank you for helping everyone.
I'm trying this forever and I can't make it work no matter what.
I need a formula that will give me a = value based on a diffente cell value. The problem is that the cell that pulls that information is a drop down that can have 20 different variations.
For example: B4 (the mail cell) is a drop down that has 20 different options. On a different sheet I have the information that will feed the other cells based on B4.
I was able to do this with 1 single option (1 of the variations) but I cannot get it to read the others. It says or too many arguments or too few arguments.
=IFERROR(IF(A4="Anaheim / Garden Grove"),'Data Base'!A2,IF(A4="Manteca / San Francisco"),'Data Base'!A4),IF(A4="Atlanta / LaGrange"),'Data Base'!A6, IF(A4="Baltimore / Perryville"),'Data Base'!A8), "") --- I tried this with ifs, or, and... nothing words.
The one I was able to make work was =IF(ISNUMBER(FIND("Anaheim",B4)),'Data Base'!A2,"invalid"), but B4 can only contain Anaheim. If I try to add another condition it says that it's too many arguments.
Please help. I'm going crazy.
Hi! If I understand your task correctly, this article may be helpful: Excel Nested IF statement: examples, best practices and alternatives. For example, try the SWITCH function as an alternative to nested IF:
=SWITCH(A4, "Anaheim / Garden Grove", 'Data Base'!A2, "Atlanta / LaGrange", 'Data Base'!A6, ........)
You can try the other alternatives suggested in this article.
Here
a=days (friday, saturday, sundat..)
If a= friday, saturday formula will show weekeknd and rest of the days forula will show office day.
Now how i implement if formula
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you WEEKDAY function. Read more: Excel WEEKDAY function: get day of week, weekends and workdays. For example,
=IF(WEEKDAY(A1, 2)<6, "Workday", "Weekend")
Please help! I am trying to use some kind of logical test to display the value from a pre-set table on a separate tab, based on the text displayed in the cell being "tested." There are multiple possible values and multiple outputs based on the values which are pulled from that table, and the test needs to be for a specific test.
Essentially, I want to pull a cost by brand, so if D2 displays "Hilton" I want it to pull the value from my data table in the cell that contains the cost for Hilton. If it contains "IHG" I want it to pull the value from my data table in the cell that contains the cost for IHG.
Where did I go wrong on my formula below?
=IF(D2=Best Western, "='Data Table (do not edit)'!B2"), OR,
IF(D2=Choice, "='Data Table (do not edit)'!B3"), OR,
IF(D2=Hilton, "='Data Table (do not edit)'!B4"), OR,
IF(D2=IHG, "='Data Table (do not edit)'!B5"), OR,
IF(D2=Marriott, "='Data Table (do not edit)'!B6"), OR,
IF(D2=Hyatt, "='Data Table (do not edit)'!B7"), OR,
IF(D2=IHG, "='Data Table (do not edit)'!B8")
Hi! In your formula, text values need to be enclosed in quotes. For example, IF(D2="Best Western",'Data Table (do not edit)'!B2).
However, if you want to find the price by brand, I recommend using functions to search the data. For example, you can see detailed explanations and example formulas here: Excel VLOOKUP function tutorial with formula examples and INDEX & MATCH in Excel - better alternative to VLOOKUP.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Arun
Arun
about 1 week ago
#44203
Hi,
How to Count (D71) CONDITIONALLY FORMATTED RED COLOR CELL only (D40:D70)?
Pls guide...
Thanks
Hi! Your question is not clear. But perhaps you will find this article useful: Excel conditional formatting for dates & time: formulas and rules.
Hi, Can you help with the below case?
In one column I have employee levels - L7, L8, L9,10
In the other column, I have calculated their time in level (In months).
Now I would like to apply a criteria-based formula to arrive at the eligibility.
If L07 employees have completed more than 24 months, they would be "eligible", or else "ineligible". For the rest of the levels, there isno level criteria and hence they will all be eligible.
Please help
Hi! Since you have two conditions, I recommend using the paragraph in the article above: Excel IF statement with multiple conditions (AND logic). Also I recommend reading this guide: Nested IF in Excel with AND statements. I hope my advice will help you solve your task.
Thanks!
I've tried using this formula - =IF(OR(AND(U3="L07",BM3>=12),"Eligible","Not Eligible"),AND(U3="L06","L08","L09"),"Eligible")
But it gives me the correct status for L06,L08 and L09 levels but for L07 level where there is criteria - It shows FALSE
Could you please help?
Hi! If I understand your task correctly, try the following formula:
=IF(U3="L07", IF(BM3>=12,"Eligible","Not Eligible"), IF(OR(U3="L06","L08","L09"),"Eligible"))
Course title postal code
============================
Course 1 - Forklift 122222
Course 2 - Technical 444444
Course 3 - Writing 333333
If the course is under "Forklift", it will auto assign to Sales D and no need to check the postal code.
If the course is not under "Forklift", then we will pick up the 1st 2 digits of the postal code and assign to respective sales name.
example: between 01-22 is under sales A,
between 23-44 is under sales B,
between 45 - 55 is under sales C,
between 56 - 99 is under sales D
Hi! It is not clear from your description exactly what result you want to get. However, you can extract the first two digits from the postal code using the LEFT function. This instruction will help you to use multiple conditions in the formula: Nested IF in Excel – formula with multiple conditions. To get a more accurate answer, describe the problem in detail.
Hello,
I have two sets of data, Data Set 1 has a list of items on customer pricing contracts. Each contract is for a specific sales location (branch). There are three locations: Location 1, 2 & 3. My goal is to indicate which items on the customer contracts in Data Set 1 have a stock status of Yes or No (y/n) and other attributed data based on Data Set 2; I will explain below.
Data Set 2 has a list of items at each sales location (branch) with its stock status (y/n), min/max level & qty on hand. This information is different based on the sales location. See below for example:
• At location 1: Item ABC has a stock status of Y, min/max is 1/2, Qty on hand is 2
• At location 2: Item ABC stock status is N
• At location 3: Item ABC stock status is Y, min/max is 5/10, Qty on hand is 10
So, I need to create a nested IF statement with multiple lookups that says if in Data Set 1, the item is on a customer contract for Sales Location 1, then I want to pull from Data Set 2 the Stock Status, min/max & Qty on Hand for Location 1.
Hi! The IF function can return only one value. I recommend reading this guide: How to Vlookup multiple values in Excel with criteria. If this is not what you need, give an example of source data and an example of the desired result.
Hi, I'm trying to complete a formula where IF D5 says Low or Moderate, then the output would say "NA" and if D5 say High or Sensitive (or the value of Low, Moderate is false), then the output would be blank. I've tried using variations of this and keep getting an error: =IF(AND(D5="Low",D5="Moderate"),"NA","")
Hi! Cell D5 cannot have two different values at the same time. Use the OR function. Read more: Excel IF OR statement with formula examples. Try this formula:
=IF(OR(D5="Low",D5="Moderate"),"NA","")
Sir I have a case, I have 5 category of revenue: Bronze, Silver, Gold, Platinum Diamond.
Now I have 3 column with header; Category Revenue 2022, Category Revenue 2023, and Movement Category
what's the function if;
(column 1 2022) = (column 2 2023) = (column 3 Movement Category)
Bronze = Bronze = Stay
Bronze = Silver = Upgrade 1 Level
Bronze = Gold = Upgrade 2 Level
Bronze = Platinum = Upgrade 3 Level
Bronze = Diamond = Upgrade 4 Level
Silver = Bronze = Downgrade 1 Level
Silver = Silver = Stay
Silver = Gold = Upgrade 1 Level
Silver = Platinum = Upgrade 2 Level
Silver = Diamond = Upgrade 3 Level
etc to Diamond
can I make it to be 1 formula?
Thanks I appreciate your reply, one by one of people cases
I will follow your Instagram or YouTube if you have it
Hi! For multiple conditions, I recommend using the IFS function. For example:
=IFS(AND(A1="Bronze",B1="Stay"),A1,AND(A1="Bronze",B1="Upgrade 1 Level"),"Silver")
You can find the examples and detailed instructions here: The new Excel IFS function instead of multiple IF.
Hello, i am trying to figure out how to get the following. I have 3 columns, (A, B and C). Columns A and B have 2 lots of data in, lets say column A has the names Tom, James and Martin, and Column B has Cow, Dog and Hat. Column C has Yes, No, or Maybe. And then I have multiples of the options, so Tom, Cow, Yes, Tom Dog, No, Tom Hat, Maybe, James Cow Yes etc.
I then have dropdowns that point to columns A and B respectively. I want to have a formula that says if the Dropdown A says Tom, and Dropdown B says Dog, then we get the relevant column C option. If A says Tom, and B says Dog, we get the relevant C option etc etc.
I am assuming its a form of IF formula but cant figure out which one,
Hi! For multiple conditions, try using the IFS function. Read more: The new Excel IFS function instead of multiple IF.
=IFS(AND(A1="Tom",B1="Dog"),"Yes",AND(A1="James",B1="Dog"),"No")
You can also create a separate table with all possible variants of values. Then get the desired variant from this table as described in these instructions: Excel INDEX MATCH with multiple criteria.
I'm trying to build a formula for benefits audit: if Mary has dental, pull the value of that deduction, and if she has health, pull the value of that deduction from one worksheet to another worksheet. SSN is the common data on both sheets. I started with xLookup to match the employee, but when dragging down the fill handle, it pulls the same dental value. Seems like I'm close, but missing something. Is Index Match best, or IF, or other...? Thanks
Format is:
Benefit Report
Employee SSN Plan Type Pay Period Rate
Miller, Mary, 1234 Dental $35
Miller, Mary 1234 Health $60
Hi! If I understand your task correctly, this article may be helpful: How to VLOOKUP across multiple sheets in Excel with examples.
If this is not what you wanted, please describe the problem in more detail.
Thank you, Alexander. I'll try it. I'm not sure how to do this. Thinking I need a Match formula to pull the amount by dental in one row, and the amount by health in the row underneath for the same employee.
Hi! If you don't want to find the value of a condition, but to calculate the sum of the values based of the condition, try using the SUMIFS function. I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Dear sir @Alexandar Trifuntov,
How to get this in Excel.
Amount <= 500,000 , result l=500.
If not , result = amount*1.5%
Hi! You can find the answer to your question in this article: IF function in Excel: formula examples for text, numbers, dates, blanks. Try this formula:
=IF(A1<=500000,500,A1*1.5%)
Im trying to create an if statement that if a cell is equal to "gravel" or "rock fill" and another cell is "Loose" or "dense", then it will provide a value if it were for that particular rock and at a particular compaction. For example, gravel at loose is 16 and 17 at dense, rock fill is 18 at loose and 19 at dense.
Hi! You can't use very many conditions for the nested IF function. You can try the IFS function instead multiple IF. For example:
=IFERROR(IFS(AND(A1="gravel",B1="loose"),16, AND(A1="gravel",B1="dense"),17, AND(A1="rock fill",B1="loose"),18, AND(A1="rock fill",B1="dense"),19),"")
However, if there are too many conditions, the formula will be very large. Therefore, I recommend using the INDEX MATCH formula and this guide: Excel INDEX MATCH with multiple criteria.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Thank you Alex, that's fantastic!
Another question I have is I have run into overworking the IF statement, I have multiple different classes equalling various units, what function should I use here? For example, class 1 = 5, class 2 = 6, class 3 = 7....
Hi! The IF function has limitations. Therefore, use the IFS function instead of multiple nested IF. Read more: Use the new Excel IFS function instead of nested IF. Hope this is what you need.
I'm trying to use Conditional Formatting to highlight cells that have less than 20 students in any course that falls between 200 and 299 (all 200 level sections. So greater than 200 but less than 300 and less than 17 students less fill the student number cell with red.
Can't get it to work.
Hi! Your explanation is not very clear. Without seeing your data, it is difficult for me to understand what you want to do and recommend you a conditional formatting formula. Explain the problem in more detail. You may also find this article useful: Excel conditional formatting formulas based on another cell.
I have a very straightforward query. i have just 2 columns - Column (1) is having profit made in a deal , Column (2) shows the number of days the deal took. I want to create a new column (3) where formula has to be put showing Tax payable . The formula should show as below
if no profit has been made then no tax
if the deal took more than 365 days then tax of 10%
if the deal took less than 365 days then tax of 15%
I have tried If and functions but it just does not work. can someone help
THe tax of 10% has to be calculated on column (1) contingent that 365 days have lapsed or lesser days have lapsed and should show 0 if there is loss or zero profit
i have tried multiple times if and function failed miserably
Hi! You can see how to do calculations depending on multiple conditions in this article: Nested IF in Excel – formula with multiple conditions. Try this formula:
=IF(A1>0,IF(B1>365,A1*0.1,A1*0.15),0)
Hello Mr Alex, Thanks a lot it worked so well. I tried so many times but failed will try to learn more from the link. Thanks once again. If I have any more doubts will certainly get in touch with you Warm Regards
Hi Alex, I have one more doubt regarding a large excel data sheet in which I would like to weed out the data. WHAT FORMULA TO USE is it V LOOK UP The scheme is as follows
(1) Column A with date of trade (2) Column B with symbol of stock being bought/sold (3) Column C with Name of stock being bought/sold (4) Column D with name of client (5) Column E with Trade name ie buy or sell
Now i want to remove all rows ie original and duplicate if the same client has bought and sold the same stock on the same day . It should leave behind only trades which are buys with no equivalent sale. I am giving below a short snippet of the table for better understanding
Date Symbol Security Name Client Name Buy / Sell
10-May-22 AKG AKG Exim Limited ABC SELL
10-May-22 AKG AKG Exim Limited ABC BUY
10-May-22 ASIANTILES Asian Granito India Limit CNM BUY
10-May-22 ASIANTILES Asian Granito India Limit Gravity BUY
10-May-22 ASIANTILES Asian Granito India Limit Gravity SELL
10-May-22 ASIANTILES Asian Granito India Limit Jits BUY
10-May-22 ASIANTILES Asian Granito India Limit Veks BUY
10-May-22 ASIANTILES Asian Granito India Limit vekk SELL
10-May-22 ASIANTILES Asian Granito India Limit vila SELL
Date Symbol Security Name Client Name Buy / Sell
10-May-22 AKG AKG Exim Limited AMRITA JAIN SELL
10-May-22 AKG AKG Exim Limited AMRITA JAIN BUY
10-May-22 ASIANTILES Asian Granito India Limit CNM FINVEST PRIVATE LIMITED BUY
10-May-22 ASIANTILES Asian Granito India Limit GRAVITON RESEARCH CAPITAL LLP BUY
10-May-22 ASIANTILES Asian Granito India Limit GRAVITON RESEARCH CAPITAL LLP SELL
10-May-22 ASIANTILES Asian Granito India Limit JITESHKUMAR S TIKADIYA BUY
10-May-22 ASIANTILES Asian Granito India Limit VENKATESH BHASKAR SHENOY BUY
10-May-22 ASIANTILES Asian Granito India Limit VENKATESH BHASKAR SHENOY SELL
10-May-22 ASIANTILES Asian Granito India Limit VIMLA PAWANKUMAR SANGHVI SELL
THe alignment gets lost everytime i paste the table hence it has come twice
Hi! I recommend reading this guide: How to find duplicate rows in Excel.
If I understand your task correctly, our Duplicate Remover tool may help you solve it in a few clicks. It allows you to specify in which columns to search for duplicates, and then find and delete duplicate rows. This can be done in just a few clicks, without formulas or VBA code. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
okay sir thanks a ton
So I have 2 columns with values that result in a final "grading" in a 3rd column.
The first column, G, has one of the following 9 values: 0,1,2,3,4A,4B,4C,5,6
The next Column, J, Has one of the following 3 values : "Benign/Negative", "Indeterminant/Atypical", "Malignant"
Dependent on the G/J combinations my 3 possible resultant values are : "Needs Review", "Yes - Pathology and Imaging are concordant", "No - Pathology and Imaging are discordant"
So when G=0 or 4B, or J="Indeterminant/Atypical", then "Needs Review"
When J="Benign/Negative" AND G>04 then "Yes - Pathology and Imaging are concordant"
If none of those conditions are met then "No - Pathology and Imaging are discordant"
How do I write this as IF statement?
Thank you for your help!!
I apologize, part of the "AND" conditions did not make it in my question. The conditions are as follows:
G=0 or 4B, or J="Indeterminant/Atypical", then "Needs Review"
J="Benign/Negative" AND G>04 then "Yes - Pathology and Imaging are concordant"
If none of those conditions are met then "No - Pathology and Imaging are discordant"
J="Benign/Negative", and G>04, "Yes - Pathology and Imaging are concordant"
Hi! All the necessary information is in the article above. Also take a look at this guide: Excel Nested IF statements - examples, best practices and alternatives. For example:
=IF(OR(G1=0,G1="4B",J1="Indeterminant/Atypical"),"Needs Review", IF(AND(J1="Benign/Negative", G1>4),"Yes - Pathology and Imaging are concordant",""))