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)
4506 comments
dear all,
how to combine these 3 condition into one IF Formula:
=IF(OR(C4="PINE"),IF(E4>=24,1200,IF(E4>=20,1100,IF(E4>=18,1000))))
=IF(OR(C4="TEAKWOOD"),IF(E4>=24,2000,IF(E4>=20,1800,IF(E4>=18,1600))))
=IF(OR(C4="FIR"),IF(E4>=24,2500,IF(E4>=20,2200,IF(E4>=18,2000))))
I want to check mutual condition in between two columns ex (a1=1,b1=2)=(a2=2,b2=1) shows error but not using duplicate condition
Hello Pratap,
To check multiple conditions, embed the AND function within your IF formula. For example:
=IF(AND(a1=1,b1=2), value_if_true, value_if_false)
date name scheme amount 12/2/2016
12/1/2016 zen general 100
12/2/2016 john special 200
12/3/2016 merry normal 300
zen if(A5:A7=$F$4,sumifs(B5:B7,"zen",C5:C7,"general",D5:D7
i mean if date row are same like 2016/12/2 if name is zen and if sheme is general then som amt, is it possible?
I have two conditions before performing nested IFs. The nested IFs, if true will perform a calculation and if False will yield Zero as a result.
is that even possible?
Hi Sherwin,
I believe it's possible and you can express those conditions using the AND function. To be able to suggest a formula I need to know the details of your task.
hi, any one who feels like helping.i need i think an if-then statement that willlook at 5 cells (numbers between o-8) and assign a weighting (a Fibonaci number) and then (sum the result).the zero and 8 can be the same low value and the one will be the high value.
would appreciate the help i am an old fart (79) and have always been lousy at maths.
Regards terry turner.
Hi Admin
I have 2 types of timber with 3 size each and each size different price:
a)Pine, 24cm and above sell at $1200,20cm to 23cm sell at $1100,16cm to 19cm sell at $1000
b)Teakwood, 24cm and above sell at $1600,20cm to 23cm sell at $1500,16cm to 19cm sell at $1400
If A1 consist Pine , B1 consist 23cm and the result should $1500, how to write the IF Function at C1.
Thanks for your help.
Hi Admin
I am trying to calculate with if function but does not work effectively. I have four division with multiple ranges and each division has separate tax rate. How can insert formula so that I can does effectively.
For Example
salary rage lies between 300,000.00 to 6,000,000.00 annually. There is tax variation
first 1-400,000 with 1%,
next 400,001 to 500,000 with 15%,
next 500,001 to 2,000,000 with 25% and
next 2,000,0001 above 35%
HI Team,
Kindly help any on the below example.
FU=35 in the cell A1, the number is changed based on my requirement, How do use formula as text & digits' in the same cell.
I need to be able to pull in a row of data to another worksheet if one of the cells is equal to a certain value. For example, I have a physician name, patient name, account number and total charges in a row of 4 cells. If I'm looking for a specific physician's patients, is there an if/then formula I can use to find all of Dr. Ortho's patients and then copy the entire row for each to another worksheet? Thanks in advance for your help!
Dear Team
If cell A1 is recorded as "Error", cell B1 should reflect as "1" and if it is "NO ERROR", then in B1 it should reflect as "0".Can you please assist me with this formula.
Regards
Prakash
I'm trying to make a formula that will enter an amount if the correct name is found. The formula needs to check that "erik" is in the row. If "erik" is listed, then I want it to take E12(my profit #) and multiply is by .1 if over 10000 and multiply it by .15 if under 10000. If another name is listed, just input 0. This is what I've tried so far...=if(and(B12>=10000,H12="erik"),E12*0.1,E12*0.15)
Thank you!
Please make a formula for this one
if M1 = 75,000
M2 = 100,000
S = 50,000
S1 = 75,000
M = 50,000
Please help me find a formulaa for this:
If G>=8 1
If 3=<G<8 0.5
If 0<G=8,"1",IF(8>G>=3,"0.5",IF(3>G>0,"0")))
But it doesn't work.the output is "1" or "0.5", not "0" ???
Please help to find the correct one?
hello,I m a bit stuck off.Any one pls. help
I have to get formulate a cell,if the condition is A1-B1>2,and A1-B1<-2, the cell will display like "INVESTIGATE" as for example
=IF(OR(X285-AJ285)2,"INVESTIGATE") pls. correct the formula.
Thanks a lot Svetlana.
Hi, I'm a little stuck, I have a table of many rows and my column E only holds 3 values (Active, Overdue and Non-Compliant) but I am trying to create an IF statement in the adjacent column F to Tell me the following:
=IF(e2=“Active”,”Compliant”,if(e2=“Survey Date Soon”,”Compliant”,if(e2=“Overdue”,”Non-Compliant”)))
But for some reason, it is returning the NAME? error. Is it possible to have 3 arguements within the same column?
Hello, i am trying to write an and or if statement along these lines: If a rep has YTD PTQ >=100% AND HAS SALES >= $3000000 OR HAS YTD GROWTH % >=5%
Can you please show me how to write out this formula in Excel?
Thanks
Hi, I have read your problem & seen some resolution given by other also,
I'm giving you simple formula for your above condition:--
Important:- Always start If formula from bigger number if there is number in the coloum.
Hi I have one doubt. I need formula for the below condition.
Quetion:-
If the Cell value is in between 1-5 then "1-5 days".
If the Cell value is in between 6-10 then "6-10 days".
If the Cell value is in between 11-20 then "11-20 days".
If the Cell value is in between 21-30 then "21-30 days".
If the Cell value is greater than 30 than "greater than 30"
Resolution:-
=if(A2>30,"Greater Than 30",if(a2>20,"21-30 Days",if(a2>10,"11-20 days",if(a2>5,"6-10 Days","1-5 Days"))))
(Suppose to your values are in calumn A2)
Hello , can anyone help me to calculate odds in football ?
I want to calculate the possibility of a team to score 1 , 2 , or 3 goals in a match when i know (1X2 and over under)?
For example i have Milan - Inter with odds (1)2.55 - (x)3.35-(2)2.95
Over (2.5) 1.85 - Under (2.5) 1.95
Hi,
I have some survey data, which has multiple choice answers like, Yes , NO, Some of the time and Most of the time.. I want ro assign scores to these text.
example Yes=1 , No=0, Some of the work=0.3 and Most of the work=0.6.
Can any one pls help me with this ?
I M USING BY COPY YOUR THIS FORMULA ITS VERY HELP FULL THANKS
IF(AND(A7>=1,A7=41,A7=61,A7=81,A740
41-60
61-80
<80
we are try to make a excel sheet to calculate our fee chart
example : if in A1 41 to 110 it will be $30 in b1 if its 111 to 160 it will be 50...
Refunds from 0 to $40 No processing fee
$41 to $110 $30
$111 t0 $160 $50
$161 to $250 $70
$251 l0 $350 $95
$351 t0 450 $155
$451 to $750 $200
$751 to $1000 $300
$1001 t0 $1500 $395
$1501 to $3000 $480
$3000 and above 17.5%
HI,
In cell b4 "OHTOA-Recycling Container Supply" is the content. if "-" found from first letter onwards, then I want the content like as follows
"OHTOA" in B5 and "Recycling Container Supply" in B6.
Any formula available in excel.
Can anyone help on this.
Hi all,
I need some help on a complicated string. I need multiple if's because I want to make a string that will reflect a person's name depending on 2 true functions.
So far for a single function it works:
=IF(AND(I2="ABC",J2="First"),"James")
but if I want to add a continuation of
=IF(AND(I2="ABC",J2="First"),"James"),IF(AND(I2="ABC",J2="Second"),"Max))
This does not work.
Basically I want this cell to reflect:
IF I2=ABC and J2=First, James.
IF I2=ABC and J2=Second, Max
Also please advise on a continuation after if I need to add more strings.
For instance:
IF I2=ABC and J2=First, James.
IF I2=ABC and J2=Second, Max
IF I2=CAB and J2=First, Brian
IF I2=CAB and J2=Second, Alex
IF I2=ACB and J2=First, Charles
IF I2=ACB and J2=Second, Rango
Thank you in advance!
Hi Svetlana,
I wrote this formula, however, and second part of the IFERROR seems not working in this IF statement, but it works separately. Could you please help to look into it and help me with it? Thanks and I'm really look forward to hearing from you :)
=IF(INDEX($A$2:$E$8,MATCH(1,($A$2:$A$8=$A17)*($E$2:$E$8=G$16),0),5)>0,"Y",IFERROR(MATCH(1,($A$2:$A$8=$A17)*($E$2:$E$8=G$16),0), "N"))
I can get "Y" when the MATCH part is working, I can get "N" when I'm using the IFERROR separately, but I couldn't get "N" when I put IFERROR into the IF...
Hi,
01-OCT 02-OCT 03-OCT 04-OCT.....31-OCT OFF ABSENT
P O P O ..... P 02,04
A P A P ..... A 01,03,31
please paste in excel sheet.
i want result like above format.
so is it possible ???
if it's possible by any formula in excel so please suggest me.
i am waiting your reply....
hi, what about a formula which searches the entire spreadsheet for a certain word. when the formula finds the word in the spreadsheet the cell in which the word is located is displayed? This would be a lot easier than an endless line of "if" tests.
For example, if I wanted to have a cell display the number cell in which "dustin" was located and the answer is B5.
Hi,
Can a function have a numerical outcome? For example lof = .5. And also, can there be multiple ones, would it be the =If(or B2="lof", "rot", "UC"...etc.) then(C2="0.5". "1.0","0.6")
Sorry if you've already answered this, or if this doesn't make any sense. Thank you
Hi,
Please help, I need to make this formula shorter because my data are kinda plenty.
Thanks in advance :)
=IF(ISNUMBER(B1),1*A$1,"0")+IF(ISNUMBER(B2),1*A$2,"0")+IF(ISNUMBER(B3),1*A$3,"0") ...
Hello Kim,
Just write the formula for the topmost cell (row 3 in your case), and then drag down the column to copy to other cells:
=IF(ISNUMBER(D3), 1*C3, 0)
Be sure to use relative cell references (without the $ sign), to instruct Excel to adjust the formula for each cell where it is copied.
I tried using the nested loops but its giving an error. Can you help me out?
IF(D8="Urgent",IF(TODAY()-C81,TODAY()-C82,TODAY()-C8<=6),"P.O Dispatch",IF(TODAY()-C86,TODAY()-C88,TODAY()-C812,TODAY()-C8<=18),"COO+CEO Signing","P.O Despatch")))))))
The error being shown is Too many arguments have been used
Thanks
Aslan
Dear,
I need a solution.Below i give a demo. In a column have phone number & have to identify operator name by code.
unique is for all first five number.
For gp = 88017 & for Robi 88018
For Banglalink 88019
880171234567 = GP
880181234567 = Robi
880191234567 = Banglalink
Dear,
I need a solution.Below i give a demo. In a cloum have phone number & have to identify operator name by code.
unique for gp = 88017 & for Robi 88018
880171234567 = GP
880181234567 = Robi
I am having difficulties with my nested if statement. I have tried different variations of If OR, IF AND, and IF AND OR. The problem is I have 4 variables that need to be met to return a yes or no answer. The data is in 3 date columns. The problem is: in order for the answer to be yes column A needs to represent a date in the past, column B needs to represent a date in the past, and column C needs to either represent a Null Date, or future date. All else is NO. Advice please!
Jon
Hmmm, when i post my formula, the website does not populate it how i have it written.
Hi Seth,
Sometimes out blog mangles formulas in comments, sorry for this. In general, you can use the following syntax:
=IF(B19<100, formula 1, formula 2)
The formula tells Excel to calculate formula 1 if B19 is less than 100, formula 2 otherwise.
IF(OR(B19=101),(B19+75)/B20/52800*3600)
Hi Svetlana!
I was wondering if you could help me with creating a formula. I'm trying have the formula us math one way, when the value is less than 100, and then use another formula once the is 100 or greater.
Here is what I have so far but it is not working:
=IF(OR(B19=100),(B19+75)/B20/52800*3600)
Any help would be great!
Thanks!!
PERCENT_RETURNS in Column Z, defined as: Returned Quantity in column U divided by Shipped Quantity in column S, but only if Shipped Quantity is greater than zero. (If Shipped Quantity is zero, the value in Column Z is to be entered as zero also.)
Been struggling with this formula..... please help.
=IF(AND(H2>18000,H2=22001,H2=28000),"125","")
Hope the issue is clear... if H2is between 18000-22000 I want the cell to return 75, if it's between 22001 and 27999 then return 100 and if above or equal to 28000 return 125 and if else Return nothing.
Where am I going wrong... please help
Trying to get the cell to display "yes" or "no" if it's equal to a specific range of cells (all numbers) on another tab.... here's what I got....
=IF(C2=('Formula Data'!C2:C136),"No",(IF(C2=('Formula Data'!C154:C264),"Yes","")))
But it's simply not working.
Hi
Could you please help me with this, I was designing formula in excel for project status. so i have Row 1 2 3 4 5 and column A,B ,C D
at A1 i want to put formula
if "a" in row A2,A3,A4,A5 then true value should be "a" if there is one or more "b" in A2 ,A3 ,A4 ,A5 then excel should take true value as "b", if there is one or more "c" in A2 ,A3 ,A4 ,A5 then excel should take value as "c" if there is one or more "d" in A2,A3,A4,A5 then excel should take value as "d" and if A2,A3,A4,A5 is blank then no value, this is for Cloumn A same is for B,C,D
Thanks a lot in advance for your kind help.
HI,
Can you please help me this regard:
=IF(AND(C2>=1,C210000,C2<=20000,"Service 2","")))
i need help on this situation
i want to write a formula which is
IF(SUM(D6*0.6%)>=40,SUM(D6*0.6%),40) using this if the value less than or equal 100,000
IF(SUM(D6*0.3%)>=40,SUM(D6*0.3%),40) using this is the value is 100001 above
how?
HI, I AM CURRENTLY WRITING A PROGRAM AND CANNOT FOR THE LIFE OF ME FIGURE AN IF FUNCTION OUT.
SITUATION: I WANT TO OFFER A 5% DISCOUNT TO CUSTOMERS WHO PAY IN FULL AHEAD OF SCHEDULE. I HAVE DONE THIS SUCCESSFULLY USING =E65*(-0.05). HOWEVER, I AM MOVING TOWARD AN ESTIMATE FROM AN INVOICE AND WOULD LIKE TO MARK A QUESTION AS YES OR NO AND HAVE THE FUNCTION POPULATE THE 5% OR NOT TO FURTHER GIVE DETAIL TO THE ACTUAL INVOICE. I USED THIS AND I AM HAVING AN ISSUE: =IF(D71=YES, "(E65*(-0.05))").
WHAT AM I MISSING?
=(IF(A3="Sales",(INDEX('Main Invoice'!G2:G4,MATCH(Sheet1!K1,'Main Invoice'!C2:C4,0))))) & IF(A3="Payment",(INDEX(payment!G2:G4,MATCH(Sheet1!K1,payment!C2:C4,0))))
i Put this Formula, its Giving me result, but with the result i can see False is written also.
For Example if Result for Sales is 10,000, i am seeing it 10000False
or Result for the Payment is 5000False
hi. i'm trying to add formulas and conditional highlight to a table?
a column has either dates, not required, obsolete or blank in the cells
in the next column i want:
if date before today = Out of Date. if date is this month = Review this month. if date is next month = Review next month. if future date = up to date, no review necessary. if obsolete 0r Not required (text) = no further action
then in next column, a coloured cell, as a traffic light system (i.e. reviews due this month orange, Out of date = red etc.
i've tried loads of formulas, if, search, iferror, and,or, today, nested, it either works for one, then not others?
Hi
I'm struggling with a formula based on the above information i know its possible but am struggling to adapt.
What i need is the following:
Sum(c1,d1)/b1*2 = in a range from 0.1 - 5, 5.1 - 10, 10.1 - 20, 20.1 >, If the answer is in the first range show 1, second range 2 up to 4.
0.1 - 5 = 1
5.1 - 10 = 2
10.1 - 20 = 3
20.1 > = 4
so i need to do the Sum first and then IF within the Range to show the score.
what do you think?
thanks so much!
Here is two different coloum (a, b).
a b Formula
1 to 10 1
1 to 10 1.5
10 to 20 1
10 to 20 1.5
20 to 30 1
20 to 30 1.5
30 to 40 1
30 to 40 1.5
40 to 50 1.5
40 to 50 1
Condition Bellow mentions.
A B
1 to 10 1 500
1 to 10 1.5 500
10 to 20 1 1200
10 to 20 1.5 1200
20 to 30 1 1500
20 to 30 1.5 1500
30 to 40 1 2000
30 to 40 1.5 2500
40 to 50 1.5 3000
40 to 50 1 3500
Please give me formula i am very sad please help.
thanks & regards.
Rahul
I CANNOT UNDERSTAND 'IF' FORMULA.KINDLY HELP ME
Hi Svetlana and nice to meet you,
If the answer in A1 cell is "Yes", how can I display a particular table B4:E8 that is hidden??
Any help would be really appreciated.
I'm trying to write a formula that will subtract A1 from B1 and if the difference is less than 10, multiply F12 by .25 however if greater than 10 but less than 18 multiply by .50 and if greater than 18 multiply by 1.
Any help would be appreciated.
Hi Kathy,
Try the following formula:
=IF(B1-A1>18, F12*1, IF(B1-A1>10, F12*0.5, F12*0.25))