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 49. Total comments: 4817
Hi Team,
I need a big help.
If the first column contains a value of months like January, February,etc and the second column has years like 2016, 2017, 2018, etc.. and at the same time i have a different sheets for each month and year for example let we take the attendance and payroll sheet, If i want the data for the number of days present for the month of February 2018 to be displayed in the 3rd column so can you guys please help me out for the same.
Hi guys
I have column A and B.
As long both columnA and B have any integer value that is marked as * completed* , column c have to executed as *yes*
Example:
Column A column B
70591- Completed 80042-completed
Column C
Yes
Wicks:
Wildcards can't be used in IF statements. Will it work for your situation to split the "70591-" out into another cell and build the IF Statement for the cell that contains the word "Complete"? If that will work then you can easily build an IF statement to check for "Complete".
Hi guys
I have column A and B.
As long both columnA and B have any integer value that is marked as * completed* , column c have to executed as *yes*
Example:
Column A column B
70591- Completed 80042-completed
Column C
Yes
Same WorkBook with a number of worksheets which are all timesheets. In V25 on the previous worksheet a formula which happens to be =U18 returns a value in time of 13:30. This means I have done 13.5 hours overtime this month. I want to carry that figure forward to the next timesheet (new worksheet cell B11). We are only allowed to carry over a maximum of 14.5 hours. I would therefore like the formula to say =IF'14 MAY 18'!V25 is less than 14:30, then return the value of V25. In this case 13:30.
However if'14 MAY 18'!V25 = 14:30 then return 14:30.
However if,'14 MAY 18'!V25 is greater than 14:30 then only return 14:30. I then need to work out another formula that shows how many hours lost (if any).do another formula to work out how many hours lost in B12, if any at all.
We got as far as:
CELL B11 I had =IF('9 JUL 18'!V25>14.5,"14:30",'9 JUL 18'!V25)
Ell B12 I had ='9 JUL 18'!V25-'6 AUG 18'!B11
But it doesn't work as you can probably tell already! Your kind assistance would be greatly appreciated. With thanks K
I've got a formula that isn't pulling my true value correctly and I'm sure it's a minor formatting issue.
=IF(P2> 0,"P2", "0.00")
Basically, if "P2" is greater than zero, I'd like it to show whatever value is in P2. If it's less than zero, I'd like it to just show "0.00"
It shows the false value just fine, but shows the literal text of "P2" when my scenario is true.
take out the quotes on P2
Not sure if this will work, but...
I want to know how to write the following;
=if(left a1, 1) = A, or B, or C, then "ON"
It's not working for some reason... help??
hi i want to insert formula excluding 0 but including number 1 to 4 ....plz help
I'm trying to get a cell to work with several other cells and I can't figure out how to do it, because I also need to use + and - inside the formula.
I'm trying do something like this, but the word True or False keeps showing up in the cell, instead of the reference cell number.
=IF(U134>U159+100,"",IF(U134<U159-100,"", Need missing formulas here))
What I am trying to figure out:
If cell number (U134) is greater than cell U159+100, then keep the cell blank. If cell number (U134) is less than cell U159-100, then keep cell blank. If the value of (U134) is equal to or above U159(but below U159+100), then display the value in cell U134. If the value (U134) is equal to or below U159(but above U159-100), then display the value in cell U134.
Basically, if it is outside a range that I set +/- 100, then I want the cell to be blank and if it falls within the range, then I want the cell to display the reference number (U134). I don't want TRUE or FALSE placed into the cell, just leave the cell blank or have the number I am referencing it to.
I can't figure out how to do that. Is there a way I can get it to do what I need it to do? Help please.
Never mind. I figured it out myself. Tough one though.
Here is the formula if anyone is having the same issue:
=IF(AA134>AA159+100,"",IF(AA134<AA159-100,"",AA134))
I guess I was trying to make it harder than it was, by trying to get the cell to add a lot more commands than necessary.
Need formula for following
Sheet 1:
Date, Invoice No, Customer Name, Due Date, Amount
Sheet 2:
Customer Name, Customer ID, Due Days
In Sheet 1 Once Date entered, Due Date column should automatically update by matching Customer name from Sheet 2 adding due days to Date.
hi am trying to create a time sheet to calculate working hours and my start time will be in one day and my end time will be in another day =IF(G28="","",IF(G28-F28>0.208333333333333,G28-F28-0.0208333333333333,G28-F28)) but i cant seem to work it any help would be great
Hello,
I have 2 columns - the first one has the service date listed, and the second column has the days between the service date and the payment date. There are 2 different payment dates for some of the service dates. I want to list a 1 or 2 in a third column - 1 for the first payment and 2 for the second. I have tried an if(and( formula and countif, but I cannot figure it out. Help please!
if i type some text (e.g vasant) i want is value in number, so which formula i use for this.
Vasant:
If I understand your question you want to try:
IF(A2="Vasant",1000,"")
Where the data is in cell A2 the formula says, If the value in A2 is Vasant, then display 1000 otherwise display blank.
You can change the cell addresses,return text and values to suit your needs.
Oh sorry, I didn’t mean c2 and d2 since I used column 2 as my example of cash or credit, but I meant to say the value listed in another, so say C3 and D3.
If a text example would help, then let’s say:
IF(C2=“cash”,C4=C3,0)
So it’s copying the numerical value in C3 to a new column,
But would I need to write this formula many times for each row? Or is there a simpler way?
Sara:
Right now the IF statement says, If the entry in C2 is cash, then put the value that's in C3 into C4 otherwise return 0. If this is what you want then all you need to do is copy this formula down the column and the relative cell references will follow, so you don't need to write the formula, just copy it down the column.
Thank you very much!
Hi! I am trying to create a relatively simple formula. I need it to read one column, and if the cell says “credit card” to enter 0
Ok so that’s =IF(C2=“credit card”, 0
But then if it says, “cash,” I need it to copy the dollar amount listed. So I know I could write it in c2, d2, etc, But is there a way to write only one formula?
Essentially, I need to add up only “cash” sales (not “credit card”). So I thought if I entered 0 for credit and could duplicate the cash cells, that I could then simply sum the new column. Thank you for any help!
Hi Doug - can you give me your email and I'll send you a mini spreadsheet with the requirements on.
Thanks again
John
Hi, I run a sports & social club register and each member has to do certain duties. I need to automatically assign a date a member did one of three duties by putting a code against their name in the register OP, REG, RAF into a last date column. I've tried the sumif but obviously not right for this requirement.
Thanks for your assistance.
John
Can you post a sample?
I realise my question was not clear.
I have forecast months in column A, prospective sales in column B and then a 12 month schedule starting Jan in column C, Feb in D and so on.
I want a calculation to read the forecast month in column A (for example July) and then show the prospective sales value in the July column - and change to another month if I changed column A to September for example
I'm having trouble with this;
if a cell shows Jul-18 (or another month) then do show a figure in another cell
I've tried =if(d1=datevalue("01.07.18-31.07.18"),D36,0)
I created a formula that looks like: =if(d5=a12,f12,0). My problem is how will Excel compute autimaticaly if I change the value in d5 that equal the value in a13, the value in f13 will automatically be the value of the cell? Thank you
I am trying to create a formula that will compare a date in a column to a specific given date, if prior that that date, I want it to put the value that is stored in another cell. If the date is after either leave blank or put in a word.
Example
If(J47 earlier than 01/01/2017, +h47, "Active")
I thought this would work:
=IF(J47<DATEVALUE("01/01/2017")+H47,"Active")
I get a message that says problem with this formula, how do I change it to make it work?
Thanks for your help!
Rhonda Bruhn
Rhonda:
I would put the 1/1/2017 date in say H47 and then compare the other dates to it. Looks like this
=IF(J47<=$H$47,I47,"Active")
IF J47 is earlier than or equal to the date in H47 then display the value in I47 otherwise display Active.
In this example the cell holding the 1/1/2017 is being held as an absolute reference by the $. In other words, when you copy the formula up or down the J column each value is compared to the date in H47 or in this case, 1/1/2017.
How to put this is formula (For calculation of Discount in a Cell on Text Basis)
IF B1=YES then C1=A1*10/100 & IF B1=NO then C1=A1
Which logical formula can I use to verify that the companies haven't given employees more than 3% escalation, and if they do, what is the % applied between each year. Thank you
Andy Garcia 2015 60.1 2016 67.31 2017 79.23 2018 79.23
Domingo Solis 2015 45.00 2016 46.35 2017 48.66 2018 56.24
John:
Where the data is in O13 and N13 the formula is:
=(O13-N13)/O13 then format the cell holding the result as a percentage with two decimal places.
In your samples the increase for Andy from 2015 to 2016 is 10.71%. So, (2016-2015)/2016.
Per the example shown below, how do I create a worksheet formula to look for duplicates in column B, then corresponding duplicates in column C and if true, sum amounts in D and then separately sum amounts in E? (e.g.- Jane Doe) If duplicate in column B, but not corresponding duplicate in C, then do nothing. (e.g.- John Smith)
A B C D E
1. DOE,JANE 234567890 5/1/2018 $84.85 $3.39
2. DOE,JANE 234567890 5/1/2018 $405.32 $16.21
3. SMITH,JOHN 987654321 5/1/2018 $247.13 $9.89
4. SMITH,JOHN 987654321 6/1/2018 $247.13 $9.89
5. JONES,JIM 736429746 5/1/2018 $369.91 $18.49
6. ROBERTS,JUDY 103947492 5/1/2018 $555.88 $27.80
Overhead costs include maintenance, cleaning, and administrative expenses associated with a car rental. The Overhead for one rental transaction is $50 for an economy car and $75 for a premium car. This Overhead cost includes maintenance, cleaning, and administrative costs for each car rental. a. With this information, in the Profit Analysis worksheet, in the first cell under the Overhead label, calculate the overhead that corresponds to the number of cars rented (NumCars) using an "IF" statement as part of the formula. b. For clarification, use the "IF" statement to determine whether to use $50 or $75 times the NumCars so that the Overhead value represents the total Overhead costs for the Number of Cars (NumCars) rented in a given row.
I want to ask you a question. I want one cell turning into red when 30 days passed after day of payment,so i'll now that the customer have to pay after 30 days
I need a formula that produces specific text if certain cells contain information (a date to be specific). The three preceding columns indicate an action being performed, and I want a column to auto populate the status.
The status' would be "Drafted" "Reviewed" and "Finalized".
Please advise if there is a formula that can accomplish this. Thanks
I want cell A, if showing a negative number, to show as zero in cell B, but if cell A is not a negative number, I want it to show as the non negative value in cell B. What formula would I use? Thanks
Tim:
I believe this will work. If the data is in cell A1, enter this in cell B1:
=IF(A1<0,0,A1)
Our electricity providers use very complicated formulas to calculate our monthly electricity rate. I am needing to change providers and find the least expensive company to use. I am trying to find an Excel formula to do a "look back" over my previous usage, using these current formulas.
In simple terms, these formulas are like the following:
"I have 10 apples. I will sell the first 5 apples for 50 cents each, the next 3 for 25 cents each and the last 2 for 10 cents each."
Can anyone help me with a formula that would accomplish this task? In the case of the electricity providers, the variables are the cost per apple and the range of usage that falls into that particular rate.
Thank you.
Scott:
Can you provide the actual amount they charged and the formula they used to arrive at that amount?
Thank you. This article helped me alot.
Hi,
If there a context in a cell separated by space or - then, I want the out put as true else false.
Anybody can help on this?
=IF(X4=A,"1",IF(X4=B,"2",IF(X4>=C,"3",IF(X4=D,"4",IF(X4=F,"5",)))))
What is the error in this if function?
Alex:
It looks as though you've got the quotes around the wrong
characters.
This should work:=IF(X4="A",1,IF(X4="B",2,IF(X4>="C",3,IF(X4="D",4,IF(X4="F",5,)))))
I have trouble creating nested ifs. I have three conditions:
T1 = 00:00 - 06:59
T2 = 07:00 - 14:59
T3 = 15:00 - 23:59
And these are the cells that I need to match the above conditions. The dates and times are together in one cell.
Col A
Row 1: 06/04/2018 06:00:25
Row 2: 06/04/2018 08:00:26
Row 3: 06/04/2018 18:35:18
This is my formula:
=If(And(A1R1>T1, A1R1T1, A1R2T1, A1R3<T1), T3, "")
How can I combine this?
Greg:
The formula you've provided isn't clear. Can you provide your needs in an IF/Then format? For example, IF A1>=6:00 then T1, IF A1>= 7:00 then T2. Something along those lines.
Ajith:
Where the value is in A55, the formula to accomplish what you want is:
=IF(A55>=5,500,"Something Else")
The formula says, If A55 is greater than or equal to 5, then enter 500 in this cell, otherwise enter Something Else.
You can change the cell address to the address you need. Also the "Something Else" text to different text. Just remember, if you want the cell to display text to enter it inside quotes. Numbers don't need quotes.
Pls help me create a formula:
If column B contains a particular value, then find the corresponding value in column A
you'll need to use a vlookup function.
Hello,
I have two columns of data and need to check Column A and then change the value to column B. like if the value is more than 5 in column A then corresponding value in column B should be 500.
Kindly help.
I am trying to do a calculation to get the AOV from my transactions and revenue. In my pivot table I have blank cells and cells with o in them that I need to ignore so I don't get errors and I have tried this but it is not correct, please help
=IF(AND(ISBLANK(K5), "", K5/K22, IF(AND((K5,K5/K22,""))
Amy:
Are you trying to count the entries or sum them.
Why aren't you using COUNT, COUNTA, COUNTIF, COUNTIFS, SUMIF, SUMIFS or something similar?
Hi.. Please solve the problem
If i input G16 then auto count H16, I16 is subtract of G16, H16 and finally J16 would be count on H16 with three fixed after numerical digit .20, .43 .53
G16 H16 I16 J16
Gross Tare Net Bag Size
22.20 0.20 22 small bag
Please note that all calculation depends on G16 Its mean gross will be be before decimal are flexible after decimal are fix (for example gross are may be 19.20, 23.2, 37.2, 28.2 28.43, 29.43, 29.5, 47.5 etc.)
G16 (input double numerical digit e.g. 15.20 & 15.5)
=G16-TRUNC(G16)
=H16-I16
=IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))
Hi,
I would like to know a formula for this example:
If a current price in cell B10 is 2,00 and new price in cell C10 is 1,00 I will get a cost impact in cell D10 of -1,00. The price has decreased.
But if both cells (B10 and C10) are empty I do not want 0,00 (zero) in cell D10, I need the cell to be empty.
Thanks!
Lina:
I think this is what you want in D10:
=IF(B10>0,C10-B10,"")
Hi, I need a help. I have been getting Excal report form one of the vender. Like one column has two different format date and time. So I need to change this into one format.
Report Time
05/03/2018 11:19
02/03/2018 6:07
12/02/2018 8:26
24-04-18 16:34
26-01-18 10:21
25-04-18 18:07
Need to change this into format "dd/mm/yyyy h:mm:ss"
Hi! I'm needing to add IF formula in TRUNC calculated numerical digit. Everything is OK but when i input in G16 double numeric digit (example: 25.20) small bag condition its not work properly. please solve the problem.
G16 (input double digit numerical value 25.20 is not working or 7.20 is working)
=G16-TRUNC(G16)
=IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))
Neetu:
I don't know what is wrong on your end, but this formula works fine on my practice sheet.
I can look at the other issue if you can tell me how H16 is related to the values in G16 and how H16 and I16 are related.
For example, If G16 is 25.2 then H16 is .5 also if H16 is .5 then I16 is 7.
I want to input in
for examole
G16 25.2 then H16 is 0.2 and I16 is 25.00 J16 is "small bag"
G16 25.43 then H16 is 0.43 and I16 is 25.00 J16 is "medium bag"
G16 25.50 then H16 is 0.50 and I16 is 25.00 J16 is "large bag"
=G16-TRUNC(G16)
=H16-I16
=IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))
this is the relationship but i am unable to establish the small bag relation that is 0.20. J16, H16 & I16 autofill
Please solve the problem.
Neetu:
I think this should get you what you're after.
Enter this into H16: =IF(G16=25.2,0.2,IF(G16=25.43,0.43,IF(G16=25.5,0.5,"-")))
Enter this into I16: =IF(G16=25.2,25,IF(G16=25.43,25,IF(G16=25.5,25,"-")))
Enter this into J16: =IF(G16=25.2,"Small Bag",IF(G16=25.43,"Medium Bag",IF(G16=25.5,"Large Bag","-")))
I know the formula in I16 just repeats the "25" value, but if you need to change the values the formula is in place.
Than you for cooperation but the actual count which tare is .20 will count as small bag, .43 as medium bag & .50 as large bag. On the other hand the gross will be as open mode and mentioned all three tare value would be fixed. Its mean gross will be be before decimal as flexible after decimal are fix (for example gross are may be 19.20, 23.2, 37.2, 28.2 28.43, 29.43, 29.5, 47.5 etc.
If i input G16 then auto count H16 and finally J16 would be count on H16. please note that all calculation depends on G16
G16 H16 I16 J16
Gross Tare Net Bag Size
Dear Sir,
there are three words in data validation list( CSL,CST,CFL), I WANT TO USE A FORMULA TO WITH RIGHT FUNCTION TO SHOW(IF RIGHT TWO WORD 'SL' THEN SHOW 'SOLID', IF 'ST' THEN 'STRANDED', 'IF; 'FL' THEN FLEXIBLE.
Veeresh:
Can you clear this up for me? Does the data validation return CSL,CST or CFL or does it return SL,ST or FL? If not why not? Why not have one set or the other?
If it returns SL,ST or FL the answer is: Where the return cell is A64 it looks like this:
=IF(A64="SL","Solid",IF(A64="FL","Flexible",IF(A64="ST","Stranded","NOT IN LIST")))
If it returns CSL,CST or CFL the answer is: Where the return cell is A64 it looks like this:
=IF(A64="CSL","Solid",IF(A64="CFL","Flexible",IF(A64="CST","Stranded","NOT IN LIST")))
Hi, good evening,
I want to use IF function in TRUNC calculated function. If i input the numeric digit 7.20 it's OK but double numeric digit (ex. 25.20) is not work properly (only 0.20). Please solve the problem.
Thanks & regards
neetu
G16
=G16-TRUNC(G16)
=IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))
Neetu:
Why do you want to use TRUNC? That function truncates a number to an integer by removing the fractional part of the number. So, TRUNC(4.3) returns 4 and it's 4 for ever more.
Are you trying to display a number? Maybe round off a number? Can you use ROUND or format the cell that holds the end result?
Is G16 formatted as a number or text? Maybe TRIM?
What I'm suggesting is that there are a number of methods you can use to display a number in the format you want without removing the data. Because, once the data is gone, it's gone.
actually I need to calculation on after decimal number in 3 fixed value 0.50, 0.43 & 0.20
G16
=G16-TRUNC(G16)
=IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))
Is this correct formula? please help.
thank u.
Neetu:
The formula you have here works great. When you input the value, it returns the text you want. I'm still not clear on why you need to use the TRUNC function.
I don't understand what you want to do with the fixed values. What type of calculation do you want to do with them?
Good Morning! I just try to prepare local invoice in excel sheet. where tare value is fixed bag weight (0.50, 0.43 & 0.20). Actually I want to in the sheet input area only G16 (with double digit numerical value) other cell are should be auto calculated. this is the matter. if i use wrong formula then please help me with new formula. TRUNC is not important.
G16 H16 I16 J16
Gross Tare Net Bag Size
17.20 0.20 7.00 small bag
Neetu:
The values in H16,I16 and J16 can be auto-filled if Excel knows the relationship between the cells.
For example, when H16 is ".2" then J16 is filled with "Small Bag". This is working on my practice sheet now.
I can help you with this question more completely if you tell me what the relationship is between these cells. For example, if G16 is "17.2" is H16 ".2" and if H16 is ".2" is I16 "7"? Show me the relationships. It doesn't have to be in a formula.
G16 (input double digit numerical value 25.20 is not working or 7.20 is working)
=G16-TRUNC(G16)
=IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))
this is the relationship but i am unable to establish the small bag relation that is 0.20.
for example:
my data from number of line
so eash 1 page have below or =<20 line
i have 9282 name
so every 1 page must below or =<20
1st situation is if i use divide 9282/20 = 464.1 same like 465 page
2nd situation is if i use divide 1/20 =0.05 same like 1 page
So want kind of formula i can use IF?
Hi can you solve this for me please.
If(J4>J3,J4-J3,0) but If(J4=Blank of 0,0) and if(J3-blank or 0,0.
it mean If J4>J3 calculate J4-J3 but if J3 or J4 = Blank or 0 return to 0.
thank before hand.
Sarith:
It appears as if you've answered your own question. The formula is:
=If(J4>J3,J4-J3,0)
in a row ex. a2,a3,a4,a5,a6,a7,a8,a9,a10. out of this 1 cell contain a number "0"(a2=1,a3=1,a4=1,a5=1,a6=0). the formula i need is to know when one of the row is zero it need to pickup and tell there`s a zero in row a2___a10
Sathi:
Why not just highlight the cells that contain a "0"?
Select the cells, go to Format, then Conditional Formatting.
In the conditional formatting window select the "Cell Vale is Equal to 0" in the three cells then click the Format button and choose a pattern you like and OK out to the sheet.
I am looking for a formula where:
column b row 193= minimum
column b row 194= maximum
if b194 is greater than b193 then b198 must be populated with a specific note and validated in another worksheet.
If note matched, passed; otherwise failed.
if there is no range found between b193 and 194; 198 should be blank.
I would really appreciate any inputs and help.
mary
Hello, I am trying to update a spreadsheet that was created by an old director. They have an area within the spreadsheet that has a column for goals, and then the monthly figures to in the columns next to it. The monthly number has either a checkmark, an exclamation point, or an x before the number to indicate if it is within goal. For example: Goal = 94% but the April column for that specific goal is 89%, this would read an 'X 89%' in the applicable cell. But the characters are also in color, so it is not just a letter or symbol typed in. When going in the cell, i cant even see any what if statements or anything other than the attached data to another sheet with in the spreadsheet. I hope this makes sense. Does anyone know how to enter a formula into a cell in order to get these characters in there? Thanks so much.
Megan:
To start, it might be beneficial if you can see the formulas and their respective cell references. There is a good description of how to accomplish this in the various versions of Excel here on AbleBits.
In the search box type "show formulas".
Once this is done you might be able to figure this out on your own, but if not write back and someone can probably help.
Hi,
can any one help me to solve my following issue,
i have a students register numbers, based on these numbers i need to return a thee numbers in another excel sheet.if any break on these register numbers i need to return a blank value on excel sheet, and from next cell it will continue based on numbers.
reference No Excel Sheet
1 1
2 2
3 3
4 4
5 5
6 6
7 7
9 it should be blank cell (bcoz break between 7 & 9)
9
please tell me the solution as soon as possible.
Thanks in advance.
Very useful. Thank you so much.
Is an If Then appropriate to highlight a cell or row that is blank when based off whats in the adjacent cell there should be a number there, for example:
if F1 is occupied and D1 is blank, Highlight that row.
or if D1 is occupied and F1 is blank Highlight that row.
what would that formula look like and can it be applied to an entire sheet?