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 on: How to use IF function in Excel: examples for text, numbers, dates, blanks
by Svetlana Cheusheva, updated on
Comments page 34. Total comments: 4822
what is the formula for interval,
fill the cell outstanding if score 4.5 to 5
fill the cell V.Good if the score between 3.5 to 4.4, and so on..
i tried to make rounding but it doesn't work if the number not whole even after rounding
This one should work. You can change the text results to whichever you think fits better.
=IF(E5>=4.5,"OUTSTANDING",IF(AND(E5>=3.5,E5=2.5,E5=1.5,E5<2.5),"SATISFACTORY",IF(E5<1.5,"FAIL","")))))
Hi
I have forgotten how to repeat a certain set of numbers for a spreadsheet.
For example i have a list of 3 items and i want the IF function to repeat as 123123 etc.
I have put 3 in the A1 cell and the number 1 in A2.
Thanks
Id like to ask how to use if function or whatever easy function can be used should I want to display the percentage in a cell if a condition has been met. The condition is if all specified range of cells contains any values like a word or date. Lets just say that cell A1 will display the percentage of completion if cells ranging from A2,A3,A4,A5,A6 contains a value like yes or no or a date.
Hi Jason,
It's not clear how you calculate the percentage of completion, but the idea is to count non-empty cells in the range A2:A6, and if the count is equal to 6, display the percentage, otherwise return an empty string:
=IF(COUNTIF(A2:A6, "<>"&"")=5, percentage formula, "")
Please i need you assistance to building formula as follows:
if A1 contains specific text, A OR B OR C = CLOSED
if A1 contains specific text, C = OPEN
Many Thanks.
I have a spreadsheet, with multiple tabs I need to be able to Concatenate a text cell (A3) with a date cell(B3) on a report form, but be blank when the date cell (B3) is empty. this is what I have so Far.
=IF(AND(NOT(ISBLANK('Super(No7)'!A3))*NOT(ISBLANK('Super(No7)'!B3))),CONCATENATE('Super(No7)'!A3&""&TEXT('Super(No7)'!B3,"DD/MM/YYYY"),"")).
The above formula works until I add the &TEXT& bit then it returns FALSE. can anyone help please.
I'm trying to get an if, then formula to generate a number in a cell if the adjacent cell is a certain letter (for GPA calculations), but i keep getting a value error message. Here is what I wrote: =IF(C3="A",4,IF(C3="b",3,IF(C3-"c",2,IF(C3="D",1,IF(C3="F",0)))))
That formula works except for when the value is "C".
Change the "-" into "=".
Hi,
I am trying to have the value of a cell(B2) in Sheet2 populate in Sheet1 cell(B3). I thought I could do ='Sheet2'!B2, but when there is no data in Sheet2 B2 the value populates as 0 in B3, I would like it to remain blank.
=IF(Sheet2!B2=0,"",Sheet2!B2)
That should work.
Thank you! Works like a charm!
Please help me build a formula as follows:
if A1 contains A,B or D then it is CLOSED, if A1 contain C then it is OPEN
thanks
=IF(OR(A1="a",A1="b",A1="d"),"closed",IF(A1="c","open",""))
Should work for your formula.
if C1, C3 & C5 have "text", how to total them in C10?
(There are approx. 20 cells with or without text that would report to C10.)
Example: C1 = smith. C3 = . C5 = jones. In this case, I want C10 to show the number "2", allowing me to calculate off of that.
Thanks in advance!
Hi James,
To count cells with text, you can use the COUNTIF function with "*" as the criteria. For example:
=COUNTIF(C1:C9, "*")
I want to make a one formula in Excel for:
IF 825>the answerthe answer4001,*0.5
Hi,
I need an help to auto calculate the order quantity, if a particular cell is less than eg : 60.
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)
i have the following formula:
=if(isblank(A1),"", if(D1<0, weeknum(A1,1),""))
working with this formula:
=if(isblank(J2),"", hlookup(max(#REF!),$R$1:$DD$89,row(J2),false))
to calculate weeks of perfect attendance. However, it has errored out for anything after week 52. how would i fix this to get it to continue past week 52.
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.
Hi is it possible to do an if formula when a cell is either finance or cash have it do another formula to equal commission?
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))))
Assuming that A1 is 2006 and B1 is 2006
=IF(A1=B1,"True","False") comes up True
but
=IF(A1="2006","True","False") comes up False.
How do I resolve this within a formula since the 2006 is coming from a different formula.
Thanks in advance.
I would like to check a cell for currency format and if its in £ then multiply it by the $ factor to convert it to $s' and if its in $ then leave it in $s'.
example: A1 is the £ to $ currency factor; A2 is the value in £ or $; A3 will be the value of A1 in $s.
I have tried using the IF function, but it will not recognise A1 currency format! I would appreciate some help with this. Many thanks.
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","")))
IF Early Bird criteria - Customers who want to avail of an Early Bird discount must place their orders on weekdays between 11:45 a.m. - 12:00 p.m. (for lunch) or 6:45 p.m. - 7:30 p.m. (for dinner).
Write a logical function to find out whether a customer is an early bird. (refer to the 'Early Bird' criteria stated above) In your function, you will have to reference Column E values (day of week) and Column F (time of order) to determine whether customers are early birds. Think about how you will reference the early bird criteria.
If a customer is an early bird, the function should return "Early Bird". Otherwise, the function should return a blank value.
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", "")
i using if condition formula it taking only 10 conditions is it only 10 conditions allowed
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=
Very Nice, i got it what i wanted thank you.
Make sure the brackets are full closed .This will prevent the repetion.
Hi, I just want the formula like I just evolving each cell's values like marks. If more than (for example >=50) just want to show that value only using.
For example, someone takes 40, 45, 50, 30 evolving all values and retrieve only more than 50 values.
Assign sequential numeric numbers to each company with IF funciton. If a company name is repeated, it will have the same number.
anyone please slve this?
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)
Hi I need some assistance with the if function.
The question is female started the swim 30 seconds after the males. I column G insert formula , adding 1 point to the total points which is in column F is the competitor is Female and for Males add no points.
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.
Use the IF function in cell E5 to calculate the annual total amount, which is the sum of the basic cost and locker fees for those who rent a locker.
For people who do not rent a locker, the annual cost is only the cost shown in column C. The Locker column displays Yes for clients who rent a locker and No for those who don’t.
Note: You must use the proper function. Do NOT write your own formula.
I can't find any (IF) Function examples to do this assignment.
I circumstance is easy enough. If client uses a locker "Yes" or does not use Locker "No" then if "Yes" add $75 to Client cost. If "NO" then the Cost in final cost does not change.
i'm trying to create a formula that will show todays date on the next cell. For example...IF H2 say Adj then I2 should have today real time date. But my issue is that i need the date to change for whatever cell i put adj. 123456---29-Oct---Watermelons---1--Quality--654321--Dole---Pending ( this will show pending until i switch it to Adj) and when i finally switch to Adj i want todays date to pop up on the ("I"cell). is it even possible? i was told that ALL my cells will show todays date and change everyday which i dont want to happen because if tomorrow i have more rejections i'll make adjustments that day and i would need the cell to have that date on it.
Hi...
I don't know if this thread is still open but i need some help.
i have never used excel before but i need to do something to present to my workplace.
the idea is this: 7 columns, 5 rows and 4 numbers. the numbers need to be placed across the board.
a few rules need to apply: at each row, the numbers must be in ascending order (from left to right) and the 4th number must be placed 2 times exactly, next to each other.
at each column, the 4th number must not exist more then 2 time or less then 1 time..
both columns and rows rule: numbers must be equally placed across the board as much as possible.
I columns as
Column-1 = Latest; Column-2 = Code-2 & Code-4 and delay in re-submission. So, i need formula is that Latest = L&S; if Code-2 & Code-4, greater than 0 should indicate-1. so, please can you send me the if formula.
Hi Svetlana,
Wondering if you can help me.
I am creating an order system and I want to increase stock ordering for products that are on offer.
In a cell I either have 0 (Cell G in image) or a date. (Cell G Highlighted Red)
I want to know if there is a formula that will return a % for any date lets say 130% (Cell H highlighted in black)
And return 0% for 0 in the cell(Cell H Highlighted in Blue)
Regards,
Robbie
Just realised I can not attach an image.
Hi,
I have one cell with A1 - "9000" and for A2 I want "9000-000", How I can tell to the excel to put the information of A1+"-000" on A2?
Thanks
can you help me!
=IF(Teamno=B3,MVisit=A5,MAX(SrNo),MAX(MDATE),0)
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")