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 48. Total comments: 4817
Help!
Column A Column B Column C Column D Column E
ABC DEF
ABC ABC
In Column E, I'm trying to find a formula that would populate ABC when it's only ABC in one or more cells in row 1; DEF when it's only DEF in one or more cells in row 1; if ABC and DEF are in the same row as it shown in row 1, then I want the formula to return "BOTH". IF statement doesn't seem to work IF(A2:D2="ABC","ABC",IF(A2:B2="DEF","DEF",IF(A2:A2="","","BOTH")))
Help!
Column A Column B Column C Column D
ABC DEF IF(I2:M2="ABC","ABC",IF(I2:M2="DEF","DEF",IF(I2:M2="","","BOTH")))
ABC ABC
IF(A12.5,"1500")))). CAN YOU PLEASE GIVE ME CORRECT ONE...
=IF(A4="GP","3",IF(A4="DMF","4",IF(A4="CARD","5",IF(A4="ORTHO","6"))))
SUB_LEVEL IN_DATE WORK_DESCRIPTION STATUS OUT_DATE
A 17-07-2018 SKELETON MAKING #VALUE! 18-07-2018
1).In STATUS Column i want "In Progress" when i enter date in (IN_DATE) Column.
2). And i want "Pending" in STATUS Column if the SUB_LEVEL Column is empty.
3). And i want "Finished" in STATUS Column if i enter any date in OUT_DATE Column.
I have tried using the following formula but it returs an error, Please help:
=IF($B4"","In Progress","Pending")*OR(IF($E4"","-Done",""))
I would like to create a formula that enable me to do the following
If col A =Jack is selected (from a list of 10 names) and
Col B=Cat is selected (from a list of 10 animals)
Col C= result in a date (from a list of dates)
Hi,
I have 4 Column, In column A Date, B diffrent Product (almost 10)
C Purchase Price D Sale Price.
Daily sale / purchase enter one by one.
I Want Prouductwise total (Purchase / Sale) in sepate sheet or column
we need a more clear formula for this function
100 direct 70 Indirect
if direct method shows 100 value than 70 shows indirect method ?kindly help me
What formula/function should be used on number say
if 123-45-6789 then SSN and if 66-666666 the EIN
SQL:
What do you want to do with this data?
I need to create a formula that adds all the S, M, L XL, 2XL in a column. I want them individually, i.e. I am adding tee shirt sizes for a large group, would like an If it equals S, then add them up...
If the "logical_test" is true, I want a value put into another cell. If the "test" is false, I want the words "Out of range" displayed.
Example: =IF (A13=50,C23=95,"Out of Range")
That is, if true, the value 95 is put into cell C23.
How do I write the formula?
Tomas:
In C23 enter =IF(A13=50,95,"Out Of Range")
i try to figure out a formula
if A1:A15=A and B1:B15=750 , C,D
C is value from the other sheet
Hi,
Please suggest a formula
A1 value is 50
If A1 >0 It should be "0" if not it should be "50" (value of A1)
I had the same issue. Here is what I discovered on my own and it works.
=IF(AA134>AA159+100,"",IF(AA134<AA159-100,"",AA134))
So, for you, I would suggest replacing my cell letters & numbers with yours.
Maybe try:
=IF(A10,"50"))
I haven't tried it yet to say if it will work, so try it.
What the formula says is this "If A1 is less than zero, than put 0 in the cell. If A1 is greater than zero, than put 50 in the cell".
I hope this helps.
It took out half of the message that I wrote down. Weird. Let's try this again...
=IF(A10,"50")) so let's see if typing after it helps to keep the sentence and formula together. lol
This is ridiculous, it did it again.
Gonna try a new way. I'm going to use spaces to make sure it does it this time. Sigh. Computers, am I right?
= IF ( A1 0 , "50" ))
When you type this, remove all spaces. It won't let me type the whole thing without removing half of the formula.
I can also try this...
'=IF(A10,"50"))
I will see if it worked after its posted
OK.. nothing is working. Time to use the last resort
=
IF
(
A1
0
,
"50"
))
Sorry guys, this stupid blog engine often "eats" the "greater than" and "less than" symbols. So, let me post a formula for this condition:
"If A1 is less than zero, than put 0 in the cell. If A1 is greater than zero, than put the value of A1 in the cell."
=IF(A1<0, 0, A1)
I have tried to wrap my brain around this one, any help would be appreciated: If cell C3 contains Doctor then I want B3 do indicate 1, if cell C3 contains Dentist, I want B3 to indicate 2; If cell C4 contains Doctor then I want B4 do indicate 1, if cell C4 contains Dentist, I want B4 to indicate 2 etc. as per the list below etc
1 Doctor
2 Dentist
3 Pharmacist
4 Optometrist
5 Veterinarian
6 Consultant
7 PME
8 Other
Thank you !
I am kinda stuck with creating a formula. I want something that, example: IF A1 is equal to or greater than (numbers in Column E) then show cell in Column E that is Greater than A1. So basically I want it to show the next highest number. I am working with dates here by the way.
Hi, I need the help of the experts! I've been tasked with creating a weekly time sheet for employees at my company, so I'm trying to figure out how to write a IF formula that sums up the totals of daily hours worked and if greater than 40 hours the remainder(overtime) goes into another cell, if less than 40 then the actual sum goes into the cell. In other words, I want cell C13 to have the sum of H29:N29 and if that value is over 40 then the remainder would go into cell C14. Is this possible?
Hi everyone,
I want to take the AVERAGE of any cell if the Campus is X
So, If Column C is my Campus and Campus=DEN (any value equal to DEN $C) will take the value of G$ and average it. This formula will be in a separate cell.
Is this possible? If so, and I sort the table will the formula continue to work?
Thank you for your help.
Hi Team,
I am trying to build a formula to tell if a given zip code falls within a list of zip codes. EG If I input 11201 in cell A1, I want A2 to tell me "Yes" or "No" if it falls withing a list of given zip codes.
If it helps, the list of zip codes are: 11201, 11205, 11206, 11211, 11213, 11216, 11217, 11221, 11231, 11238, and 11249.
I have an issue with graphs in Excel. I have 2 workbooks, workbook 1 is the master and has line graphs on sheet 1 and data for the graphs in sheet 2.
Workbook 2 is an exact copy as far as the graphs and data is concerned. I don't want to enter the same data twice so I am using this formula to copy values of data from workbook 1 to 2 and it works. =IF('[Commercial Sector KPIs 2018 V1.xlsx]Data'!H3,'[Commercial Sector KPIs 2018 V1.xlsx]Data'!H3,"")
But..... The data is from January to December. I have only just started to use this formula in wb2 and when I copy it over from July to December the graph shows zero's for those 6 months which I don't want it to do as it affects the linear trend line I'm using.
Is there a way of having the formulas in wb2 and the graph only showing results if there is an actual value in wb1.
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?