The IF function is one of the most popular and useful functions in Excel. You use an IF statement to ask Excel to test a condition and to return one value if the condition is met, and another value if the condition is not met.
In this tutorial, we are going to learn the syntax and common usages of Excel IF function, and then will have a closer look at formula examples that will hopefully prove helpful both to beginners and experienced Excel users.
Excel IF function  syntax and usage
The IF function is one of Excel's logical functions that evaluates a certain condition and returns the value you specify if the condition is TRUE, and another value if the condition is FALSE.
The syntax for Excel IF is as follows:
As you see, the IF function has 3 arguments, but only the first one is obligatory, the other two are optional.
 logical_test  a value or logical expression that can be either TRUE or FALSE. Required.In this argument, you can specify a text value, date, number, or any comparison operator.
For example, your logical test can be expressed as or B1="sold", B1<12/1/2014, B1=10 or B1>10.
 value_if_true  the value to return when the logical test evaluates to TRUE, i.e. if the condition is met. Optional.
For example, the following formula will return the text "Good" if a value in cell B1 is greater than 10:
=IF(B1>10, "Good")
 value_if_false  the value to be returned if the logical test evaluates to FALSE, i.e. if the condition is not met. Optional.
For example, if you add "Bad" as the third parameter to the above formula, it will return the text "Good" if a value in cell B1 is greater than 10, otherwise, it will return "Bad":
=IF(B1>10, "Good", "Bad")
Excel IF function  things to remember!
Though the last two parameters of the IF function are optional, your formula may produce unexpected results if you don't know the underlying logic beneath the hood.
 If value_if_true is omitted
If the value_if_true argument is omitted in your Excel IF formula (i.e. there is only a comma following logical_test), the IF function returns zero (0) when the condition is met. Here is an example of such a formula:
=IF(B1>10,, "Bad")
In case you don't want your Excel IF statement to display any value when the condition is met, enter double quotes ("") in the second parameter, like this:
=IF(B1>10, "", "Bad")
. Technically, in this case the formula returns an empty string, which is invisible to the user but perceivable to other Excel functions.The following screenshot demonstrates the above approaches in action, and the second one seems to be more sensible:
 If value_if_false is omitted
If you don't care what happens when the specified condition is not met, you can omit the 3rd parameter in your Excel IF formulas, which will result in the following.
If the logical test evaluates to FALSE and the
value_if_false
parameter is omitted (there is just a closing bracket after thevalue_if_true
argument), the IF function returns the logical value FALSE. It's a bit unexpected, isn't it? Here is an example of such a formula:=IF(B1>10, "Good")
Putting a comma after the value_if_true argument forces your IF statement to return 0, which doesn't make much sense either:
=IF(B1>10, "Good",)
And again, the most reasonable approach is to put "" in the third argument, in this case you will have empty cells when the condition is not met:
=IF(B1>10, "Good", "")
 Get the IF function to display logical values TRUE or FALSE
For your Excel IF formula to display the logical values TRUE and FALSE when the specified condition is met and not met, respectively, type TRUE in the
value_if_true
argument. Thevalue_if_false
parameter can be FALSE or omitted. Here's a formula example:=IF(B1>10, TRUE, FALSE)
or
=IF(B1>10, TRUE)
Note. For your Excel IF statement to return TRUE and FALSE as the logical values (Boolean values) that other Excel formulas can recognize, make sure you don't enclose them in double quotes. A visual indication of a Boolean is middle align in a cell, as you see in the screenshot above.If you want "TRUE" and "FALSE" to be usual text values, enclose them in "double quotes". In this case, the returned values will be aligned left and formatted as General. No Excel formula will recognize such "TRUE" and "FALSE" text as logical values.
 IF statement to perform a math operation and return a result
Instead of returning certain values, you can get your IF formula to test the specified condition, perform a corresponding math operation and return a value based on the result. You do this by using arithmetic operators or other Excel functions in the
value_if_true
and /orvalue_if_false
arguments. Here are just a couple of formula examples:Example 1:
=IF(A1>B1, C3*10, C3*5)
The formula compares the values in cells A1 and B1, and if A1 is greater than B1, it multiplies the value in cell C3 by 10, by 5 otherwise.
Example 2:
=IF(A1<>B1, SUM(A1:D1), "")
The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the formula returns the sum of values in cells A1:D1, an empty string otherwise.
Using the IF function in Excel  formula examples
Now that you are familiar with the Excel IF function's syntax, let's look at some formula examples and learn how to use IF as a worksheet function in Excel.
Excel IF statement for numbers: greater than, less than, equal to
The use of the IF function with numeric values is based on using different comparison operators to express your conditions. You will find the full list of logical operators illustrated with formula examples in the table below.
Condition  Operator  Formula Example  Description 
Greater than  >  =IF(A2>5, "OK",) 
If the number in cell A2 is greater than 5, the formula returns "OK"; otherwise 0 is returned. 
Less than  <  =IF(A2<5, "OK", "") 
If the number in cell A2 is less than 5, the formula returns "OK"; an empty string otherwise. 
Equal to  =  =IF(A2=5, "OK", "Wrong number") 
If the number in cell A2 is equal to 5, the formula returns "OK"; otherwise the function displays "Wrong number". 
Not equal to  <>  =IF(A2<>5, "Wrong number", "OK") 
If the number in cell A2 is not equal to 5, the formula returns "Wrong number "; otherwise  "OK". 
Greater than or equal to  >=  =IF(A2>=5, "OK", "Poor") 
If the number in cell A2 is greater than or equal to 5, the formula returns "OK"; otherwise  "Poor". 
Less than or equal to  <=  =IF(A2<=5, "OK", "") 
If the number in cell A2 is less than or equal to 5, the formula returns "OK"; an empty string otherwise. 
The screenshot below demonstrates the IF formula with the "Greater than or equal to" logical operator in action:
Excel IF function examples for text values
Generally, you write an Excel if statement with text using either "equal to" or "not equal to" operator, as demonstrated in a couple of IF examples that follow.
Example 1. Caseinsensitive IF formula for text values
Like the overwhelming majority of Excel functions, IF is caseinsensitive by default. What it means for you is that logical tests for text values do not recognize case in usual IF formulas.
For example, the following IF formula returns either "Yes" or "No" based on the "Delivery Status" (column C):
=IF(C2="delivered", "No", "Yes")
Translated into the plain English, the formula tells Excel to return "No" if a cell in column C contains the word "Delivered", otherwise return "Yes". At that, it does not really matter how you type the word "Delivered" in the logical_test argument  "delivered", "Delivered", or "DELIVERED". Nor does it matter whether the word "Delivered" is in lowercase or uppercase in the source table, as illustrated in the screenshot below.
Another way to achieve exactly the same result is to use the "not equal to" operator and swap the value_if_true and value_if_false arguments:
=IF(C2<>"delivered", "Yes", "No")
Example 2. Casesensitive IF formula for text values
If you want a casesensitive logical test, use the IF function in combination with EXACT that compares two text strings and returns TRUE if the strings are exactly the same, otherwise it returns FALSE. The EXACT functions is casesensitive, though it ignores formatting differences.
You use IF with EXACT in this way:
=IF(EXACT(C2,"DELIVERED"), "No", "Yes")
Where C is the column to which your logical test applies and "DELIVERED" is the casesensitive text value that needs to be matched exactly.
Naturally, you can also use a cell reference rather than a text value in the 2^{nd} argument of the EXACT function, if you want to.
Example 3. Excel if statement for text with partial match
If you want to base your condition on a partial match rather than exact match, an immediate solution that comes to mind is using wildcard characters (* or ?) in the logical_test argument. However, this simple and obvious approach won't work. Many Excel functions accept wildcards, but regrettably IF is not one of them.
A solution is to use IF in combination with ISNUMBER and SEARCH (caseinsensitive) or FIND (casesensitive) functions.
For example, if No action is required both for "Delivered" and "Out for delivery" items, the following formula will work a treat:
=IF(ISNUMBER(SEARCH("deliv",C2)), "No", "Yes")
We've used the SEARCH function in the above formula since a caseinsensitive match suits better for our data. If you want a casesensitive match, simply replace SEARCH with FIND in this way:
Excel IF formula examples for dates
At first sight, it may seem that IF formulas for dates are identical to IF statements for numeric and text values that we've just discussed. Regrettably, it is not so.
Unlike many other Excel functions, IF cannot recognize dates and interprets them as mere text strings, which is why you cannot express your logical test simply as >"11/19/2014" or >11/19/2014. Neither of the above arguments is correct, alas.
Example 1. IF formulas for dates with DATEVALUE function
To make the Excel IF function recognize a date in your logical test as a date, you have to wrap it in the DATEVALUE function, like this DATEVALUE("11/19/2014"). The complete IF formula may take the following shape:
=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")
As illustrated in the screenshot below, this IF formula evaluates the dates in column C and returns "Completed" if a game was played before Nov11. Otherwise, the formula returns "Coming soon".
Example 2. IF formulas with TODAY() function
In case you base your condition on the current date, you can use the TODAY() function in the logical_test argument of your IF formula. For example:
=IF(C2<DATEVALUE("11/19/2014"), "Completed", "Coming soon")
Naturally, the Excel IF function can understand more complex logical tests, as demonstrated in the next example.
Example 3. Advanced IF formulas for future and past dates
Suppose, you want to mark only the dates that occur in more than 30 days from now. In this case, you can express the logical_test argument as A2TODAY()>30. The complete IF formula may be as follows:
=IF(A2TODAY()>30, "Future date", "")
To point out past dates that occurred more than 30 days ago, you can use the following IF formula:
=IF(TODAY()A2>30, "Past date", "")
If you want to have both indications in one column, you will need to use a nested IF function like this:
=IF(A2TODAY()>30, "Future date", IF(TODAY()A2>30, "Past date", ""))
Excel IF examples for blank, nonblank cells
If you want to somehow mark your data based on a certain cell(s) being empty or not empty, you can either:
 Use the Excel IF function in conjunction with ISBLANK, or
 Use the logical expressions ="" (equal to blank) or <>"" (not equal to blank).
The table below explains the difference between these two approaches and provides formula example.
Logical test  Description  Formula Example  
Blank cells  =""  Evaluates to TRUE if a specified cell is visually empty, including cells with zero length strings.
Otherwise, evaluates to FALSE. 
=IF(A1="", 0, 1)
Returns 0 if A1 is visually blank. Otherwise returns 1. If A1 contains an empty string, the formula returns 0. 
ISBLANK()  Evaluates to TRUE is a specified cell contains absolutely nothing  no formula, no empty string returned by some other formula.
Otherwise, evaluates to FALSE. 
=IF(ISBLANK(A1), 0, 1)
Returns the results identical to the above formula but treats cells with zero length strings as nonblank cells. That is, if A1 contains an empty string, the formula returns 1. 

Nonblank cells  <>""  Evaluates to TRUE if a specified cell contains some data. Otherwise, evaluates to FALSE.
Cells with zero length strings are considered blank. 
=IF(A1<>"", 1, 0)
Returns 1 if A1 is nonblank; otherwise returns 0. If A1 contains an empty string, the formula returns 0. 
ISBLANK()=FALSE  Evaluates to TRUE if a specified cell is not empty. Otherwise, evaluates to FALSE.
Cells with zero length strings are considered nonblank. 
=IF(ISBLANK(A1)=FALSE, 0, 1)
Works the same as the above formula, but returns 1 if A1 contains an empty string. 
The following example demonstrates blank / nonblank logical test in action.
Suppose, you have a date in column C only if a corresponding game (column B) was played. Then, you can use either of the following IF formulas to mark completed games:
=IF($C2<>"", "Completed", "")
=IF(ISBLANK($C2)=FALSE, "Completed", "")
Since there are no zerolength strings in our table, both formulas will return identical results:
Hopefully, the above examples have helped you understand the general logic of the IF function. In practice, however, you would often want a single IF formula to check multiple conditions, and our next article will show you how to tackle this task. In addition, we will also explore nested IF functions, array IF formulas, IFEFFOR and IFNA functions and more. Please stay tuned and thank you for reading!
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.
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.
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.
=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,)))))
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?
Thank you. This article helped me alot.
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?
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)
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 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
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.
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
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:
=(O13N13)/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, (20162015)/2016.
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
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.
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'm having trouble with this;
if a cell shows Jul18 (or another month) then do show a figure in another cell
I've tried =if(d1=datevalue("01.07.1831.07.18"),D36,0)
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
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?
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 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!
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!
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.
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!
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(G28F28>0.208333333333333,G28F280.0208333333333333,G28F28)) but i cant seem to work it any help would be great
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.
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<U159100,"", 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 U159100, 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 U159100), 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<AA159100,"",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.
hi i want to insert formula excluding 0 but including number 1 to 4 ....plz help
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??
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
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
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 80042completed
Column C
Yes
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 80042completed
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 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.
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 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.
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, 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?
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.
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 !
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<AA159100,"",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 try to figure out a formula
if A1:A15=A and B1:B15=750 , C,D
C is value from the other sheet
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 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...
What formula/function should be used on number say
if 123456789 then SSN and if 66666666 the EIN
SQL:
What do you want to do with this data?
100 direct 70 Indirect
if direct method shows 100 value than 70 shows indirect method ?kindly help me
we need a more clear formula for this function
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
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)
SUB_LEVEL IN_DATE WORK_DESCRIPTION STATUS OUT_DATE
A 17072018 SKELETON MAKING #VALUE! 18072018
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",""))
=IF(A4="GP","3",IF(A4="DMF","4",IF(A4="CARD","5",IF(A4="ORTHO","6"))))
IF(A12.5,"1500")))). CAN YOU PLEASE GIVE ME CORRECT ONE...
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
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")))
Hello,
I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low"
Hello,
I want to create automatic calculation for impact assessment. It has 3 columns for criteria; A, B and C. So, when i enter criteria in these tree columns 4th column must automatically calculate date. for example: when enter A= local, B= temporary, c=medium, d column calculates "low". how can i enter the formula in d column?
Bayasaa:
I think this will work for you:
Enter this in D1:
=IF(AND(A1="Local",B1="Temporary",C1="Medium"),"Low","Not Low")
No I have never used either one of them before.
Oh and that table is 8 x 8
Up to 8 color prints and 8 price range
It’s excel online so I’m going to guess the most up to date one.. it just frustrating when I’m doing a quote at work that I have to keep looking at the sheet and there is more then one sheet like this so I wanted to put it in excel since that is where I build my quotes.
I’m looking to do a formula . Having trouble with it. What I’m looking for is pricing. If the total units is between 56 and 144 and it is a one color print the price would be .75 but if the units is between 145 and 200 and it is a one color print the price would be 1.00. Can anyone help me with this?
It’s like a table 1 color print between 36 and 66 is .72
1 color print between 66 and 144 is .97
2 color print between 36 and 66 is .97
2 color print between 66 and 144 is 1.15
And it keeps going but I would like to populate the amount in one cell. So if I had a 3 color print and it was between 68 and 145 the price would be 1.15
But if I had a 2 color from and it was between 0 to 68 then it would be 1.20
I need help in figuring out how to write a formula:
I have four rows and twelve columns for my spreadsheet.
Two of the rows will have whole numbers manually entered by me and my coworkers.
I want to write formulas for the other two rows to do automatic calculations based on the data in the other two rows.
Example:
If the value of the data in cell A of row four is less than or equal to the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row four.
But, if the value of the data in Cell A of row four is greater than the value of the data in cell A of row one, then the value of the data in cell A of row two needs to equal the value of the data in cell A of row one.
Numerical Example:
Row 1 Cell A we manually enter the number 34
Row 4 Cell A we manually enter the number 52
Because (Row 4 Cell A) 52 is greater than (Row 1 Cell A)34, the value that should appear in Row 2 Cell A is (The value that is in Row 1 Cell A) 34
or
Row 1 Cell A we manually enter the number 42
Row 4 Cell A we manually enter the number 36
Because (Row 4 Cell A) 36 is less than (Row 1 Cell A) 42, the value that should appear in Row 2 Cell A is (The value that is in Row 4 Cell A) 36
Once I figure out how to write this formula, I know how to write the formula for the cells in row three, which will be a simple subtraction formula.
Can you help me please?
Hi,
I'm curious if it's possible to use an IF function to compare text values.
For instance, I download emails from one report then enter them in a cell next to the emails from an original report...I'd like to simply enter an IF function of some sort to compare them (as an automatic QC) rather than visually compare.
Any help would be greatly appreciated!
Hi,
Below spreadsheet shows columns AD and rows 19.
I require a formula that states if A1 is blank & D1 contains text, insert a "B" in column A1 or leave existing values. Thanks
A B C D
1 3 K240 K240  EARTHWORKS
2 B K250 K250  Clearing and grubbing
3 K260 K260  (a) Soft excavation
4 3 K270 K270  BASES 1  50
5 K280 K280  Blinding
6 K290 K290  Reinforcing
7 K300 K300  Formwork to sides of Bases
8 K310 K310  Concrete to Bases
9 K320 K320  Formwork to sides of plinths
IF I WANT GREATER THAN 10 DIGITS AND LESS THAN 10 DIGIT NUMBER IN ONE CELL ?
HOW TO APPLY FORMULA
if a number 15 digits is correct but less then 15 digits number is wrong how to work out this
Rajesh:
If I understand your question you want to create an IF statement that checks the number of digits in a cell and returns one thing if it does and another thing if it does not. If that's what you're looking for the formula is: =IF(LEN(D35)=15,"Yes","No")
This is for a digit count of exactly 15. Any digits more or less will return "No".
Is that what you want?
Hi.
How can I make a formula who can do this
content of cell A1 is 1 then display content of cell D1
and if content A1 is 2 then display content of D2 etc.
=IF(A1=1;D1) this works but only for A1=1 how can I put varios
formulas together
=IF A1=1 then D1, IF A1= 2 then D2 this one does not work.
Thanks Rudy.
Rudy:
Are 1 and 2 the only values that A1 can hold?
If so, would =IF(A1=1,D1,D2) work? It says, if A1 equals 1 then display D1 otherwise display D2.
Logically, I want an excel function to check the value of week number and return the text string from one of Quarter1, Quarter2, Quarter3, Quarter4.
For this, I'm trying to IF function to return the text string.
The formula that I have used is 
(NoteA3 holds the value of week number.)
=IF(A3=13=25=37,"Q4","Please Check"))))
The IF function works fine for week number values less than 13.
However, for values 13 and above, it returns the text string Please Check.
Kindly help diagnose the problem. And propose if any other function can be used to achieve the same result in a simplified way!
Samrat:
I'm not sure what you're looking for, but it might be this.
=IF(A3=13,"Q1",IF(A3=25,"Q2",IF(A3=37,"Q3",IF(A3=56,"Q4","Please Check"))))
A3 will have to hold these numbers for the Q value to be displayed otherwise the cell will display "Please Check".
Any help would be appreciated. I cannot figure out how to make this work.
If C5 = Y and D5 = Y then E5 = L. If C5 = N and D5 = N then E5 = H. If C5 = Y and D5 = N then E5 = M. If C5 = N and D5 = Y then E5 = M.
Thanks in advance
I am trying to create a tracking form for specific tasks with SLA's. In one cell, I have a specific task and in another cell the SLA say is "2" days.
I am trying to get the SLA's to populate when selecting a certain tasks.
Any help you can provide will be greatly appreciated.
Thanks
Hi i am lookin to use the function below but it isnt working.
=IF(B2 = I2,"YES","NO")
Basically both cells contain text,
So for example B2 would read "Dispatched" and I2 would read "Delivered" and the forumla would return the word YES
If B2 would read "Dispatched" and I2 also would read "Dispatched" then the cell would return NO
Its basically to see if the status of the booking has changed overnight and would be filtered to yer or no?
Thank you for any advise or help?
I would like to ask a question :
I have 4 text alternatives like ( Apple, Orange, Banana and pear).
I want excel to check a cell and if that cell contains one of them do different sum calculation. for instance if it is apple sum up C1 and D5 and E3 and if it is orange sum C2 and D3 and E1 and put this sum in cell F1
how can I do this ?