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)
4494 comments
Hello, Ben:
I think what you're looking for is a nested IF statement.
It should look like this:
=IF(B25<=0.5,"Unfit",IF(B25<=0.65,"Level 1",IF(B25=0.85,"Level 3"))))
Hi Doug - I will try it - thank you!!!!!!!!!!!!!
Hi Guys,
Sorry - this might be a repeat of my previous question.
I am looking at the "IF" "AND" combination:
The test score is in B5
The conclusion is in B6 (Unfit / Level 1 / Level 2 / Level 3)
IF the person score below 0.5 = Unfit
IF the person score between 0.5 and 0.65 = Level 1
IF the person score between 0.65 and 0.85 = Level 2
IF the person score above 0.85 = Level 3
What is the single formula to get this?
Hi Dear,
Thank you so much,
trying to do this formula:
IF cell E2= Married and cell A2*25%<2000 should be 2000 if false should be A2*25 %, AND IF cell E2= Single and cell A2*25%<1800 should be 1800 if false should be A2*25%.
I use this formula for each but I can't combine it in one cell, Could you please help me?
=IF((AND(E2="Single", A2*25%<1800)),1800,A2*25%)
=IF((AND(E2="Married", A2*25%<2000)),2000,A2*25%)
Qty
Slab Amistar Top- (13 Points) 20 40 72 100 150
Chess-(10 Points) 20 24 52 72 100
Bonus points % 0 10% 15% 20% 25%
Can any one please support me for making calculator for of this offer
Column A contains a set of values
Column B contains another set of values.
In column C formula is:=IF(A1>B1,"No","Yes")
Another formula is needed in column D if column C is No , then it must state Yes and visa versa.
Can somebody please help?
Linette:
Wouldn't the formula in D be the same thing as in C?
Formula in D =IF(C1="NO","Yes","No")
Hello,
I have only cell to input. If
GDD, the result would be GD Dimaisip, if EDM, the result would be ED Manlapaz, if LGB, the result would be LG Boletche, and if not GDD, EDM and LGB, the result would be DT Alarcon.
Any help would be much appreciated.
If employee is full time they get benefits, if not they don’t. Use a formula to put “yes” “no” in the column. How to solve this by using IF formula.
If Column A = “Y” and Column B = “5” Pull the detail from column D
Dinesh:
You want to use and IF AND statement. In general they look like this: =IF(AND(Something is True, Something else is True), Value if True, Value if False)
So in your case where the data is in A2, B2 and D2 it looks like: =IF(AND(A2="Y",B2=5),D2,"No Match")
The "No Match" text can say what you want. Just keep it in the double quotes.
HELP ME ON GRADING OF MARKS:
IF GRADE =a, mark is 4, etc.I TRIED =IF(C10=A,"4",IF(C10=B,"3",IF(C10=C,"2","1")))
NOT WORKING.
Makesh:
You were close. The double quotes should be around the text, not the number. The formula looks like this:
=IF(E38="A",4,IF(E38="B",3,IF(E38="C",2,1)))
If you wanted to enter text for the grade it would be:
=IF(E38="A","Four",IF(E38="B","Three",IF(E38="C","Two","One")))
My problem is..
I have a machine in comany, I want diaplay it's maintenance due due dates as follows.. More than or equals to 31 days- Ok, less than or equals to 30 days-Not Ok, less than or equals to 5- Need attention, less than or equals to 0- Emergency.
Pls help me to write formula for above condition
Herald:
I believe the formula you want looks like this:
=IFERROR(IF(N14>=31,"OK",IF(N14<=30,"NOT OK",IF(N14=5,"Needs Attention,IF(N14<=0,""Emergency"))),"More Than 31 Days")
The date is in N14, but you can change that address to suit your situation.
The IFERROR is there to catch dates greater than 31 days.
If you don't need it you can remove it and the parentheses that contain it. I was using =DATEDIF(StartDate,TODAY(),"D") to give me some dates to work with in days. This generated some negative dates. For example, if one of the StartDates was after TODAY() it would throw a #NUM error.
Please help to make a formula in the following condition:
First Condition
=IF(AND(AR25>0,AR25365,AR25770,AR251070,AR251135,AR251435,AR251500,AR25<=1800),"Milkable")))))))
2nd Condition,
as per first condition if "Dryer" then hoe many days it was gives milk last one year,
or if "milkable" how many days it was gived us milk last one year.
please help to make a formula as per second condition.
Tnx
Jafor
/Users/coreygoff/Desktop/EXCEL.gif
=IF(T53>=15,IF(P53="R",T53,IF(T53>=25,IF(P53="P",T53))),"")
IM RECEIVING A "FALSE" WHEN I WANT IT BLANK, LIKE IN ROW 51 & 52...
Hi there,
I am trying to create some conditions within a cell and I am really struggling with a formula. I have 30,000 rows with website information. I am trying to categorise each site into Entertainment, News, Sport and use key words to populate a cell, .
So I want to create a rule that says something like this:
If Cell B3 contains Sport cell D3 = sport OR if cell B3 contains News D3 = News OR If cell B3 contains Entertainment Cell D3 = Entertainment. How do I turn this into an excel formula?
the problem is that cell B3 contains other words, not just sport or news...so what I want to do is apply it to the string and not an exact match. Does this make sense, is this even possible?
Hi I am trying to combine several IF formulas into one - the formula used to calculate what I want depends on the value of the variable. For example:
If BW12 is <=1 then the formula is 1-BW12^2/2
If 1<BW121.7 then the answer is 0.05
I know what I have currently:
=IF(BW12<=1,(1-(POWER(BW12,2)/2)),IF(1<BW121.7,0.05)))
is wrong so I would appreciate any assistance,
Thanks
Lily:
What is the meaning of this piece, "1<BW121.7"?
Hi. When there are no amounts entered on a sheet, what formula do I enter so this, #DIV/0! is recognised as a 0 amount as my calculations will not work with this symbol present?
Thanks
Jim:
It depends on what version of Excel you're using but,
after Excel 2007 you can use =IFERROR(A2/A3,0). This tells Excel if your formula evaluates to #DIV/0!, then return 0, otherwise return the result of the formula.
Help with coming up with a formula!!
COUNTIFS('EMPLOYEE Detail'!E:E,"TWIN LAKES MEDICAL FOUNDATION INC",'EMPLOYEE Detail'!H:H,"EMPLOYEE")+COUNTIFS('EMPLOYEE Detail'!H:H,"EMPLOYEE",'EMPLOYEE Detail'!G:G)
It keeps giving me an error. I am trying to get the twin lakes medical foundation and the employee information which are from two different columns to give me a $total from another column.
I am working off of 3 columns to try to get the data I need, I was able to get it to count how many employees have selected the product I am just stuck on trying to get it to calculate as to which company the employee is with to get me the $ they have elected.
Column E Column G Column H
Stoutco 14.18 Employee
Cedar 22.54 Employee + Spouse
Stoutco 22.54 Employee + Spouse
Cedar 40.14. Employee + Family
Cedar 22.54 Employee + Spouse
I came up with this formula to get me the total for each COUNTIFS(E:E,"STOUTCO",H:H,"EMPLOYEE) to get me a total of 1.
I just need help with coming up with Stoutco, Employee = Column G for the whole column to pull how many people elected the products.
Please and Thank you,
Stephie
Uses the if
=If(and(A1time(12,30,00)), 60 , If(and(A1time(18,00,00)),30,0). Just cudnt fiq out bracket. How do I add another condition so that is display the number 30 also? But the time is A112,45,00?
Hi Svetlana,
I need a formula that would do this,
If colum A has the same name of column B and the value at Column C is bigger than zero, then multiply the number on column C by the number on Column D. Can I get that?
Thanks
Bob
Very helpful stuff! Thanks much!
I am trying to build a formula for the following:
If Cell C7 has a "Y" in it and Cell E7 is blank then Cell G7 needs to say "To Print" otherwise leave no wording in G7.
I know the experts will scoff at the simplicity of this but I can't fathom it, all help appreciated!
I'm not a regular Excel user so I apologize in advance.
I need to create a column with a IF formula that indicates whether a "yes" is populating any of the 4 previous cells in each row.
need help too.
if after computing the grades in a column1 and on column2 the cell will automatically comment on the rate of poor, average, and excellent (depending on the grade bracket). how to create formula to put the grade on 2nd sheet with columns of poor, average, and excellent. but on the cell, the grade on the 1st sheet will automatically be transferred on the correct column range.
i.e.
No. / poor / average / excellent
P1 75
P2 85
P3 84
P4 90
thank you
How to apply IF condition if the days fall in this category
>45 Days
31-45 Days
16-30 Days
0-15 days
Is this correct :=IF(H9<=15,"0-15 Days",IF(H93045 Days")))
Manish:
Can you make your question clearer?
What does "IF(H93045 Days") mean?
Can I use this for sorting out carry out in result computation? If not help me out please
=IF(AW9<40,"TSL 102",IF(AR9<40,"FOT 213",IF(AM9<40,"FOT 212",IF(AH9<40,"FOT211",IF(AC9<40,"CME 122",IF(X9<40,"WPT 216",IF(S9<40,"WPT 214",IF(N9<40,"AGT 231",IF(I9<40,"GNS 201",IF(D9<40,"FOT 214",))))))))))
Fadele:
I don't believe the nested IF statement you included in your post will provide anything useful.
Try breaking it down to smaller pieces that help answer a question.
Put the IF statements in separate cells to check the logical value in each cell.
Check here on AbleBits and read the articles about IF and nested IF statements and I believe you'll see how the IF statement should be written.
If March, April , May Sale is 0 & June Sale is 1 then True Or False
if((and(F8="HOME LOAN",J8>0.80%)),"APPLICABLE","NOT APPLICABLE"),if((and(F8="LAP",J8>1.10%)),"APPLICABLE","NOT APPLICABLE"),if((and(F8="LRD",J8>0.50%)),"APPLICABLE","NOT APPLICABLE")
Result: #VALUE#
Can any one Help me to fix the problem in the Above formula
Hi. I have 3 columns. a credit column, a debit column, and a total. if there is a value in the debit column, i want it to deduct from the total. if there is a value in the credit column, i want it to add to the total. there con only be a debit or credit for each record.
James:
If you have the Excel version that allows you to create tables, consider creating a Table for this data. The Table would have six columns. They would be from left to right:
Item, Credit, Running Credit, Debit, Running Debit, Running Total.
Where the first data cell is B6 The Running Credit would have =SUM($B$6:$B6) this would create a running total of the credit.
The Running Debit would have $D$6:$D6 in its top cell. This would create a total for the running debit.
The Running Total would have in its top cell =SUM($B$6:$B6-$D$6:$D6) which would create the running total.
At the bottom of each column in a Table you can quickly create a sum or various other stats.
You can insert a row in the Table to include a new item and the totals will be updated as soon as you enter the value.
You can sort the data quickly and easily while maintaining the relative relationships.
You can also format the Table with nice looking formats.
AbleBits has an article that explains Tables and their advantages.
Not sure if this is the right place but I have the following two problems and don't know if there is a solution so please help
Problem 1
In cell A1, I enter the gender, B1 the age and C1 the score and according to this information, D1 by means of a formula must indicate Pass or Fail. But the pass mark differs according to gender and age. For example Female younger than 35 must achieve 80% to pass, Male younger than 35 must achieve 90% to pass. Female older than 35 must achieve 60% to pass and Male older than 35 must achieve 70% pass. What can I do that when I change the gender or age then the applicable formula (pass mark) is automatically selected in cell D1
Problem 2
I have three cells with scores (A1=60, B1=70, C1=50) I want the highest score of the three to be indicated as a value in cell D1 and E1, by means of a formula, indicates the text Pass or Fail or Did not Write (60 being the pass mark). Yes I did use the max function for D1 and IF function for E1 but here is the challenge. If I enter a text like "DID NOT WRITE" in anyone of cell A1,B1 or C1 then the highest score must still be indicated in cell D1. And if the text "DID NOT WRITE" is entered in all three cells then I want cell D1 to indicate a text like FALSE or something so that the formula in E1 can indicate "DID NOT WRITE"
I will really appreciate some help in this regard if possible.
Thank you in advance
Hello.
I am hoping for some help building a formula.
I want to create a sequence of numbers that increases as it goes down the column. For Example A1 = 1. A2 = 2. However, I am hoping to create a condition that if the G2 contains a value that is fully encompassed within G1 then the A column repeats the value from above. For Example if G1 = zyzy121 and G2= zyzy, then I would A2 to use the same number as A1. I would then like A1=1, A2=1, and A3=2
Let me know if you have any ideas. Thanks
Hello, i am seeking a better way
currently i have a data list (down)with
name,acquisitionday,units, across the top of my excel file i have a data list (across) with name,rateday,rate.
my current formula =if(name across matches name down,(if((acquisition date<rateday,round(units*rate,2),0)),)
continuation,
seeking to have 1 tab with down data, and 1 tab with across data
on the down data tab, seeking to insert a nested if function to calculate results
down tab
abc,9/19/17,100 result to be 25, as the last rateday is after the acquisitionday
across tab (rates and dates can be different)
abc, 6/1/17, 0.10
abc, 8/1/17, 0.15
abc, 10/1/17, 0.18
My Quistion is that
like column-1 is 20-30 and column-2 is 5 then remark is 400 + column-1, 31-40 and Column 2 is 7 then remark is 700 +column -1,41-50 and column 2 is 10 then remark is 10000 + column -1, 51-60 and column -2 is 12 then remark is 12000
Hi,
I am looking to create a formula that looks in different cells (for example: A1, B1, C1, D1) and if these cells have "no" then it skips until it finds something different than "no" and put in the cell where the formula was written whatever text was in that cell.
thanks
Oren:
I believe this will produce the result you're after.
Where the list is structured as a vertical list in column A cells A1:A100, enter this in B1:
=IF(A1="No","",A1) and copy it down the B column.
This formula says, If A1 has "No" in it then leave B1 empty, otherwise enter what is in A1.
You can type in whatever text you need by typing the text between the quotes holding No or the empty quotes.
Hi,
I am looking to create a formula that looks in different cells (for example: A1, B1, C1, D1) and if these cells have "no" then it skips until it finds something different than "no" and put in the cell where the formula was written whatever text was in that cell.
For example cell A1, B1, & D1 have "no"and cell C1 has "apply" the result would be apply
thanks
hi,
i am looking for a formula for wages calculation with below condition,
--if "X" person works same day in Same place in different session or one session(AM, PM, and Evening session), he will be paid only GBP 3.00 amount(his working hour in each session are mentioned in different rows).
--if he works in different place in same date he will be paid GBP 3.00 for each place.
Thanks Doug
Got this one to work
=IF(IF(D7=0,0,IF(D7<=59,1,2))=2,IF(H7=1,2+5,2),IF(IF(D7=0,0,IF(D7<=59,1,2))=1,IF(H7=1,1+2,1),0))
It there anyway I can send the formula other than in this thread ?
Again the formula above is incomplete, its only showing 5 IF's but I have 6, it keeps dropping IF(D759,"2"
This is both together but it's only returning the clean sheet and not the points for minutes played
=IF(D7=0,H7*D7,IF(D7>=60,H7*5,IF(D7<=59,H7*2,IF(D7=0,"0",IF(D759,"2"))))))
Hi Doug
This works for the clean sheet
=IF(D7=0,H7*D7,IF(D7>=60,H7*5,IF(D7<=59,H7*2)))
This works for the mins played
=IF(D7=0,"0",IF(D759,"2")))
Just can't get them to work together.
Hi Doug
I've tried it again but it's still not returning what I need.
What I'm trying to do is if someone plays less than 60 mins they get 1 point for playing and 2 for a clean sheet if they play 60 or more they get 2 for playing and 4 for a clean sheet and finally if their playing time shows 0 then 0 for playing and 0 for clean sheet.
=IF(D8=0,"0",IF(D859,"2")))
This works for the minutes played but I'm trying to get the clean sheet in the same formula.
Thanks for all your help.
This is making me greyer by the minute..
Stewart
PS my clean sheet is in column 8, so I enter a 1 if they play
Again its dropped the middle, not sure what is up but if(d7<=59,"1" is missing again
Stewart:
Not sure what is happening on your Excel sheet, but the formula I sent works fine on mine. As to the other issues you're seeing, I don't know how to help with that.
Nope its reverted back to the same one cutting out the middle which is ,IF(D7=60,"2"))) that follows =if(d7=0."0",
Again, I'm not sure what is happening but the copied formula is fine my end but soon as it's posted it not the one I copied..
"=IF(D7=0,"0",IF(D7=60,"2")))" tried it with "" to see if it sticks"
Sorry still attached the wrong one a second time
=IF(D7=0,"0",IF(D7=60,"2")))
Hi
I can't get this to return.
=IF(D7=0,"0",IF(D7=60,"2",(IF(D7>=60,H7*5,IF(D7<=59,H7*2))))))
Thanks
Hello Stewart:
Try this:
=IF(D7=0,"0",IF(D7=60,"2",(IF(D7>=60,H7*5,IF(D7<=59,H7*2)))))
Thanks Doug
But now my 60>= doesn't return my 2.
This is what I am trying to do.
if sheet >= 60mins then 2 points, if less than 60 mins 1 point, if 0 mins then 0 points + if clean sheet =1 then if cell with minutes >= 60 mins then 5 points, if less than 60 mins 2 points and if 0 mins 0 points.
Hope this helps, I'm really struggling to get the 3 if's + another 3 if's to work together.
On there own they work
This is the clean sheet one
=IF(D7=0,0,IF(D7>=60,H7*5,IF(D7<=60,H7*2)))
This is the mins played one
=IF(D7=0,"0",IF(D7=60,"2")))
I need them to work together if possible
Sorry minute one is this
=IF(D7=0,"0",IF(D7=60,"2")))
hello
thanks for your help
how to do this formula ???
if one column is bad d5 =0
else if another column is good d5 =10
Hi Ali,
if(d5=0,” Bad”,”Good”)
I keep getting a #N/A when the cells that use this formula are blank. I want the formula cell to show blank when there is no information to complete the calculation. Here is the formula that is in the cell.
=IFS(W6=1,T6/AD8,W6=2,T6/AD9,W6=3,T6/AD10,W6=4,T6/AD11,W6=5,T6/AD12,W6=6,T6/AD13,W6=7,T6/AD14,W6=8,T6/AD15,W6=9,T6/AD16,W6=10,T6/AD17)
I have tried multiple ways of using "" but I can't figure out how to not get either the #N/A or the FALSE to show in the formula cell.
Callie:
I found this solution on the web. The solution was for a question like your's concerning the blank cell issue.
You should replace the HLOOKUP parts with your IFS pieces. As the above article explains you can also trap that #N/A error with IFNA or ISNA.
As an aside, the number of IF variables in your formula makes your situation a very good candidate for another approach. There are way too many IF then parts to keep up with.
Anyway, I believe you're using Excel 2007 or newer, but I included the first part for the folks on here who are using older versions.
Excel 2003 or older:
=IF(ISERROR(HLOOKUP($D$6,Benefits,2,FALSE)),"",HLOOKUP($D$6,Benefits,2,FALSE))
Excel 2007 or newer:
=IFERROR(HLOOKUP($D$6,Benefits,2,FALSE),"")
IF CELL ADDRESS B5
I WANT TO APPLY IF FORMULA FOR TWO/THREE CONDITIONS FOR THE SAME CELL ADDRESS
EXAMPLE
B5>500000,"B5-500000",IF(B5>=1000000,"1000000", "0")
KINDLY GUIDE ME WHAT KIND OF FORMULA SHOULD BE IMPLEMENT IN SUCH CONDITIONS AND HOW?
Shivaji:
I think what you're looking for is:
=IF(B5>500000,B5-500000,IF(B5>=1000000,1000000, 0))
If the numbers are actually text then you need to enclose them in quotes, otherwise you leave them as shown here.
You have applied a wrong formula, please find the correct formula given below-
=IF(B5>500000,B5-500000,IF(B5>=1000000,1000000,0)
I need to calculate a policy amount that depending on how it’s being paid, what amount of the policy we would get for that year. I’ve started what I think it the right formula but I’m getting an error and I’m not sure where I’m wrong. I’ve broken out the formula for each option and hope that it will work when all put together. I know that they all work individually except for Quarterly. I can’t seem to figure that one out. Any help you can give me would be great!
One Time = Total Annual Policy
Annually = Total Annual Policy
Semi-Annually = If Jan-Jun then Total Annual Policy; If Jul-Dec then ½ of Annual Policy
Quarterly = If Jan-Mar then Total Annual Policy; If Apr-Jun then ¾ of Annual Policy; If Jul-Sept then ½ Annual Policy; If Oct-Dec then ¼ Annual Policy
Monthly = Total Annual Policy divided by 12 times months left in year (including month it was issued). (Example: Policy issued in May, Policy amount $400, (400/12)*(13-5) = 266.64)
=IF(E7="One Time",J7),
IF(E7="Annually",J7),
IF(AND(E7="Semi Annually",Q7<=6),J7,J7/2),
IF(E7="Quarterly",IF(Q7<=3,J7),IF(Q7<=6,(J7*0.75)),IF(Q7<=9,(J7*0.5)),IF(Q7<=12,(J7*0.25))),
IF(E7="Monthly",((13-Q7)*(J7/12)))
Hello
I've read your guidance and created the following formula to achieve the following. Unfortunately it doesn't work. Any advice would be greatly appreciated.
If L3 is empty return 'No Appt',
If the date is L3 is in the future return 'Future Appt'
and If both R3 is empty AND the date in L3 is in the past return 'No Report'
=IF($L3="","No Appt", IF(L3>TODAY(),"Future Appt", IF(AND($R3="", L3<TODAY(),"No Report"))))
Jonathan:
Start building this IF And Statement from the inside and see where it breaks.
Begin by putting IF($R3="", L3<TODAY(),"No Report", "T") in a cell. See if that will calculate.
Then continue to build the formula piece by piece and you'll see where it breaks. When it does this may help you to determine what your goal is and how to accomplish it in another way.
I'm looking to create a formula to dumb down some accounting entries. I have 4 variables, Taxes Payable, Taxes Receivable, Increasing, Decreasing
If the balance sheet shows Taxes Payable AND the provision for taxes is increasing, then Debit 702 & Credit 202
If the balance sheet shows Taxes Payable AND the provision for taxes is decreasing, then Debit 202 & Credit 702
If the balance sheet shows Taxes Receivable AND the provision for taxes is increasing, then Debit 702 & Credit 124
If the balance sheet shows Taxes Receivable AND the provision for taxes is decreasing, then Debit 124 & Credit 702
I'm not sure exactly how to do this.
Thanks!