IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading
by Svetlana Cheusheva, updated on
IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading
Comments page 116. Total comments: 4830
hi, i need help in creating a logic formula
lets say i have 2 different formula, but both formula give the same value. and with the value i have obtain i needed to to the other formula. the question is, how can i use the value if one of the formula is blank?
Hi All,
Can anyone help me with this one:
If A1 says Vendor 1, A2 Vendor 2 so on and D1 says Vendor 1 then the B1 is ZZ and E1 is AA.
I need a formula that will match cells A1 and D1 then, if they are match/equal will proceed in matching the B1 and E1 cells.
Thanks
HI to all
can any one hlep meon this:
I have anumber in cel A1 250, I want to put in cel B1
If the number in A1 more than 180, add -360 to A1
Please send me a reply on my e-mail many thanks in advance
Please help!
I need a formula that looks at a cell.EG A1
If A1 says “card” or “mail order” then it needs to take the value off another cell (B1) and times it by 0.03. If however A1 says “defaults” it needs to take the value in B1 and times it by 0.015.
This is what i have so far: it doesn’t work :(
=IF(A11=”Card”,b1*0.03,IF(A1=”Mail Order,B1*0.03,IF(A1=””Defaults”,B1*0.015))
Hi Kattrina,
You just have 1 incorrect cell reference (A11 instead of A1) and a missing closing parentheses at the end of the formula. Once you fix this, the formula works just fine:
=IF(A1="Card",B1*0.03,IF(A1="Mail Order",B1*0.03,IF(A1="Defaults",B1*0.015)))
To make the formula a bit more compact, you can add the OR statement, like this:
=IF(OR(A1="Card", A1="Mail Order"), B1*0.03, IF(A1="Defaults",B1*0.015))
Hi, im looking for a formula that will help me with the below example:
A1=600
A2=8
A3=122
quantity over 50 for A3 to be multiuplied by A2, plus A1.
if A3 is 50 or under, to display A1 only.
please help.
thanks, Rob
Hi Rob,
You can use the following formula:
=IF(A3>50, A3*A2+A1, A1)
Please I want a formula that can count through cells like C2:F2 to check for grade “A1” -“C6” = pass but if there is “D7” - “H” = fail
Hi!
I don't know what you want to calculate, but you may find this guide useful: How to use Excel COUNTIFS and COUNTIF with multiple criteria. If this does not help, explain the problem in detail.
Hello,
I am looking for a formula that will return "rural" if the second character of postal is "0".
N5Z3J2
L5R3S6
L2M4G3
M2M3R1
N0G1L0
So far I have this :
=IF(ISNUMBER(SEARCH("0",M2)), "rural", "city")
Anyone knows how I can target the second digit only?
Thanks,
Hi Daniel,
Try this one:
=IF(MID(M2,2,1)="0", "rural", "city")
A B C D E F G H
Annual Q1 Q2 Q3 Q4 Annual Q1 Q2
1]3.8 1.08 2.06 0.15 1.1 1.1 1.08 0.89
2]3.8 1.08 0.98 -1.91 0.95 1.1 1.081 -0.183
Hello sir/Madam
I have a question in above situation.
1st condition is Annual & Q1 value be always match . but Q2, Q3,& Q4 value never be match.
if Annual & Q1 value mismatch then it should be an error, and if Q2, Q3, &Q4 match then it should be an error.
now i have try [if(A1=A2,"OK","error")]this formula. but this formula work only Column A & B (e:g Annual & Q1)but it does not work Q2,Q3,Q4
So, could you please help me in this matter....
Thanks
hello
I work in two sheet with if function
=IF('Sheet1'!G12="WBGELD";"C";"A")
that function is in second sheet and i like when the cell is empty in first sheet to have empty blank cell and in second sheet
thank you kind regards
Hi Ismaili,
Just add one more IF function to check for a blank cell:
=IF('Sheet1'!G12="";""; IF('Sheet1'!G12="WBGELD";"C";"A"))
Hello,
help me in a formula in when :
for example : a1*b1= in_cell_ c1
-am need if this number in cell c1>2.00 keep the cell c1 the same vale (i mean keep the result a1*b1 without change ).
-and if the number in cell c1<2.00 replace the cell c1 to 2.00 automatically
thank you
Hello Razzouk,
Here's the formula for C1:
=IF(A1*B1>2, A1*B1, 2)
I need a formula that will sum if the value in both cell a & b are greater than 0. If one or both are less than 0 I need the sum to be 0. So if I have a 4 in cell a and a 0 in cell b I need the sum in c to equal 0. If cell a and b are both 4 I need c to equal 8.
Is there a way to do this formula? Thank you
Hi Tegan,
Here you go:
=IF(OR(A1<=0, B1<=0), 0, A1+B1)
I am looking for a formula to show date from today in a specific cell.
So I want to put a date in a1, amount in b1 and I want c1 to always show TODAYS amount. Does that make sense? I provided a visual below.
Example: Amount today
Date Amount 57
9/25/15 57
I tried the following and it did not work
=IF(A1=TODAY(), "B1")
Any ideas would be great.
Hi VIKING,
You were on the right track, just remove the quotes surrounding B1 because they turn a cell reference into a text string:
=IF(A1=TODAY(), B1)
Hello Svetlana,
I want to use the IF logic for the following, but don't know how to set it up:
If B2=I, and H2=>15, I2 should =Yes
If B2=R, and H2=>30, I2 should =Yes
If B2=I, and H2=>45, I2 should =Yes
If any of these are present and H2 is < then I2 should =No
Hopefully, you can help me because this newbie is lost.
All of the logic is based on the Letter in B2, and the time in B3.
Hi Alvin,
Here's the formula for I2 as per your conditions:
=IF(OR(AND(B2="I", H2>=45), AND(B2="R", H2>=30)), "yes", "no")
Svetlana,
Thank you very much.
Alvin
Svetlana,
Now the employee wants it to be time, can you show me how to convert the formula to time?
I cant figure this out.
If G6 equals "HPHe" then I want it to enter the value of P10, but if G6 equals "OFA" then I want it to enter the value of P11
=IF(OR(G6="HPHE",P10,"")=IF(G6="OFA",P11,""))
Hi Nick,
You put it like this:
=IF(G6="HPHE",P10, IF(G6="OFA",P11,""))
Hi
I want to know the formula for subtracting the numbers in three columns with numbers in the third and second columns of the first row are less than the numbers of the third and second columns of the second row.
How can I write the formula for that condition?
=IF(B37="","",IFERROR(COUNTIFS(BASF_GLOBAL!$AI:$AI,"Recommended",BASF_GLOBAL!$D:$D,$B37),""))
Can anyone tell me what B37="," means when B37 is a vell containing text value.
Hi Richa,
"" is an empty string. So your formula reads as follows: when B37 is empty, return nothing (blank cell), otherwise return the result of COUNTIFS. If COUNTIFS returns an error, then also return an empty string (blank cell).
HI
i need to know the formula for
if A1 is US b1 value should be 1000 if A1 value is indiab1 value should be 2000.
The value is fixed
thanks
Hello Prabhu,
Here you go: =IF(A1="US", 1000, IF(A1="indiab1", 2000, ""))
IF(AND(C3=2014,C3=2015),SUMIF(Sales!$B$4:$B$544,C4,Sales!$Q$4:$Q$544)+SUMIF(Sales!$B$4:$B$544,C4,Sales!$AD$4:$AD$544),IF(C3=2014,SUMIF(Sales!$B$4:$B$544,C4,Sales!$Q$4:$Q$544),IF(C3=2015,SUMIF(Sales!$B$4:$B$544,C4,Sales!$AD$4:$AD$5440),"")))
AND I WANT ANOTHER TRUE THING,
SUMIF(Sales!$B$4:$B$544,C4,Sales!$Q$4:$Q$544)+SUMIF(Sales!$B$4:$B$544,C4,Sales!$AD$4:$AD$544)
hello ma'am
i want to know about excel formula for:-
if we mark "x" in d1 cell then shows entry of cell a1,b1,c1 into next sheet, please tell me the formula.
Need to get value
A column = Numbers
B column = Text 1
C column = Text 2
D column = Text 3
Need to get answer if Text 1 is equal to Text 1 then number have to be printed
Hello!!
I need help with this formula : =IF(COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, (Stock3) - 6)
This works perfectly but I need to add "Right Triple Track" into the same formula for entire column Barcode:Barcode
I tried =IF(COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, (Stock3) - 6),(Barcode:Barcode, "Right Triple Track") > 0, (Stock3) -6 but it didn't work.
Hello Martin,
If you want the Barcode column to contain both "Left Triple Track" AND "Right Triple Track" at the same time, then use the following formula:
=IF( AND( COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, COUNTIFS(Barcode:Barcode, "Right Triple Track") > 0) , (Stock3) – 6)
If it is sufficient for the Barcode column to contain either "Left Triple Track" OR "Right Triple Track", then use:
=IF( OR( COUNTIFS(Barcode:Barcode, "Left Triple Track") > 0, COUNTIFS(Barcode:Barcode, "Right Triple Track") > 0) , (Stock3) – 6)
Hi,
Please help me out with the excel formula for Gosi (government tax) calculation.
Gosi - =(Basic+HRA)*Gosi but I want add saudi and non saudi in the excel sheet. So if the column has saudi employee will calculate accordingly and if non saudi as per that.
Eg:-
Saudi Gosi Company Expenses - 12% and Saudi Gosi employee expenses - 10%.
Non Saudi Gosi company expenses - 2%.
And below are details and please show some formula for the same.
Nationality Basic HRA Gosi Comp Expenses Gosi Emp Expenses
Saudi Saudi 10000 5000 12% 10%
Jordan Non Saudi 7000 3000 2% No deduction
Yemen Non Saudi 3000 1000 2% No Deduction
Pakistan Non Saudi 2000 500 2% No Dedection
Thanks Hooping Soonest reply.
Regards.
samsher Hashmi.
Hi - I have an "IF" formula that I want to sum up but it is giving me "0" even when the "IF" formula's have a total.
=IF(D4>1,"1","") this goes from A4 to A 103, I want A3 to be =Sum(A4:A103)
What am I doing wrong? :(
Thanks!
Hi Rebecca,
To add up cells that meet a certain criteria, use the SUMIF function like:
=SUMIF(D4:D103,">1",A4:A103)
I have a table and a list box. I am attempting to write a formula that takes the list box value and changes the table values based on the list box, basically resetting the table.
The table is set up for people to enter values. Then they need to be able to 'clear' the table and start over. We are not able to use macros or vb.
HI,
KINDLY HELP ME IN I HAVE A RANGE OF DATES VARYING FROM 0 TO 60. I NEED TO CATEGORIZE INTO FOLLOEING GROUPS IF DATE RANGE IS BETWEEN 60 TO 30 IT SHOULD RESULT > 30DAYS, 15 TO 30 AS > 15 DAYS, 5 TO 15 AS > 5 DAYS AND 0 TO 5 AS < 5 DAYS..
=IF(OR(I25,"<5 DAYS",I2=30,">30 DAYS"),">DAYS")
Hi KARTHICKEYAN,
You can use a nested IF formula similar to this:
=IF(I2>30, ">30 DAYS", IF(I2>15, ">15 DAYS",IF(I2>5, ">5 DAYS", "< 5 DAYS")))
Hi I am unable to figure this out. I need a formula that says: If any of the cells Barcode:Barcode contain the term "1 In Production", then add the text "In Production" to corresponding [In Production]1" cell.
Many Thanks
Hi Martin,
Try a formula similar to this:
=IF(COUNTIF(A1:A10, "1 In Production")>0, "In Production", "")
Where A1:A10 is the range of cells to check for the term "1 In Production".
Works perfectly!!!!
You saved my life!!
Thank You
Hi,
I have 3 columns (A, B, and C) and I want to make a list of text of column A based on the conditions of column B and C. It is somewhat like "if B = "text" and C = "text" => copy the text in cell column A and paste to the new cell.
How is the formula for that?
Thanks.
Hi Karissa,
You can use a formula similar to this:
=IF(AND(B1="x", C1="y"), A1, "")
hi..
this lesson was more helpful for me to study regarding functions of MS excel.thanks a lot all of you..
I am looking for a formula that will compare 2 cells on a sheet, if they both contain the letter o then the number 25 is to appear in a third cell, if one or both cells are blank, then 0 is to appear. i.e. if cell AE6=o and cell AE20=o then cell AF6 is to say 25. If either cell AE6 or cell AE20 (or both) are blank then cell AF6 is to say 0. Please help, thank you.
Hi Chris,
You can use a nested IF formula like this:
=IF(AND(AE6="o", AE20="o"), 25, IF(OR(AE6="", AE20=""), 0, ""))
Thank you. I am also looking for a formula that will post a symbol if 1 of 2 different cells = a value of 1, and fall within a of a number generated in another cell. Here is the formula that I am trying to use: =IF($E$7=1,IF(AND($L$18>=190,$L$18=160,$L$18<=164),"•")),"")))
Let's say that this formula is in cell A1, and when E7 is "activated" by equaling 1 a random number is generated in cell L18. If the number in that cell falls between or equals 190 and 194, I want a • to appear in cell A1, if not, then blank (unless the following formula is true). In the same cell A1, when D6 is "activated" by equaling 1, a random number is generated in cell L18, and if that number falls between or equals 160 and 164, I want a • to appear in cell A1, if not then blank (unless the other formula is true). Both E7 and D6 will never be "activated" at the same time. My current formula returns a #VALUE report in the cell.
Thanks in advance!
Hello Chris,
Please use:
https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/
please suggest , if i select any month i need date wise in row
If May is selected then date 1 to 31 ,Sept 1 to 30 and for leap year feb
Hi,
I want to set up a formula that calculates if an invoice is "overdue" or "OK".
I have my Invoice Date Raised column, I have a Due Date column and a STATUS column which I currently enter manually. I want a formula that will change a the STATUS field as the Invoice date is hit/over. Does anyone know if this is possible and if so how? Im guessing I need a field to show the current date?
Michelle.
HI
I have a problem where I have to write an if function for "if the last digit in a number is even enter 1000 if not, 0". I can't figure out how to put the "last digit is even" part into the formula to make it work in excel.
Thanks in advance
Hi Peter,
You can use the RIGHT function to get the last digit and ISEVEN to find out whether it's even. For example:
=IF(ISEVEN(RIGHT(A1,1)), 1000, 0)
Hi,
I want to do a if function to a data range (not just one cell). For example, for data ranga A1 to Z1, I key in =if(A1:Z1=2,"1","0"), but of course this is incorrect. Can you help me get the right formula or function for this one?
your help is much appreciated.
Thanks.
Hi,
I have several columns with dates (in ddmmmyy format). I want to determine which date in a row is the most recent and determine how recent it is by comparing it to today's date. How do I do that?
Thanks in advance,
Henry
(6516513) <<>> (6516681321)
this two codes are different but meaning is same. Is this possible? ( if I could teach to excel that this codes are same and if it is possible to create formula which will find (using vlookup or other formula) one of this code give him some colour and same time this formula gonna find second code which is same and gave second ones same colour)??? in case of every other code colour must be different :(
Hi,
can you please help me to pick the start and end date of following excel.
WO CO start End 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 7-Sep 8-Sep 9-Sep 10-Sep
11856 MK1501 30
11857 MK1502 60 60 60 60 30
11858 MK1503 30 60 60 60 60
=IF(F9:F10<0,'1A'!P3:P3+'1A'!F9:F10,MASTER!P3+1)
Please help me for adding two column cell in formula
Thanks
I have cells c6:t6(achieved scores of students) but the obtainable score is on c4:t4. that is, if in cell c6 a student scores 0 but the obtainable score on C4 is 25, meaning they score 0 of 25, D6=3 and D4=3 (meaning 3 out of 3). I want U6 to be the sum of obtainable scores.
Hi,
i have a lot of machines need annual certification, and i neeed to apply if function to let me know
if the date of certification "EXPIRED" or "VALID" or will be expired within one month
Thanks
Abbas
HI,
So i have A1 - D1 as breakdown values for total in E1 and;
have 'Paid' in F1 and the date in G1 and;
have the same amounts as A1 - D1 in H1 - K1 if its paid.
what i want is to automatically have the amounts in A1 - D1 in H1 - K1 if i type in Paid in F1.
Can you tell me a IF formula i can use for this?
Actually I want any formula To I Used in excel To sort out data Like We have 3 Column SIZE DIA and Buyer Code
Buyer Code is F-015 This buyer want Many Sizes different Sizes different dia 41.5 dia is 150X200 29 Dia is 100X200 and 47 Dia is 180X200 So i want any condition i put Buyer code and Dia auto detect its size
hi friends i want a something mentioned below
if G5 between 90 and 95 then Pass other wise Fail
Please help
Thanks
Can your "value_if_true" or "value_if_false" statements in an IF function reference pull text from another cell?
Need for for calculating if age falls within a certain range then to return a set figure. I have generated this but its giving me no joy:
=IF(AND(B6>=18,B6=31,B6=51,B6<=64),3.5)
Hi,
I have 2 columns with dates in them.
A=Current Due Date
B=Reschedule To date
How can I do a logical test to show in a 3rd column all the reschedule dates are more than a week apart from the current due date?
Thanks,
Hello,
I want to create a formula , for to look in coloumn B2:B100, in cell text contain is "Raj" then print "Raj availabel" in A1 cell
hi,
I have data tabke
date time open high low close
and I want to convert the data for a day/days into time ranges so that I can chck how it moved with each day as a unique day.as the O H L C will chane with in the day only as er time change requested
If I wat to pull the 1st open for a time range, how do I pull the 1st open even though it is not the start time In my report sheet.
it may so happen that the data may start coming only 2-3 seconds later though say at 9:00:03 where as the start time in my query would by 9:00:00
HI,
HOW WOULD I WRITE THE FORMULA TO GET THE FOLLOWING
IF a0,IF(A3="INDIA",6,IF(A3="NEPAL",10,IF(A3="SRILANKA",15,))))),"")
LEADTIME
INDIA = 8 DAYS
NEPAL = 10 DAYS
SRILANKA = 15 DAYS
Hi,
How would I write the formula to get the following:
If A10.99999,A1=0.99999 otherwise = A1.
I want to Winsorise it to be between 0.00001 and 0.99999
Thank you for your help!
Nazi
I want to create a formula. I have 5 types of value like 4.17, 4.33, 4.50, 4.67, and 4.83 I want if 4.17 comes in a cell the dependent cell will show 4 min 10 sec. For 4.33 it will show 4 min 20 sec, 4.50 show 4 min 30 sec, 4.67 show 4 min 40 sec, 4.83 show 4 min 50 sec and 4.00 show 4 min. How can I do it in excel?
the value before decimal is min like 6.17 will represent 6min 10 sec
I have an excel question. I've got a scenario where I'm trying to write and if/or function. Here's what I have so far: =IF(OR(C43=0,C44=0),0,IF(OR(C43120,C44>4),0.15,0.1))). If C43 is between 1-60 or C44 =1, then the discount is 5%. If C43 is between 61-120 or C44 is between 2-4, then the discount is 10%. If C43 is greater than 120 or C44 is greater than 4, then the discount is 15%. Can you help me write this formula? Thank you!