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 10. Total comments: 2999
I want to use the IF function to test if one cell (say H5) is above zero, then insert a given cell's value (say the numerical value in C5), if the test is false, then insert another cell value (say the numerical value in E5).
=If(H5>0,C5,E5)
Hi,
I have the conditional formula on my J cell that read as follows: =IF(G3="F2F","1", "0"). G3 has a drop down menu to choose from and one of the options is F2F. How do I add all the ones at the end of my J cells?
Thank you,
Elida
Do you want different values for the different drop down values in G3? Otherwise, use the formula below. This will give you a 1 if G3 has either F2F,F2F2, or F2F3 (replace with whichever is on your list)
=IF(OR(G3="F2F",G3="F2F2",G3="F2F3"),1,0)
Hi, I'm trying to write an if/then statement that will represent numerical values. For example, If L48 is greater than L47, then show the value inside L48. If not, show the value inside L47.
I get it to do the yes, no feature, but I'm having trouble getting it to display the value inside of L48.
=IF(L48>L47,L48,L47)
I need to lock one column so the formula is displayed but do not want the rest of the spreadsheet formulas to show. How can you freeze one column display the formula?
You can remove the "=" so it does not act as a formula.
Depending on the city, you will either take a shuttle to/from the airport or rent a car. Insert an IF function that compares to see if Yes or No is located in the Rental Car? Column for a city. If the city contains No, display the value in cell F2. If the city contains Yes, display the value in the Rental Car Total (F4)
Hello,
The formula below should do the job for you:
=IF(E1="Yes", F2, IF(E1="No", F4, ""))
Hey,
Your tutorial is great. I am trying to get this nested formula but somehow its only picking up last logical test values. Can you please check & advise.
=IF(B$4>B7<B$3, "Expiring within 2 Weeks", IF(B$3<B7B$2, "Expired", "Valid")))
Regards
Hello Syed,
Here is the updated formula for you:
=IF(AND(B$4 > B7, B7 < B$3), "Expiring within 2 Weeks", IF(AND(B$3 < B7, B7 > B$2), "Expired", "Valid"))
What is the cell you're trying to find the value of? B7?
I would trying using IF(AND and doing it that way
Instead of =IF(B4>B7B3,B7<B4),"True","False")
oops, for some reason it got cut off.
Instead of =IF(B4>B7B3,B7<B4),"True","False")
Not sure why it's cutting off so I'll try one more time. "=IF(AND(B7>B3,B7<B4),"True","False")"
Plz help.....
If i write a number greater then 100 in a cell mistakenly. I want Excel shows an error in that cell.
For example
I write 23 26 56 100 in cells and write mistakenly 105. So i want to show 105 in error.
I would just highlight the whole column and do a Conditional Formatting, Highlight Cell Rules, Greater Than..., 100. Then if the cell is highlighted in red you'll know it's over 100.
Please show me formula for the below. Thank you!
IF A1 = ABC B1 = 1234; if A1 change to DEF then B1=5678; also if A1 change to GHI then B1=91110
Hello Cindy,
Please try to use the formula below to solve your task:
=IF(A1="ABC", 1234, IF(A1="DEF", 5678, IF(A1="GHI", 91110, 0)))
I Tried several times, but it doesn't work. Could you please check and advise again. Thank you!
Thank you for replying, Cindy.
Most likely it is the delimiter that was causing troubles. Please try this formula instead, it should work:
=IF(A1=”ABC”, 1234, IF(A1=”DEF”, 5678, IF(A1=”GHI”, 91110, 0)))
I've updated the formula in my previous comment as well. Sorry for the confusion.
It works now. Thank you so much!
Glad to hear that, Cindy!
Hi Cindy,
Try using commas instead of semicolons to separate the arguments (that depends on which character is set as the List Separator in your Regional Settings):
=IF(A1="ABC", 123456, IF(A1="DEF", 5678, IF(A1="GHI", 91110, "")))
This will only work on 4 letters at a time. I'm assuming you meant ABC = 123 and not 1234. If not this wont work for you.
=IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))-64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) -64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))-64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))-64))))
Actually I need 6 digits (ABC=123456). So it doesn't work for me! Thanks for trying!
Here is the 6 letter version. To add more numbers you just have to copy everything from IF to the second & and change the number after the A1.
=IF(MID(A1,1,1)="", "", CODE(UPPER(MID(A1,1,1)))-64&""&IF(MID(A1,2,1)="", "", CODE(UPPER(MID(A1,2,1))) -64&""& IF(MID(A1,3,1)="", "", CODE(UPPER(MID(A1,3,1)))-64&""& IF(MID(A1,4,1)="", "", CODE(UPPER(MID(A1,4,1)))-64&""&IF(MID(A1,5,1)="", "", CODE(UPPER(MID(A1,5,1)))-64&""&IF(MID(A1,6,1)="", "", CODE(UPPER(MID(A1,6,1)))-64))))
Good Morning,
I am trying an exercise in Excel, we have a sheet some details, however, in the first column A2, have some digit number in the same column digit will be sometime 7, 8, 10 and some case digit numbers with dots then TWO digits.
I am looking for a formula that can help me to write text in the next column............
Example : =IF(A2=(len)8,"NOBILIA",IF(A2=(len)7,"IMPULS",IF(A2=(len)10,"LINEA DÉCOR"))).
Will you please help me with it.
Sincerely yours
Ikram Siddiqui
=IF(LEN(A4)=7,"IMPULS",IF(LEN(A4)=8,"NOBILIA",IF(LEN(A4)=10,"LINEA DECOR","")))
Hi Svetlana,
I am trying to achieve in the formula to show the following:
If the cell drop down is selected with "CB2" is standalone to show "local", if "CB2" & "CB3" (combined) to show "local & far" and if "CB3" to show "far"
How can I achieve this?
TIA
Hi Tia,
There seems to be a problem with the IF(AND()) function. The comment truncates the formula for some reason.
=IF(A1="CB2","LOCAL",IF(AND(A1="CB2",A1="CB3"),"LOCAL & FAR",IF(A1="CB3","FAR","")))
Can you help me to accurately represent this formula please:
=IF(L4>0612,"High Risk")
Hello Ricardo,
Please try to add quotes so that the formula will look like:
=IF(L4>"0612", "High Risk", "")
Hi Sveetlana Cheusheva
Is it possible that if I enter any text in column"P", "L" should show what is there in "K".
=IF(NOT(ISBLANK(P7)),"0","=K7")
=IF(ISBLANK(P7)),"=K7","0")
Hi Ashwini,
I think you've overthought it :) A simpler formula will work:
=IF(P7<>"", K7, 0)
Can you help me write a formula for the following situation plse.
If A1-A2 is less than zero, then show the negative value. If not, then show zero.
Tks
Hello Jaime,
The following formula should do the trick for you:
=IF(A1-A2<0, A1-A2, 0)
Hi Jaime,
Here you go:
=IF(A1-A2<0, A1-A2, 0)
Can u direct me on the error of my formula? =IF(E22>0)B22+E22
I want it to be cell B22+E22. But ONLY if there is an entry in E22. If E22 hasn't been filled in yet, then I want the formula to be in place but not to SHOW. Much Appreciated!
=IF(E22="","",IF(E22>0,B22+E22))
hi! like to corelate data in 2 sheets of same excel file. e.g. sheet 1 and sheet 2
I need help to create if or
any formula like
=if(sheet1b2=10,"sheet2A3=Sheet1A3","blank")
plz help
=IF(Sheet1!B2=10,Sheet1!A3,"")
Put this formula cell A3 of Sheet2.
write semicolon instead comma
Hi,
I have D9 = 0 to 2000, I would like to write this formula in C10
if the value of D9 is equal or greater than 15, then cell C10 value will be 15, otherwise, the value of cell C10 should equal to the value in D9
=IF(D9>=15,C10=15,C10=D9)
But, it doesn't give what I want. anyone can help?
Thank you
remove the C10=
Hello, I have a spreadsheet with an "if" function to provide different values based on what is selected in a drop-down box. I am trying to then use the result of that box to create a sum with another box. The problem I think is that when I created the "if" statement now that cell is being viewed as text, not a number so it will not add it. Can someone help me? Below is the "if" formula that I am using.
=IF(H3="AS", "10",IF(H3="SNS","10",IF(H3="TNS","0",IF(H3="TSNS","6",))))
Try removing the quotation marks on the value_if_true as this makes the result as text.
=IF(H3="AS", 10,IF(H3="SNS",10,IF(H3="TNS",0,IF(H3="TSNS",6,))))
How would I write an If function that inserts the value 0.08 if the client is located in Washington and the value 0 if the client is located elsewhere.
=IF(H4="WASHINGTON",0.08,0)
hello. how do i code a rule for this: "safe" if the distance is between 9 and 10 and "error" if the distance is less than 9 or greater than 10.
=IF(AND(F13>=9,F13<=10),"SAFE","ERROR")
Replace the cells with where your distance values are.
i want to create a sheet in which, if A1 has value which is divisible by 5 then it should be multiplied by 5 (i.e. 5,10,15,20) in B1 or if A1 has the value which is not divisible by 5 (i.e. 1,2,7,9,11) then it should be multiplied by 4 in B2.
Please share the formula.
=IF(MOD(A9,B$9)=0,A9*5,A9*4)
Hello.
I have been trying to write the following for the last 2 days/
Column A - 5.5
Column B - 6.5
Column C - (If The difference between A and B is bigger than 10% of A, I need >10% to input ESC and <10% to input CLEAR
Hope I’ve made this seem clear and thanks for any help
=IF(SUM(B2-A2)>A2*10%,"ESC","CLEAR")
I need a formula that’s had me stumped for weeks and I know it’s simple ?
I’m trying to find out if a name in column A2 matches or contains a name in the same row columns b2,c2,d2 if yes leave blank if no then x
I have it this far
=if(A2=B2,””,”x”) but I need to add c2 & d2
Help pleeeease
Hi!
Just nest the OR function in the logical test to evaluate multiple conditions:
=IF(OR(A2=B2, A2=C2, A2=D2), "", "x")
Please note that the above formula leaves the cell blank only if A2 exactly matches B2 or C2 or D2.
To test if A2 contains a name that is part of cell contents of B2 or C2 or D2 (e.g. A2 contains "John" and one of the other cells "John Doe"), use this formula:
=IF(OR(ISNUMBER(SEARCH(A2,B2)), ISNUMBER(SEARCH(A2,C2)), ISNUMBER(SEARCH(A2,D2))), "", "x")
Nope not as simple as I thought but it worked like a charm! Thanks
hi
i used your formula
=IF($C2"", "Completed", "") but facing a problem.
the specific text "Completed" appeared in the targeted cell where i put any text not only date. even if i put a space there, the text "Completed" shown too.
Actually i am trying for a formula where i will put either any date or a certain text in a cell then a specific text will shown in an another cell.
example-
if i know the date then i will put the date 13.10.2019 in a cell then "completed" will be shown in an another cell but if i don't know the date correctly then i will use certain texts like "Not confirmed" then "completed" will be shown.so here i want to merge two formula for a cell.
TIA
can you help me.
IF($C2="", "Completed", "")
Try this and check.
I need something that would work with setting the next columns numbers to negative. If A1>=215 B1 = "-"
A1 B1
215 8 changing the 8 to a negative 8
I believe you need another column in there. If you want the negative values to be in column B, the original 8 should be in column C.
=IF(A20>C20,C20*-1,C20)
Hi all, I need some help because I am pretty dumb with computers :-)
I am trying to get a cell to show either "Long" or "Short" in it by comparing 2 other cells,
ie.....d9 should show long if j9 is greater than m9 or short if j9 is smaller than m9. here is what I have tried and it returns "False"
=IF(J9>=M9, "Long", IF(M9<=J9, "Short"))
Hoping you guys can help.
Change the formula to
=IF(J9>=M9, "Long", IF(J9=M9, "Long", "Short")
However, you may want to consider when they are equal. If so then use the formula below.
=IF(J9>M9, "Long", IF(J9<M9, "Short","Equal"))
Hope this helps
Change the formula to
=IF(J9>=M9, "Long", IF(J9=M9, "", "Short"))
However, you may want to consider when they are equal. If so then use the formula below.
=IF(J9>M9, "Long", IF(J9<M9, "Short","Equal"))
Hope this helps
I am trying to get a cell to have it's background turn red if the result of it's formula is greater than 0 if I have a date in a cell, and nothing in a second cell. This is what I typed but I get an error and not sure why.
=if (and (isblank($C2),$E2="",$F2>0),0 ,1)
Also tried this "=if (AND (isblank($C2)=false, isblank($E2), $F2>0),1 ,0)" still doesn't work, Notsure what I'm doing wrong
I am trying to do the following:
Row 1 options: No, Yes-2nd, Yes 3rd. (Drop down options)
Row two needs to equal:
No= $100
Yes, 2nd= $50
Yes, 3rd= $30.
I can figure it out to be No= $100, but then if I put Yes, 2nd or Yes, 3rd it equals $50.
Help please!!
Is it possible to change the options to Yes1 or Yes2? This might be an easier way of going about this.
i want month wise count the consignment(s) by formula
Eg...
The Month is April
Assign April = "04"
Now count Numbers until the month is not changed from 04 to 05
for example
1/4/19 = 1
5/4/19 = 2
10/4/19 = 3
11/4/19 = 4
but..
01/5/19 = 1
2/5/19 = 2
so, please give me the right formula for the same
To begin with, you need to make sure the data is sorted by date. This is important for the below steps to work.
Now let us assume you have the date values from A1 to A6. Column B will have the following values:
B1=1
B2=IF(MONTH(A2)MONTH(A1), 1, B1+1)
For the rest of the B columns, you can copy paste the B2(You can also drag). So the formula for B3 will be like =IF(MONTH(A3)MONTH(A2), 1, B2+1) and B4 will be =IF(MONTH(A4)MONTH(A3), 1, B3+1)
Hope this helps
Hello I'm using this function below but I keep getting a name response instead of what I am looking for. Is there something wrong with the syntax?
=IF(AND(C3=WHITE, D3=Y), [WhiteY], [FalseWhite])
Thanks
Use this instead
=IF(AND(C3="WHITE", D3="Y"), C3&D3, "Falsewhite")
Note: When you are comparing text, the double quotes sign is required.
I'm trying to put together a formula that will look at a cell and a date and then search two columns to see if those two entries exist and to return a True/False response.
=IF(AND(A2=D2,B2=E2),"TRUE","FALSE")
Hi all
I just want to ask,it is possible to use If function if there is a gap in the given range?
You can use countif function. The following line looks at blank values("") in the Range A1 to B10, and returns TRUE if there is a blank cell.
=COUNTIF(A1:B10,"")>0
Madam
With A column time to B column time if 1 hour late meens c column need to show as "Late" how formala. Pls
Assuming B1 is always greater than equal to A1, you can use the following command
=IF(TEXT(B1-A1,"h")"0","Late","")
Hi,
How to handle situation:
I have I3 value 153022, and I need that if char 1 value is bigger than 2 (or I could use also equal to 7) then it gives to cell K3 value KO and otherwise leave empty. I used =IF(LEFT(I3;1)=7;"K0";" ") but it returns no value to anywhere.
Please advise - is this even possible to do that?
=IF(LEFT(I3,1)=7,"K0","")
Just change the semicolon ";" to a comma ",".
Upon further review, you may need to add quotations to the 7.
=IF(LEFT(I3,1)="7","K0","")
or
=IF(LEFT(I3,1)>1,"K0","")
I'm not sure why it works with > without quotations but, does not with =.
M8 me value chahiya
j8 me zero value he to M8 me E8 ka data aana chahi a
You can use a simple IF statement for this
=IF(J8="", E8, "")
or
=IF(J8=0, E8, "")
=IF(LEFT(A3,1)=6,Y,"") not working????????
=IF(LEFT(A3,1)="6","Y","")
Seems you have to use "" when using the = on the Left function.
Still a problem to paste the formula.
In other words, I wish to insert categories in a column refering to the precedent showing the number of habitants per municipality. To analyse thousands of lines, my categories are for a number of habitants between:
0 and 999: A
1000 and 2499: B
2500 and 4999: C
5000 and 9999: D
10 000 and 19 999: E
20 000 and 500 000: F
So sorry that the copy/paste is not working for the formula... Thank you for your help
Hi Dalia,
I seem to have the same problem and for your formula, you have to use If(and(
Ex. if(and(C1147>=10000,C1147<19999),"E",[similar formula with updated range]
=IF(C1147=1000,C1147=2500,C1147=5000,C1147=10000,C1147=20000,C1147<500000),"F",))))))
=IF(C1147=1000,C1147=2500,C1147=5000,C1147=10000,C1147=20000,C1147<500000),"F",))))))
Something is wrong with the original, it didn't post the whole formula.
Something is wrong with the these forums as it doesn't seem to let me post the actual formula.
=IF(C1147=1000,C1147=2500,C1147=5000,C1147=10000,C1147=20000,C1147<500000),"F",))))))
I am using an IFS function to write a color ("Red", "Yellow", or "Green") in cell F4. It is dependent upon the results (a number between 1 & 5) in another cell (AA4). If I type a number into the cell AA4, the formula yields the appropriate color. However, when I use a formula (Average) to generate the number, it does not work. I get an error message (#N/A in Excel and #Name? in Sheets). How do I get the IFS function to recognize the results in the cell instead of the formula?
Problem solved. It was a rounding issue. Even though I could only see a 1, 2, 3, 4, or 5, the underlying numbers were 1.??, 2.??, etc., so the IF statements were not true. I used the Round function in the Average formula and solved the problem.
I am trying to write an equation that will do the following:
If the value for B6*0.25 is less than 1,000, I want to display 1,000, and then if the value of B6*0.25 is greater than 1,000, I want it to print that value. Below is my failed attempt. Thank you for your help.
=IF(B6*0.251000,B6*0.25))
=IF(K133*0.25<1000,"1000",0)
Hello I'm trying to write an IF statement that results in showing two conditions when met. For example, I input -5 in the reference cell I want the IF statement to punch out 5 below or if the number is positive to punch out 5 above in the same cell.
Use this;
=IF($R14<0,($R14*-1)&" Below",($R14*1)&" Above")
Hi I am trying to use the if function but I am stumped.
I need me S/Sheet to be blank is 0, if between 1 and 28 show yes and higher than 28 No.
My current formula is =IF((AND(F2>=1, F2<=28)), "Yes", "No") but the everything with a 0 gets a no when I need it blank
HELP PLEASE xx
Use this;
=IF($F11=0,"",IF($F11<=28,"Yes","NO"))
I am trying to do travel rates based on where an employee visits. I would like to create a formula that includes 50 states and top cities within those states.
So if John travels to Washington State he choose WA from a drop down box. From there in the next cell cities will become available such as Seattle, Tacoma and Spokane. Based on which city John chooses travel rates will appear in the next cells such as acceptable hotel rates, breakfast, lunch and dinner. If John chooses AZ then Flagstaff, Phoenix, Sedona and Tucson appear...makes sense? Possible?
Hope anyone can help
Yes it's possible with conditional drop down list.
Need data work on it.
In column A (there are 4 sentence choices: I want an Apple, I don't like Bananas, I love coconuts, I need water)
A1 I want an Apple
A2 I don't like Bananas
A3 I love coconuts
A4 I need water
In column B, I want to use a function to check column A and search for partial text, then past a value in cell. For example, in B1, checks for 4 possible conditions in A1, check for "ppl" and copy the value "Yes" to cell in B1 OR check for "Ban" and copy the value "No" to cell B1 OR check for "coco" and copy the value to B1 OR check for "wat" and copy the value "end" to B1.
What function should I use?
If (AND condition works here..
I need your urgent help please
I have two column, i want the current date in second column if i put a text in first column,
When there is no text in first column then no date should be in second column
Ahmed:
From your post it's not clear what type of text should be in the first column, so I just used ISBLANK. Here's what I came up with for you.
=IF(ISBLANK(A2),"",TODAY())
The cell where this formula is entered displays today's date when A2 is not blank otherwise the cell displays nothing.
Doug,
regarding the =IF(ISBLANK(A2),"",TODAY()) , Will the date automatically update each day or will the date be set to whatever day you saved the file? I am trying to use the same idea. I want the date to post when anything is typed in cell A2, but I dont want the date to automatically update each day. I have to create a single file for each day of the month and am hoping to not have to go into each file and type the date each time. Looking to create a template.
Please if you can help me my IF format is
=IF(C2 ISBLANK,"",+30DAYS
So basically what I want is if there is a date in cell C, then for cell D to add 30 days but if it is blank then leave cell D blank, i have an error in the +30 days but i can not work out how to do it
Hello, Sam:
I think what you're looking for is something like this:
=IF(ISBLANK(C2),"",C2+30)
Be sure C2 and the target cell are formatted as dates.
Thank you so much Doug :)
>= 95 % = 1
>= 97 %= 2
>= 99.6 %=4
>= 101 %=5
>=103%= 6
Kindly suggest for above if formula
Hello, Dayanand,
Please try the following formula:
=IF(A1 >= 103%, 6, IF(A1 >= 101%, 5,IF(A1 >= 99.6%, 4, IF(A1 >= 97%, 2, IF(A1 >= 95%, 1, "")))))
You can learn more about nested IF function in Excel in this article on our blog.
Hope you’ll find this information helpful.
hi , how can i write the fact that if two countries use the same Currency 'euro' for exemple , 1 otherwise 0 ?
waiting for your response
Hi Sasou,
If we understand your task correctly, the formula below should work for you:
=IF(A1 = B1, 1, 0)
Where A1 and B1 are the cells that contain currencies.
Hope this is what you need.
Please share me Excel Formula for following....
I want difference in two different dates but if this difference is more than 1 day it should reflect other wise not
Hello, Shekhar,
Assuming that your dates are in the first row, the following formula should work for you:
=IF(B1 > A1, B1-A1, "")
Hope this is what you need.
Is this correct
=IF(LEN(C3=10),RIGHT(C3,8),RIGHT(C3,7))
Hi Samith,
I believe this is what you are looking for:
=IF(LEN(C3)=10,RIGHT(C3,8),RIGHT(C3,7))
If(N2="YES",i should get a drop down list of (a b c d e ), if N2="NO", then i should get NA)
how do i write this formula in excel
Hello, Hermant,
Unfortunately, there is no way to solve your task using standard Excel functions. Most likely you need a special macro. I am really sorry we can’t help you with this.
You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.
I wish I could assist you better.
Hello all, I hope someone can shed some light on my issue.
I am trying to calculate fuel consumption based on different MOVEMENTS of the ship from cell A1.
Cell A1 will either be, LOAD, SEA, MANEUVER, DISCH. Each Movement will correspond to a different CELL with its corresponding fuel consumption rate. SEA corresponds to cell A20 (or 1.02). I tried the IF function, but could only manage to get one IF.
ex A1 (SEA), B1=30 hours. FORMULA in C1, =IF(A1="SEA",(B1*A20)). This one works, but I cant seem to add multiple IF functions for LOAD, MANEUVER, DISCH, withouth it saying #value.
Any help would be great! thank you
Hello, Pricilla,
Thank you for your question.
It looks like you need to apply an IF function with multiple criteria. Please have a look at the following article for more details:
Excel nested IF statement - multiple conditions in a single formula
If you still encounter any difficulties with the formula, please let us know. We'll do our best to help.
Mary,
Thank you so much! That was it! Merry Christmas!
~P~
Pricilla,
Thank you very much for your reply. I'm so glad to hear you found the solution in this article!
Merry Christmas and Happy New Year! :)