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 38. Total comments: 4822
Hi,
This is the criteria :
If assets age is..., then charge over the cost:
=1 year = 75%
>=2 years = 65%
>=3 years = 55%
>=4 years = 45%
>=5 years = 35%
>=6 years = 25%
>=7 years = 20%
Lets say :
A (List of Asset) = Motor (A1); Car (A2); Truck (A3)
B (List of Cost) = RM10,000 (B1); RM50,000 (B2); RM80,000 (B3)
C (List of Purchase Date) = 1/1/2015 (C1); 1/1/2016 (C2); 1/1/2017 (C3)
D (Disposal Date) = 1/1/2019 (D1; D2; D3)
E (Results) = what is the formula for this?
Good day,
I need to write a formula to say that if 15:10:06 it greater than 15:00:00 the make it 15:00:00 and if the value is less then leave it as is,
eg. 15:10:06 to 15:00:00
eg. 14:45:51 leave as is
Hi, I am trying to read excel cells, and want to write an if statement which recognizes whether the cell contains a numerical value or text (#N/A,#DIV/0!) and return specific values in return -
1 - if the cell contains numeric value - 4500, if statement should return numeric value 4500
2 - only if the cell contains this specific text - "#N/A", if statement should return text "NoASP"
3 - only if the cell contains this specific text - "#DIV/0!", if statement should return text "MissingASP"
I tried various formulas, for example - =IF(EXACT(A3,"#N/A"), "NoASP", A3), but this works only if I add in the cell containing the values #N/A with apostrophe or single quote like this ('#N/A), Changing the cell format to text didn't work for me.
Can anyone please help.
I need a formula where I can take todays date in one cell and in another cell if it is not three days after todays date an error pops up says invalid entry.
Hi,
I am trying to use a IF command, ie., =IF(B4=1, "10:00", IF(B4=2, "8:00", IF(B4=3, "8:27","0:00"))). Though i am getting the the result correct, i am not able to do the calculation on the hours (10:00) which is showing as text. How can i get the above result converted in hours.
santhosh
I'm trying to take multiple text in different cells and want them to shows as 1 specific text.
I need to convert Time To Employee shift in excel
Ex. 06:00 To 07:00 A shift
14:00 to 22:00 B shift
22:00 to 06:00 C Shift
In this middle time suppose to some employee will come 06:015 and leave the company 14:15 so i need to count Ashift .
Any Body Help
=if(60=>60)
Hi,
Please help on following
if column A is equal to 0 than multiply column B1 X column C1
date format having in a specific cell, it will indicate 0. If date not mention, the result will be 1. how can we get result like this using if function.
can anyone pls reply asap
I am looking for the formula for the following:
-if cell A1 has "Always" in it, I want cell B1 to reflect 2
-if cell A1 has "Sometimes" in it, I want cell B1 to reflect 1
-if cell A1 has "Never" in it, I want cell B1 to reflect 0
Please help!
Hi there!
I am trying to write an IF statement. If Override Date is blank, then use the value in Creation Date to populate Actual Date. If Override Date is not blank, then use the value in Override Date to populate Actual Date. Here's what I have thus far: IF({Override Date}="",{Creation Date},{Actual Date},IF{Override Date}"",{Override Date},{Actual Date})
Thoughts?
Hi
I'm hoping you can help, I cannot upload data as it is all sensitive personal info, but I need to cross reference hours lost due to mental health and none of my attempts are providing satisfactory results. I have several worksheets in my spreadsheet breaking down cases by region e.g. 1,2,3 etc. I have each case laid out per employee within their region tab and a column lists their hours with another denoting "yes" or "no" for mental health. What I need to do is pick up anything 'Yes' for mental health with the number in their hours so I can work out how many hours we lost per week of absence. They are set up like "Region 1!Q:Q" for yes/no and "Region 1!G:G" for the hours.
Is this possible? Thanks.
need help
Column 1 Column 2 Points
OS and OS =5
OS and VS =3.5
VS and OS =3.5
VS and VS =2
what formula will i use?
=IF(B5B4, " ", "NO CHARGE") This formula works perfectly however, my problem is that if the B5 cell and the B4 cell are empty, it will also say "NO CHARGE" which is not what i want. Instead I want the formula to roughly be able to do this:
"If B5 is equal to B4 (the cell directly above) the current cell will execute 'NO CHARGE', otherwise it will execute nothing. But if both cells are empty, it will also execute nothing, instead of still executing 'NO CHARGE' because both cells are technically still equal to each other because they are empty."
Could you please help me with a formula that solves this issue?
I wrote this formula =($K4+$J4)/2 and is giving me #value, but when I put value in the cell feeding either of K4 or J4 it come out well
Dear Genius,
I need your help for Drivers Salary calculation for distance base as below.
Km 1 to 10 5 US$
Km 11 to 20 8 US$
Km 21 to 30 10 US$
Km 31 to 40 12 US$
Km 41 to 50 15 US$
Km 51 to 60 20 US$
Km 61 to 70 25 US$
Please do favor
=IF(D5=“FALSE”,EOMONTH(F5,0),DATE(N57),0)
***Cell no (N57) = write an another date
Pls above conditions are not properly plz send me correct conditions
This is excellent blog with very good information. Thank You.
I am trying to do a function which will help me with this issue:
If text in cell A matches a text in a cell in a list in column B then import from the next cell in the list C (same as if you were to use Sumif but with text only!)
I want to add today's date to a cell (F9) if cell F12 is populated. If it is blank, I want F9 to stay blank. Not working with what I've tried. Please help. thanks
=IF(F9=" ",(" "),(DATE(TODAY(),TODAY(),1))) - is not correct. Giving me NUM#
Hi Team,
If "formula" Can we use with particular cell in name sppose "punit12&₹" This name i have english alphabets,numbers with special character so usse "if" Formula is "count" Other "No" In No candition I am ussing name with hindi character or chainse character other etc character
Pls do the needful
Regards
Punit
+91 7065751520
Hi
Please assist with formula: If date cell in sheet 1 is the same date from a column of dates in sheet 2, copy data in the respective row in sheet 2
Thank you
Beth
Our appraisal office is looking to create an excel spreadsheet to manage the amount of assignments we can accept. I was hoping to create an IF function for a date only if we type "YES" in the column. I have tried but was not successful.
Ex. Date Given to us 10/08/19 ---> Inspected? YES ---> Due Date would be the IF function 10/08/19+2 business days, if successful it should automatically input 10/10/19.
Is this possible?
I want actual value(obtained Data) in a cell but if value become 0 so want to my cell show <10
so what i do
How i shall formulate if PASS,FAIL by making statements from text such as P/A which indicates as present and absent
Hi,
I read through the comments but not sure if a similar type of question asked.
What function is best used if I have a list of items which will be answered Yes/No. I want a cell to state yes if all the cells are ticked as yes, if not all of them is yes (even if only one is no) that cell should say No.
Currently I am trying the IF function but just struggling to get it working.
Thanks for your assistance.
Hi
I need a formula if cell A2 = any cell in column c it will copy text contained cell d2
Thanks
Can you help me with this formula:
i have 3 coloums A,B,C
A. Sl No
B. Name
C Code
in this if i enter name in B Cell is it possible to pic automatically code in C Cell. please help me.
Hello, I am trying to use IF to help auto populate a sheet. I have 12 sheets, what I want to do is put a sheet name in the reference cell and have a cell from that sheet displayed. Example in A1 sheet name. B1 'sheet name'!B3.
Two thigs i have tryed
=IF(OR(A1=421,”’421’!BE”,””,OR(A1=424,”’424’!B3,””,OR( A1=429,”’429’!B3”,””))))
=IF(OR(A1=421,A1=424,A1=429),”’A1’!B3”,””)
I need help with....insert an IF function in cell F5 that shows "PASS" if greater than 59 and "FAIL" if the requirement is not met.
Hi,
I need your help with the following formula please.
If A1 is greater than B1, then C1 should show the value typed in A1, and if B1 is greater than A1, C1 should show the value in B1.
=IF(A1>B1,"A1","B1") or =IF(A1>B1,"A1",IF(A1<B1,"B1")) When I do it, it shows the name of the cell and not the value :(
Thank you in advance
Wow. This is exactly what I've been looking for. The "Excel if statement for text with partial match" is what I have been trying to do for a day now at work and finally I found this. You made my life so much easier. Thank you!
Hi
I'm trying to write a formula to read every cell in each row and replace the value if its bigger than 1 with the first value in the row ( each row has a different value in the first cell ) , should i use conditional formatting ? if yes how should i write the formula ?
I need a formula that would copy the text entered in cell C to cel D only when cell A or B is marked with 'x'? Blank entries in cell C would also be skipped is that possible?
Hello,
I need to search two separate text in one column to return "Yes" of "No". What am I doing wrong I this formula?
IF(AND(ISNUMBER(SEARCH("welcome",F2)("Good",F2)),F3>10),"Yes","No")
WHAT FORMULA CAN I use to look for the VALUE in cell "A2" is equal to CELL VALUE IN "I14" in a different sheet - but I want it to do a search in multiple sheets (75 to be exact) then SEARCH IN MULTIPLE SHEETS and if there is a match to "A2" return back WITH THE VALUE IN CELL "L34" of the sheet were the value was found.
I m using if conditions below
=If(A1<=1000,"0",IF(A2001,"80")))
BUT WHEN I USE SUN FUNCTION TO GRAND TOTAL, AMOUNT SHOWING ONLY '0'
KINDLY ADVISE AND HELP PLEASE
WHAT FORMULA CAN I use to look for the VALUE in cell 'A2" is equal to CELL VALUE IN "I14" in a different sheet - but I want it to do a search in multiple sheets (75 to be exact) then SEARCH IN MULTIPLE SHEETS and if there is a match to "A2" return back WITH THE VALUE IN CELL L34 of the sheet were the value was found.
hi please cou;d you tell me where I am going wrong:
I need a formula for the following:
column O2 contains a due date, column P2 contains completed status of Yes or No. I need to add a status of overdue or Due within 90 days.
so if the date in column O2 is over 90 days from today then the status is "Overdue" but if the date in column O2 is within 90 days from today then the status needs to be "Due within 90 days"
I need a formula to calculate a value from a group of numbers in a column:
Column A - has dollar figures
Column B - has dates WHEN the amount in column A is paid.
I want to calculate the unpaid amounts from column A when column B doesn't have a date entered or a blank cell.
hello iam sandip mere ko aisa formate make karna he exm:-90 he to 75 ke above 10 ana chahiye 81 ke above 15 86 ke above 20 91 ke above 25 ager 75 ke below rahega to 0 ana chiye mene ye formate banaya he mager ye kam nahi kar rha he,
=IF(C4>=76,"10",IF(C4>=81,"15",IF(C4>=86,"20",IF(C4>=91,"25",IF(C4<=75,"0")))))
please help me my whatsapp no.8669800963
I am trying to write a formula to distinguish between if a serial # starts with a Letter or a Number then multiply by two different % rates . Any help would be great.
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.
How to enter some number in another cell according to ented number in first cell. ie. If 1 is enterd in column 1st then atomatically 300 (rate per kg) is enterd in money column. Please answer quickly. Thankyou for readin and answering me.
Hi All
Can you please help me with a formula? I need to create a monthly report.
Reporting period is from 26th previous month to 25th of the current month for example for May 2019 reporting commence from 26th April to 25th May.
Can you help? Many thanks in advance.
Jonathan
Hi There,
Please help me to come up with the formula i needed.
In a column, if there is a value encoded the result must be 8 or 0 only.
How is it?
Thanks a lot.
Hello,
I'm trying to condition a cell (A) color based on 4 other cells (B,C,D,E).
The first one is a case number and the 4 cells are attempts to reach a customer via phone.
So, if a first call is made and the customer is not reached, B cell will contain text, A cell will change to yellow.
When a second call attempt will be made, C cell will contain text and A cell will need to change to orange.
After the third call attempt is made, D cell will contain text and A cell will need to change to red.
If the customer is reached at any given point (1st,2nd,3rd attempt), E cell will change to value (Y) and A cell will need to change to green.
Can you help with this?
Thank you
Hello Team
How to find the TP value with IF condition by using TYPE.
TYPE ENTRY PRICE TP
Sell 1.34365 1.34165
Buy 0.69792 0.69992
Please help me with this.
Thanks
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)