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 50. Total comments: 4817
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?
Sales 100% 90-99 89-80 70-79 <70
Target
<80% 2.10% 1.9% 1.7% 1.3% 1.1%
<89% 2.40% 2.2% 1.9% 1.4% 1.2%
<99% 2.70% 2.4% 2.2% 1.6% 1.4%
100% 3.00% 2.7% 2.4% 1.8% 1.5%
106% 4.50% 4.1% 3.6% 2.7% 2.3%
i want to create a incentive sheet
if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1
i am trying to calculate via this formula but the result shown false
=IF(AND(E8<80%,E9<70%),D12*N15,IF(AND(E8<90%,E9<70%),D12*N16))
plz correct it
Faisal:
I've looked at you question off and on for a couple of days and because of the formula you included it's still not clear to me what you're after.
However, if what you want to accomplish is this:
if sales target is less then 80% and avg selling price is <70% then value cell*with 1.1
the formula would look like this:
=IF(AND(A196<80%,A197=70%),A198*1.1,"Something Else")
If this is what you want, replace the "A" cell addresses with the addresses where your data is stored.
This formula says: If the value in cell A196 is "<80%" and the value in cell A197 is "70%" then multiply the value in cell A198 times 1.1 otherwise display "Something Else".
Thanks a bunch! I had issues with using the IF function on other examples because it depends on your version of excel. I'm using 2004 I believe and this example worked perfectly for me! Cheers :)
I am adding 2 cells together. Sometimes one or the other cell is blank and the =sum(a1+b1) formula doesn't bring back a value. if cell b1 is blank then return cell a1 value,or if cell a1 is blank then return cell b1 value, otherwise sum a1+b1
Leni:
This should work: =SUMIF(A5:B5,">0")
Where the numbers are in A5 and B5 and are greater than zero.
Asjath:
This type of situation calls for the VLOOKUP function.
In your case in Sheet 2 B1 enter this:
=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)
Where the data you want to lookup is in Sheet 1 cells A1:B3
guys,
I have two sheets in my excel.
Sheet1,
A1:A10 are text & B1:B10 are text
Now in sheet2
if i link any of item from A(A1 to A10), in next i need the B item automatically.
who can help to this?
I am trying to get a formula to work from a date table, and using today's date. For example: if today within April 2018 it will pull the code "EX" and place it in a cell in another sheet.
YEAR/MONTH JAN FEB MAR APR MAY JUN
2018 AX BX CX DX EX FX
2019 AY BY CY DY EY FY
2020 AZ BZ CZ DZ EZ FZ
2021 NA PA QA RA SA TA
This is what I was working with but because I am using 2013 I am limited to 7 rows.
IF((YEAR(TODAY())=2018)
AND(MONTH(TODAY())=01),Date!B2,
OR(MONTH(TODAY())=02),Date!C2,
OR(MONTH(TODAY())=03),Date!D2,
OR(MONTH(TODAY())=04),Date!E2,
OR((MONTH(TODAY())=5),Date!F2,
OR(MONTH(TODAY())=6),Date!G2,
OR(MONTH(TODAY())=7),Date!H2,
OR(MONTH(TODAY())=8),Date!I2,
OR(MONTH(TODAY())=9),Date!J2,
OR(MONTH(TODAY())=10),Date!K2,
OR(MONTH(TODAY())=11),Date!L2)
Any help would be greatly welcomed.
I am trying to use a formula to do the following
in F1 I have the TODAY Formula
Column A will contain various dates
Column C an Y or N
If Todays date matches the date in any cell in column A, i want the Y or N in column see to be pulled into the I2 under todays date. Can any one help with this?
Sir
I want to use formula that if cell no menton RD then multiple of 5% and if cell number mention URD then no multiple. Please tell me sir.
Is the data in these cells "TEXT and a NUMBER"? Or do they contain only text or only a number. Show an examples of some cell's contents.
Hi,
i need formula:
if m11=100% Q11 show 15%
if m11=51% Q11 show 7.5%
if m11=52% Q11 show 7.7%
if m11=53% Q11 show 7.8%
untell 99%
thanks
Hi,
i need formula:
if m11=100% show 15%
if m11=51% show 7.5%
if m11=52% show 7.7%
if m11=53% show 7.8%
untell 99%
thanks
Good afternoon,
Cell A1 = 2 Sizes of mixed, ranging variable number.
(Example:&UA63%38TL & E1D&HX1)They change radically inside there 10 and 7 digit format respectively. Tracking each number is impossible, but the total number of digits remain constant; 10 and 7)
I need to get a True/False for a cell with a specific digit count in a cell.
I hope that was clear enough to understand.
Thank you!
hi
i am making price list
i need to do in b column if number is same in b column so in in k column result will be plus in l column
exam
1 A B --------- K L
2 1 --------- 25.50 38.25
3 1 --------- 12.75
4 2 --------- 05.75 17.20
5 2 --------- 11.45
SO WHAT SHOULD I WRITE IN L COLUMN....
Hi,
I am sorry, your data look distorted in the comment above. If you can send us a small sample workbook with your source data and the result you want to get, we'll be able to help you better.
Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.
Thank you.
thanks you so much for reply Ekaterina Bespalaya
hi
i am making price list
i need to do in b column if number is same in b column so in in k column result will be plus in l column
exam
1 A B --------- K L
2 1 --------- 25.50 38.25
3 1 --------- 12.75
4 2 --------- 05.75 17.20
5 2 --------- 11.45
SO WHAT SHOULD I WRITE IN L COLUMN....
hi
i am making price list
i need to do in b column if number is same in b column so in in k column result will be plus in l column
exam
1 A B --------- K L
2 1 --------- 25.50 38.25
3 1 --------- 12.75
4 2 --------- 05.75 17.20
5 2 --------- 11.45
SO WHAT SHOULD I WRITE IN L COLUMN....
if I want to find if C1 is between 200 and -200) put "Lab" and if G2 is "PD25088" put "GEO"
Your question is unclear because the connection between the two outcomes is unclear. I can't figure out the logic.
However, the first part will require =IF(AND(C1-200), "LAB", "No LAB").
The second is =IF(G2="PD25088","GEO","No GEO").
I need formula in excel
in entire row if same number - count only 1 and other show as zero
111 - show as 1
123 - show as 1
124 - show as 1
124 - show as 0
125 - show as 1
125 - show as 0
126 - show as 1
125 - show as 0
i want to use that if condisation
like when i type company name it has to show the GST no.
i have so meany clients so thats way mail me or msg me how can i use that condisation 8197938637
Hi! How can I create a formula for the following?
=IF(H9="S4309","",IF(H9="S4310","",IF(H9="S4311","")=I9+365))
So, if cell H9 equals either S4309,S4310 or S4311 then cell K9 will be blank. Otherwise, it will populate an expiry date based on cell I9.
Thank you for your help!
I believe this is what you're looking for.
=IF(OR(H9="S4309", H9="S4310",H9="S4311"),"Blank",I9+365)
I put the word "Blank" in there to test it. You can use "". I formatted I9 as Date and H9 as Text.
Thank you for the lovely examples of using the IF() function. I'm going to use this for a lesson on teaching the IF() function.
Please ignore my first post, as I have worked out the formula, but realised I cant sum the values in the target Cell
So this what I am trying to do now.
If C27 = P then S27 = 1
If C27 = DO then S27 = 0
Is the above possible.
What is the best way to combine into one argument.
Thanks.
Looks as if this will need a nested IF formula. Ablebits has a good description of how to use these as well as similar conditions. Search "Nested IF formulas in Excel" here in Ablebits.