The IF function is one of the most popular and useful functions in Excel. You use the IF function 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")
If you don't want your IF formula 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 if 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")
If you put a comma after the value_if_true argument, your IF function will returns 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
If you want 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. If you want your IF formula to return TRUE and FALSE as the logical values (Boolean) 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 to "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.
 Get IF to perform a math operation and return a result
Instead of returning certain values, you can make 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.
IF function examples 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 IF formula for text values 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. IF formula for text values 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 functions 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 to 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!
=IF(A2=5, "OK", "Wrong number")
BUT I WANT TO VALUE OF A1 CELL AT "Wrong number" POSITION
Hi!
If my understanding is correct, you simply need to replace "Wrong number" with A1 like this:
=IF(A2=5, "OK", A1)
The formula returns "OK" if A2 is equal to 5, otherwise it picks a value from cell A1.
If you want something different, please clarify.
Hi, I want to enter a formula that will give me a result only if there is a value > 0 in a row
i.e. if A1=0 then give me B1. But if B1=0 then give me B2 etc.
Can anyone help? I've got this so far, but it just gives me 0:
=IF(R2=0,S2,IF(S2=0,T2))
=IF(OR(A1=0,B1,B1=0),B2) u try this.
=IF(OR(A1=0,B1),IF(B1=0,B2))
I have 2 cells within Excel that I would like to apply the if function to, but I am not sure how to setup it up properly. Right now it reads:
=IF(T11=R13,"Ok","Check Total")
But, I want the formula to take in account if T11 is less than or greater than R13 by "5 units or 5 increments" to indicate "OK" not just equals to. How would I setup my formula?
Thank you in advance!
I have 2 cells within Excel that I would like to apply the if function to, but I am not sure how to setup it up properly. Right now it reads:
=IF(T11=R13,"Ok","Check Total")
But, I want the formula to take in account if T11 is less than or greater than R13 by "5 units or 5 increments" to indicate "OK" not just equals to. How would I setup my formula?
Thank you in advance!
Check Below Formula Once
=IF(OR(T11R13),"5units/5increments","Ok")
=IF(OR(T11R13),"","")
How do I get to extrapolate for if A#=0, B# without just typing =IF(OR(A1=0,B1),IF(A2=0,B2),IF(A3=0,B3),IF(A4=0,B4)) and on and on
=if(L4,"Half Yearly,180,If(L4,"Quarterly",90,if(L4,"Yearly",365)))
I work at a school, and i manage the book shop over there, so i want to keep my record straight for my boss, i forget things so fast :) and also my purchase record, thats why i want to set a formula to fastly enter. i deal with notebooks, diaries and artpads, so I want a formula for text and numbers both, for example if i write 'D' in a cell, the formula will write the amount of '55' for a Diary, and so on for notebook, i mean if i write 'N' for notebook, the formula will write the amount '50' for me. I just want to set that formula. So i can deal with other customers also.
=IF(A2="D","55",IF(A2="N","50"))
:)
=IF(OR(A1=0,B10),B1,B2)
This is your solution mate :)
enjoy it
if only you'd be my professor
IF A2's value = O then take date from B2 And IF A2's value = D then take date from C2 And IF A2's value = K then take date from D2
Hello AJAY,
Here you go:
=IF(A2="O",B2,IF(A2="D",C2,IF(A2="K",D2,"")))
It worked! Thank you Svetlana
If in place of Date in B2 there is alphanumeric text, then?
Hello Arnab,
It does not make any difference, the formula pulls any value from B2.
i need to find the maximum value and to show its time from the column of time. each value has got its specific time which is on the same row but different columns.
A formula please.
Thank You in Advance.
Hello Japhetmunnah,
This can be done using a VLOOKUP formula, like this:
=VLOOKUP(MAX(A1:A10), A1:B10, 2)
Where A is the column where you look up for max value, and B is the column containing times, 2 is the column number (col_index_num) in table array from which the value should be returned.
Do you help me? How to create tables in excel?
=IF((Data!$D$11="Concrete"),SUM((SUM($B$40*9)/1.3)*0.865/Data!$B$26),SUM((SUM($B$40*9)/1.3)/Data!$B$26))
I am trying to use the above if statement to calculate a value based on the value selected in Data!D11. The choices in a drop down D11 are Concrete or Frame. My logic being if D11=Concrete then do the first formula but if false (Frame) do the second formula. When Concrete is put into D11 this works correctly but if Frame is selected the value is not correct. I can put the second false formula in another cell and it calculates correctly. Can someone tell me how to fix this.
Rick
I am needing the cell in sheet two to = a cell in sheet one. it is a date. But if there is not a date in sheet one,yet, I want the cell in sheet two to show as blank/empty and then when the date is put in the cell in sheet one i want it to show the date in sheet two.
can you please help?
I'm trying to figure out a formula that will compare two columns "dates" and then produce the greater value of the two columns in the third column. Can you please help?
Hi Shannon,
Assuming that you have dates in columns A and B, the formula is as follows:
=IF(A1>B1, A1, B1)
Hi,
I Have a Data My software needs to work 99.99% availability when measured on Monthly basis If the downtime exceeds 60 min in a month for every 30 min Penalty should be calculated on Quarterly Payment of rs 10000 can u design a methodology with formula with calculated output.
Hi,
I read above that excel doesn't recognize dates unless wrapped in DATEVALUE.
I had the above formula that you gave and it doesn't always work. In my case one of my columns could possibly have a 0 or be blank.
My formula is in column A my date columns are D and G. So I had
=IF(G2>D2, G2,D2)
G is the column that sometimes could have a 0 or be blank (the data is pulled from another system.
Also, my formula doesn't recalculate once I update the data in column G. Any thoughts? I have auto calc turned on.
Could you help with this formula?
"J21" = "%" and "M21" = "%" "K18" is the result but the are 3 answer.
J21 is > than 0
M21 is > than 0
J21 & M21 are > than 0 And needs to be
/ by 2
Hope you follow what I am looking for
Thank You
Hi Gene,
I understand that you have some percentages in cells J21 and M21, but all the rest is befogged.
Can you please try to formulate the task in the following way?
If J21 >0, then we do what?
If M21 >0, then we do what?
If J21 and M21 >0, then we do what?
If J21%>0 then put % in K18 as that %
If M21%>0 then put % in. K18 as that %
If M21 & J21 %>0 then devied by 2 then put
That % in K18
Only 1 of the 3 formula's can be true at a
Time and it's % in put in K18
Hope this is better for you to see what I want
Thank you
Hi!
It's not quite clear which value shall be divided by 2 if both J21 and M21 are greater than 0. The following formula divides the sum of values in the above mentioned cells:
=IF(AND(J21>0, M21>0), (J21+M21)/2, IF(J21>0, J21, IF(M21>0, M21,"")))
If you are looking for something else, then replace (J21+M21)/2 in the formula with the right value.
Svetlana i am having a data which is update by team daily.
Now i want 3 things to get from that data
1:updated
2:Missed to Update
3:Coming soon
Hi Vivek,
Excel formulas are not able to respond to a data change. Probably this can be done by using a VBA macro.
Hi,
Is this function just for one by one ? how if i want all of the data from one column like i want the data of column B with more than 1000 will go for high, the range 500999 will go for medium and range 1500 to low. Do i need to copy the formula and paste it one by one or it can help me comes out everything in one time, i have 19k data from that column
As I understand, it should be like this
in cell(k18)=if(and(j21>0,m21>0),(j21+m21)/2,0)
in col write a formula to give rank to student based on below table(without using if condition)
marks:=33 but less than 60,>=60 but less than 70,>=70 but less than 80,>=80 but less than 90,
>=90
grades:= <33,"Fail",<60,"pass",<70,"3rd div",<80,"2nd div",90,"distinction
plz solve this question
Can you please look into the formule; where I am doing wrong...
=IF(SUM(T7:AE7)=N7, "GOOD", "Check again")
This formula is returning 'Check again' though the sum(T7:AE7)=N7
Regards,
Pavan
Forgot to mention...
N7 is a result of a formula (for eg: C19*I6)
Thanks,
Pavan
Hi Pavan,
I don't see anything wrong with the formula. I've done a couple of quick tests and it worked perfectly. Maybe it makes sense to double check the sum?
forgot to mention...
here, N7 is again result of some other formula (eg: C19*I6)
Thanks,
This does not really matter, your formula should work anyway. If against all expectations it doesn't, you can send your sample worksheet at support@ablebits.com and we will try to figure this thing out.
use:
IF(round(SUM(T7:AE7);2)=round(N7;2), "GOOD", "Check again")
or the amount of signs you actually need usually 2 is enough :)
Hi,
=IF(SUM(T7:AE7)<=N7,"Good","Ccheck Again")
If you Want use greater than or less than on that formulas you can use this try Once.
Thanks for your reply.
I will try again.
Thanks
Can you please look into the formula; where I am doing wrong...
IF(A1,B1,C1>=85%,"Tk 300",IF(A1,B1>=85%,C1=85%,B1=85%,A1=85%,B1,C1=85%,A1,C1=85%,A1,B1<85%,"Tk 100","0")))))))
How about: =IF(SUM(T7:AE7)=N7, 0, F5*H8). Is there an excel command to make the calculation in the second value work?
Hi Bob,
Your formula does work. If the sum in T7:AE7 is not equal to N7, the value in F5 is multiplied by H8. Or are you looking for something else?
i have 2 sheets, i want have the value 1 inputted on sheet two when/if it matches the date from sheet one. Basically i have set the work schedule up so that when an employee is put in a work spot, it adds 1 on the second sheet indicating the employee has work on that date. it's a daily schedule, so i am trying to automate the two sheets. so when i put someone to work it auto populates under the correct date on both sheets. if the are not working it should show 0
this is what i have so far, but not working...just shows false
=SUM(SUM(C2:C2)=1,IF('New daily  Table 1  Guelph'!D6,D6)='Days worked test draft'!O1,O1) ="1"
Column C2 shows if a person works or not, then the row to the right are the dates for the month
any help would be greatly appreciated
Hi Dome help with a formula
I have a weight of 450 in cell a1, i need a if formula to say if a1 greater than 470.1 but less than 500 then pick up value in cell a2 on this occasion .3.
can you help
thank you
Hi Stephen,
Here you go:
=IF(AND(A1>470, A1<500), A2, "")
Hi Svelana !!
I have two excel sheet containing a list of peoples, but there is some error e.g. font, spelling etc.
Pls help me to find the code of peoples from one sheet to other.
Hi Summy,
To find typos and other similar data that differ in 1 to 10 characters, you can use our Fuzzy Duplicate Finder addin:
https://www.ablebits.com/excelfindsimilar/index.php
If you want to locate font/color differences, more advanced tools are required. Please see the following post on our forum for more details:
https://www.ablebits.com/forums/topic/9344comparemergingworkbooksexcel2010/?p=11508
Hi
I want to put in an IF function that returns different text depending of the numerical value of the cell next to it. e.g. if the number is less than 43  "poor", 4350 "average", 5170 "above average" etc....
I know I have to set up some cells with the lower value of each range and the text next to each one (pivot table?) but I can't remember the formula for saying  if it is less that A1 then print "textA" etc..
Hello Fo,
You can use a nested If formula like this:
=IF(A2>=51, "above average", IF(A2>=43, "average", "Poor "))
You can find an example very close to your task as well as a few alternative formulas in this article  Using nested IF functions in Excel
yea
This was very informative, can you help with this formula please?
If Cells in E:E = value "YES" AND Cells in F:F date older than 6 months then calculate result of Cell in B:B  (minus) a number "6"
Hi Sami,
Excel cannot understand the "month duration" because the number of days in each month varies. So, here is the formula as per your conditions, except that dates in column F are older than 180 days rather than 6 months:
=IF(AND(E1="yes", TODAY()F1>180), B16, "")
Thank you for the formula, It did not work, I have the date sat with date value but the input just blank... if I leave F1 blank I get the results I need but, F1 "date" is important.
Hi I am looking for a forumlar to completed the following calculation in a cell:
If A1 > 200,000 cell = A1200000 but if A1>1,000,000 cell = 800,000
Please can you help me out with this formula
Hi Chris,
The following nested IF formula might work a treat:
=IF(A1>1000000, 800000, IF(A1>200000, A1200000, ""))
Could you help with this project?
If J21's % is > then 0 then put that % in K18
If M21's % is > then 0 then put that % in K18
But if J21 & M21 %'s are > then 0 then devied
By 2 then put the result in K18 as a %
Only 1 of the 3 formula's can be true at
a time
Thank you
Hi Gene,
The first 2 conditions are clear. As for "if J21 & M21 %'s are > 0", which value shall we divide by 2?
J21's % & M21's % are added together then devide by 2 to get a % that can be used
The this help
Here you go:
=IF(AND(J21>0, M21>0), (J21+M21)/2, IF(J21>0, J21, IF(M21>0, M21,"")))
hi Svetlana,
i have 3 dates and wish to use these dates for formula calculation, can you help me? all dates have some different rates applicable
I need to make a an if formula that will tell me yes if something matches but then also has a text and amount for if the match is not correct. So I need a =if(A1=B1,"Yes" and then if A1 doesn't = B1 for the formula to say "Difference noted of (A1B1)" to say how much the difference is while also having text in the same false side of the if function.
Can you help me?
Hi Zach,
You were almost there :)
=IF(A1=B1,"Yes","Difference noted of "&A1B1)
Thank you! Worked perfectly :)
That is great thank you Svetlana just what I needed
i need help with this formula
"if d4 equal 0 display 0, if d4 is greater than 0 but less than 9 display 1"
Hi Tahsa,
Here you are:
=IF(AND(D4>0,D4<9),1,IF(D4=0,0,""))
Thank you Svetlana.
This was extremly helpful
I need an If formula that will provide me with a value that is deterimed by the data validations that are selected contain different text selections:
for example: If cell D2 that has been selected from a data validation that has the text "No Show" or the text "Cancelled" then I want cell G2 to enter a value of 50 ... if it has any other text, then enter the value as 200 in cell G2
Any way of doing this?
Thanks
Hi Sheldon,
The solution is to use the OR function in IF's logical test, like this:
=IF(OR(D2="No Show", D2="Cancelled"), 50, 200)
Hello,
I desperately need help on below case. I am combining two reports, on sheets 12 and 13. With below formula I should be seeing "Required" in the cell because '12'!AU19 is not blank nor zero. Other cells with the same circumstances shows "Required" with no problem. When I evaluate this formula on '12'!AU190 it will actually say that it contains a constant.
=IF('12'!CQ190,'12'!CQ19,IFERROR(INDEX('13'!$A$1:$ABG$1466,MATCH('Draft (2)'!$A19,'13'!$A:$A,0),MATCH('Draft (2)'!AU$1,'13'!$1:$1,0)),IF('12'!AU190,"Required","")))
If you could help me on this it would be great. Thank you.
sorry, the last bit looks like this. IF('12'!AU190,"Required","")))
somehow I cannot put does not equal, therefore I will put it in text, in the last bit, the argument is AU19 does not equal zero.
Hello Ganaa,
Try the following IF statement:
IF(AND('12'!AU190<>"",'12'!AU190<>0),"Required","")
Quried
Can you help me with this:
IF(D4="DSSM", "B4210"), IF(D4="DSSM", "B4180")
Hi Rawle,
I am not sure I fully understand what the formula is supposed to do. If you can explain the conditions to me, I will try to help.
Plead help, I need a formula to do the following:
If A1=Y then both B1 and C1 should either be N or blank, or IF B1=Y then both A1 and C1 should either be N or blank, or IF C1=Y then both A1 and B1 should either be N or blank.
What I mean is there should only be one Y in any of the cells A1,A2 and A3. Otherwise display error message if there is more than one Y.
Hi Ticia,
The following formula displays "error" if there is more than one Y in any of the cells A1, B1 and C1, it returns an empty string in all other cases.
=IF(AND(A1="Y", B1<>"Y", C1<>"Y"), "", IF(AND(A1<>"Y", B1="Y", C1<>"Y"), "", IF(AND(A1<>"Y", B1<>"Y", C1="Y"), "", "error")))
Hopefully this is what you are looking for.
Hi Svetlana,
Thanks for your prompt response. The formula does not seen to work, probably because of where I have placed it. Where can I put the formula to make it work? I placed the formula in data validation > settings > allow > custom > formula.
Ticia,
Simply copy it into a cell where you want the error message to be displayed.
looking for some assistance on a formula.
I have the number 5 in cell a3
I need in cell b3 if a3 is > 6.5 then I need a letter "c" inputted / if a3 is < 4.5 then I need a letter "a" inputted / and if the a3 is between 4.5 and 6.5 then I need a letter "b" inputted in cell b3
I tried to do all of this and correcting the errors don't make sense
any help would be appreciated
Hi TanMan,
Try this one:
=IF(A3>6.5, "c", IF(A3<4.5, "a", "b"))
Hi, what if I want to do the opposite:
=IF('Enter Scores'!B7="D", "3",IF('Enter Scores'!B7="B","5.10",IF('Enter Scores'!B7="C","4.50",IF('Enter Scores'!B7="E","1.50",IF('Enter Scores'!B7="F","0",IF('Enter Scores'!B7="A,"6"))))))
Basically each letter has a number value, if A is entered to B7 on another sheet then the number 6 should show up on the current sheet in the appropriate cell.
I've added this function to a number of cells and I can't use a =SUM(B6:B12) function because the numbers aren't really 'numbers' according to excel.
I've formated the cells to recognise the number; but I can't seem to get them to add up; I only get a total of 0.00
MAybe I need to use another function entirely
Please can you help me with this 1
If a range of cells (A1:A100) have another cell's data(B1) I want it to return the amount of times that reoccurs into cell C1
I need to be able to repeat the formula down C column for adding up different data in B1, B2, B3, etc column
Any Help much appriciated!
Column A is lots of different part numbers that repeat sometimes (purchases)
Column B is the part numbers aranged in numerical order (stock list)and one of each
Column C is the total of times it was Purchased
Hope that is clear enough!
Hi Andrew,
I believe the COUNTIF function is better suited for this job. Here's the formula for cell C1, and you can drag it down to other cells in column C:
=COUNTIF($A$1:$A$100, B1)
I Love You! spot on
Hi,
I need to modify the following formula so that when cells B2 or C2 are blank then the output cell is also blank.
=IF(OR(C2B2>30,C2>DATEVALUE("12/13/14")),"No","Yes")
Currently it defaults to "Yes" when they are blank. Can you help me with this? Thank you.
Hi Blas,
Try a nested IF that checks for blanks, like this:
=IF(OR(C2B2>30, C2>DATEVALUE("12/13/2014")), "No", IF(AND(B2="",C2=""), "", "Yes"))
It worked but I changed the nested IF to OR instead of AND. Thank you very much Svetlana.
i will try my best to explain......let's say we have 1,500 users in c25
Heading:
Login's per Month............................Fee
e25 = 11999 ..........................f25 = $10.00
e26 = 2,000  4,999....................f26 = $6.00
e27 = 5,000  9,999....................f27 = $4.00
if c25 is between 1 and 1999 logins how do we make c28 = f25 or $10.00
if c25 is between 2,000 and 4,999 logins how do we make c28 = f26 or $6.00
if c25 is between 5,000 and 9,999 logins how do we make c28 = f27 or $4.00
We would like to plug the number into c25 and then c28 will change to what ever the fee is.
thank you soooooooo much
LC3
I think you can concatenate 3 IF functions in this way:
=IF(AND(C25>=1, C25<=1999), F25, "") &IF(AND(C25>1999, C25<=4999), F26, "") &IF(AND(C25>4999, C25<=9999), F27, "")
Please note that if a value in cell C28 is outside of 19,999 range, the formula will return an empty string.
i'm very new at this I preformed a If and put it into cell c28 and got no return, I do put the formula in where I want the change to occur correct?
You put the formula in the cell where you want the result to appear.
Excellent, THANK YOUUUUUUUUUUUUUU
is there a way to concatenate more than 3 IF functions
Sure, you can include as many as you want as long as the total number of arguments in the formula does not exceed 255.
You can find more info about the the CONCATENATE function as well as other alternatives to nested IF in the following article:
https://www.ablebits.com/officeaddinsblog/2014/12/03/exceliffunctioniferrrorifna/
I am trying to come up with an IF statement that put a name only if the column to the left of it is not blank. However, there are formulas in the columns to the left that make some of them look blank, even though the actual cell is not blank. What formula can I use to return a blank if the cell has a formula, but is blank?
I started with this, but I don't know how to exclude the formula.
=IF(ISBLANK(B8)," ","Clarence")
Hi Janna,
If my understanding of the task is correct, replacing ISBLANK(B8) with B8="" should work a treat:
=IF(B8=""," ","Clarence")
hi i am not sure if i will explain it correct but i need help i need a formula to say if c2>d2 then true 3 false 0 and if c2=d2 then true 1 false 0
thanks very much
Hi!
Try this one:
=IF(C2>D2, 3, IF(C2=D2, 1, 0))
I have following formula but it writes the result as only A even condition not satisfied
=IF(F4="","",IF(ISERROR(VLOOKUP($D$4,Sheet5!D:D,4,0)),"A","B"))
Please Help me
Hi Kumar,
First off, please check the VLOOKUP range. Currently it contains just one column (D) and naturally Excel returns an error. You probably meant:
VLOOKUP($D$4,Sheet5!D:G,4,0)
Hi, Svetlana,
I'm a bit stuck on finding a formula that will suit my needs. My spreadsheet has column A: first name; column B: last name; column C: type of training; column D: date of training.
I need a formula that will look at column A, B, and C and if they match any other row in column A, B, and C I need it to select the most recent date.
this will remove all duplicate training and keep only their most current training record.
Any suggestions? i'm stumped on this one.
If it helps, I can certainly send you a copy of the workbook that I'm trying to do this in.
A B C D
1 a:5 b:4 a:26 b:3
2 a:4 b:45 a:6 b:18
3 M3 a:50 b:40 a:26 b:30
how to use IF function on above data. i want to print:
1) let's say, if the number after the colon [:] equal or more than 5, then just print "a" or "b"
2)if the number after the colon [:] is less than 5 then print "X".
Sorry, I am not sure I can follow you. Can you try to explain in other words?
if a1>20 and b1> 50 add a1+b1 if if a1<20 and b1< 50 do nothing
please i need help on that
Hi Shabtom,
Here you go:
=IF(AND(A1>20, B1<50), A1+B1, "")
I want to calculate age between 014, 1524, 2559 and 60+ from single age registered over the months in input sheet to analysis sheet. I want to know the formula;
the logic is if a person of 24 age is registered in January, 2014 then it should be counted in 1424 age group in analysis sheet.
Thanks
You can calculate it using the COUNTIF function.
Here are the formulas to count age 14 – 24.
If dates are entered as text, e.g. " January, 2014":
= COUNTIFS($A$2:$A$100, "<=24", $A$2:$A$100, ">=14", $B$2:$B$100, "January, 2014")
If the date is formatted like this 1/10/2014, please use this formula:
=COUNTIFS($A$2:$A$100, "<=24", $A$2:$A$100, ">=14", $B$2:$B$100, "<=1/31/2014", $B$2:$B$100, ">=1/11/2014")
In a specific cell I am wanting it to tell me If a Response is LESS than 28 days old it is Outstanding & If it is MORE than 28 days old it is Overdue (this I can do) BUT I also want it to tell me if another cell on the same sheet contains 'Yes' it overides the previous command and that cell needs to read Complete but if that cell is blank it will ignore the rule. At the minute I have this: =IF((E3B7)>28,"Overdue","Outstanding"),IF(M9="Yes","Complete","0")
Hi Jen
Try this one
=IF(M9="Yes","Complete",IF((E3B7)>28,"Overdue","Outstanding"))
Hi, I want to enter a formula for the following situation:
If the cell contains specific text, then calculate xy, otherwise x
I was trying if(B1="text",A1D1,A1) but it is not working
Hi Chacon,
The formula looks correct, I don't see any reason for it not working. You can try it on a blank sheet and you will see it does work. Maybe the problem is in the source data?
Thanks...I got the solution................
Hi,
I need to check the over all strength based on five cell values where each cells have different conditions to meet. It all pass the condition i would say 100%, if any four condition pass, then 75%, similarly for 1 & 2 & 3pass condition. if all fails it is 0%.
For all pass my condition is If(and(a1>=n,a2="Yes", a3<=y, a4z, a5>=100%), 100%, ).
I need to know how to get number of pass criteria and based on that allocating the strength percentage. Is it easy to nested if or need to any other function or method.
Please Suggest.
Regards,
Jai
Hi Jai,
If the Pass criteria can be used in any order, e.g. 1st, 3rd and 5th, then you'd better use the CHOOSE function:
=CHOOSE((IF(A1>4,1,0)+IF(A2="Yes",1,0)+IF(A3<3,1,0)+IF(A4>=6,1,0)+IF(A5=100%,1,0)),0%,20%,40%,50%,75%,100%)
Just change the conditions for each cell as you want.
The principle is as follows: each IF statement returns either 1 or 0 depending whether the conditions are met or not. The sum of the IFs' results varies from 0 to 5.
The CHOOSE function returns 0%,20%,40%,50%,75%,100% (you can also modify this part if needed) according to the sum of the IF functions' results.
Hi I want to include a value from a list in my IF formula.
=IF(A18 ="Other" ), D18, (SUM((D18+E18)*F18))
so basically if the value from the list in cell A18 = the word Other (that's a value in the List) I want to display the value in cell D18 if Other is not displayed in A18 then I want the Sum calculation to be done.
Is this possible?
Hi Bel,
Try this one..
=IF(A18="Other",D18,SUM(D18,E18)*F18)
Awesome! Thanks so much for your help Prasenjit!
Alternatively, you can put it in this way:
=IF(A18 ="Other"), D18, (D18+E18)*F18))
Svetlana Cheusheva,how can u do, i tell u when i come back..........soonnnnnnn
I want a function which will solve my query as mentioned below.
A B C D E
1 71501 giri 1
1 76597 sekhar 1
1 71503 satish 1
I want value of column C in column E If value of column A and particular value of column B match with value in column D
for example my requirement is column E = "sekhar"
please help.
Hi, Svetlana,
I'm a bit stuck on finding a formula that will suit my needs. My spreadsheet has column A: first name; column B: last name; column C: type of training; column D: date of training.
I need a formula that will look at column A, B, and C and if they match any other row in column A, B, and C I need it to select the most recent date.
this will remove all duplicate training and keep only their most current training record.
Any suggestions? i'm stumped on this one.
is this something you can help me with? i'd really appreciate it. I'm in a time crunch and I have no one else to ask for help.
Hi Amanda,
You can simply sort your table by column D (most recent dates will be at the top), and then use the standard Excel Remove Duplicates tool to find duplicates in columns A, B, C and delete them.
If it is not the case, then send us a small sample workbook with your data at support@ablebits.com including the expected result, and our support team will try to help.
Svetlana,
I'm trying to avoid having to do this manually as the workbook is very large and will continue to get larger. I have emailed you a copy of the workbook as well as what i'm looking for.
Thank you,
Amanda
I have a cell that contains: Check # 0000
In other words, Check # 0000  9999
Can you recommend an If Formula. I have tried
=IF(A1="Deposit","Deposit",IF(A1="Check","Check"))
This works only if I have Check in the cell but with the number sign and numbers it returns a false value. Does anyone have a solution?
Hi William,
Try this one
=IF(A1="Deposit",A1,IF(SEARCH("Check",A1),A1))
Please let me know if it is working...
Case Qty (CQ) = 20
Max Level (MQ) = 18
Min Level (MiQ) = 8
Quantity on Hand (QOH) = 5
Reorder Level (ROL) = If(QOH<=MiQ,MQMiQ,0)
Reorder Quantity (ROQ) = ?
I want to have ROQ as.... If ROL is Zero then Zero, If ROL is Less than 60% of CQ then CQ, if ROL is greater than 60% of CQ then zero. Pl advice.
Hi Sreedhar
Please let me know if it is working for you...
=IF(ROL=0,0,IF(ROL 0.6*CQ,0)))
hi,
i have this check list with a checkbox, and i need to know how i can make the question status cell to display a text(done, pending) while changing the cell color (red to green),when i select one of the checkbox answer.
ex. do you have a list of traces on PCB? []yes []no []n/a [question status]
Hi!
I am trying to create a formula which will tell me when two dates in two separate columns do not match. Then if they do not match, what the earliest of the two dates is. Many cells in the second column are blank.
something like =IF(J2=K2,"Yes","No"&J2K2)
But it only gives me #VALUE
Create a 2 columns, one after each current date column. Convert the columns to a number, not a date. Divide one column by the other. If 1/1 = 1, then all rows with a value of 1 have no difference. If the value isn't one, there is a difference. I'm sure there's a formula, but this little trick works great! I call them "divchecks".
i am trying to write a formula. if d7 contains the word late display 1.
Hi Tahsa,
Here you go:
=IF(D7="late", 1, "")
Hi,
I am trying to create a purchase order that is tied into my existing estimating sheet. I would like the items that are selected in my estimating sheet to transfer over to the Purchase Order Sheet. The IF function that I am looking for will not only calculate $'s but also text in the same line. Is there an easy function that would work out for this.
This is the example that I can add for your reference.
(2) Work Sheets one labeled Estimating Sheet the other is Labeled Purchase Order.
(4) Cells Needed Per Line which are: Quantity, Description, Unit Price, and Total.
Please let me know if there is any thing more that you may need!
Thanks!
Hi,
can you help me with this formula?
Currently it shows TRUE when I use this formula =RIGHT(A1,8)=D1 want to add below conditions as well accordingly it should show TRUE or FALSE.
If A1 is not blank it should go for =RIGHT(A1,8) = D1 return values should be TRUE
If A1 is blank D1 may be 123 or 456 when d1 falls in 123 or 456 return value should be TRUE when the condition falls out of this scope return values should be FALSE.
Please help me.
Thanks
Hi,can you get me a if condition to display the value as 0 with out disturbing the present formula = if(R12>15000,15000*12/100,R12*12/100)
I don't know VBA code. Just a little familiar with basic formulas. I need a simple condition formula that when specific data is entered by user in a cell of one worksheet it will open other worksheets within the workbook. Greatly appreciate any help.
i need to add today's date on one cell if i write a number o letter on another cell some like this. =if(F3=text, "(today()") but i can't make it work.
=if(F3="text", today(),"")
Hi can you help me with this formula, I want to create an "IF" Formula
example :
B C D E
FinDate AgreeDate ActDate Delay Formula
10 31.08.2013 22.08.2014 356 C10E10
11 21.09.2013 10.03.2014 13.03.2014 3 D11E11
12 30.10.2013 15.04.2015 76 D12TODAY()
13 30.10.2013 456 C13TODAY()
Everything start with act date if blank, check Agree date, then check Fin Date. Final Result is delay ... ( Days )
Thanks
Hello,
I need a formula to see if the same name is contained in each column
for example a1  z1 contains the word accounts receivable
a2  z2 the word cash
a3  z3 the word land
and etc.
Any help would be greatly appreciated.
Hi,
I am looking to match data points along each row in a worksheet.
One column contains values (03)
The other column contains values (01)
And the last column contains values (17)
I want to find a formula that will find out how many times each of these scenarios happen:
Column one is 3/Column 2 is 0/Column 3 is 1 or 2
Column one is 2/Column 2 is 0/Column 3 is 3 or 4
Column one is 1/ Column 2 is 1/Column 3 is 4 or 5
Column one is 0/ Column 2 is 0/ Column 3 is 5, 6, or 7
Thanks!
i have a table basically column A date, column B cash, column c Check. I need to look at column B & C in same row and sum all based on whether column A's year text only, like 2015.so it would give me a total of all payouts in 2015 only. the date column is formatted 1jul2015 with multiple years being listed.
date cash check
1jul2015 50.00 0
1/jan2014 0 100
1feb2105 0 75
total for 2015 should be 125.00
Regarding Example 3. "IF formula for text values with partial match."
I am looking to get the same result but for text values in a RANGE of cells (not just one cell) with partial match.
So if in a range of cells, containing several names in each cell, the name "Brigitte" appears, than I want excel to return an X, if not than I want nothing returned.
I have been looking an dlooking and haven't found the solution so far.
Thanks a lot in advance for your help.
I tried this: but it doesn't work: =IF(ISNUMBER(SEARCH("Brigitte",'FY15 Germany & Benelux Projects'!K2:O2)),"X","")
I tried this: but it doesn't work: =IF(ISNUMBER(SEARCH("Brigitte",'FY15 Germany & Benelux Projects'!K2:O2)),"X","")
I'm need to get the following outcomes for multiple scenerios. Here's and example:
If E2=1 then G2="NIC"
If E2=2 then G2="CCM"
If E2=3 then G2="HP/DP"
I want to copy this formula throught a farily large spreadsheet.
never mind I figured it out:
=IF(B1=1,"NIC",IF(B1=2,"CCM",IF(B1=3,"HP/DP")))
Hi Svetlana, do you have a formula for words of which the column 3 is the final identifier from the given information from columns 1 and 2:
Column 1 Column 2 Column 3
Common Stock Common Stock = Common Stock
Preferred Stock Preferred Stock = Preferred Stock
Rights Common Stock = Common Stock
Rights Rights = Rights
Preferred Stocks Rights = Rights
Hi,
Is it possible to get an if result that shows, in a single result, text and a math result?
=IF(D20<100%,"Dropout to be recovered"(SUM(D18D19)),"Over target")
After "Dropout to be recovered" I would like to show the amount to be recovered.
Thanks.
Hi Adolfo,
Sure, just use the concatenation operator (&)  "Dropout to be recovered "&SUM()
If you want the text and number to be separated with a space, type a space before the closing quote, like in the example above.
Hi,
I've beentrying to fix this sheet i'm working on for a day now. i hope you can help me. what i'm trying to do is if my team will put their name choosing from a drop down list from A1, it should automatically put their employee ID in B2.
please help...
Thanks..
Hi Mark,
Assume that you have master base data where you have all the info about emp name and ID. So from there you can create a employee list by the data validation rule and then put it in A1..then at B1 put this vlookup
formula = Vlookup(A1,Base data range,Column index no,0)...
So when you will put their name choosing from a drop down list from A1, it should automatically put their employee ID in B2.
Hello, i am trying to evaluate between five different columns, in which one of the four columns has a date and two or three of the following columns may or may not have a value. I have tried Isblank but don't get the results. eg: subtract ba, if b blank then use ca, if b and c blank then da, if b,c,e blank then ea.
Hi Jack,
Try this one:
=IF(AND(B2="", C2="", D2=""), E2A2, IF(AND(B2="", C2=""), D2A2, IF(B2="", C2A2, B2A2)))
Hey Svetlana
I am dealing with 10000 calls and every call has a time (like 13:56:40)
but I want to count only calls from 8:00:0017:00:00
I am using =IF(AND(B433>=08:00:00, B433=08:00:00, B433<=17:00:00), "1", "0")
13:36:06 1
18:56:07 0
14:32:26 1
I want it like this but I am not sure what is the mistake with my formula
I need a formula that will allow me to transfer dates from one page to another.
The first page will be in date format but I need it to correlate on the second page in dated columns as a single X or checkmark.
I would appreciate your assistance.
Thank you,
I'm working with a database the puts in new orders at the end of the spread sheet. What I'm trying to do is only bring the "new" entries into another sheet. I've made an identifer of using "1" for new and "0" for old. How would I only display the "1" entries on the workbook? Any help would be greatly apperciated.
How do you use an If function to count how many characters there are in a cell.
The question is to ask if there are less than four characters in a certain cell?
Hi Kimberly,
You can use the LEN function to count the number of characters in a certain cell, and then you can embed it in the IF function. For example:
=IF(LEN(A1)<4, "good", "bad")
I have a table with different tasks and Price of that task. I want to be able to put in a task code and get have a cell read "direct" or "indirect". Would if function work best?
Hi,
I am doing a project and I need to use the IF function in cell B14 to see if the average in B10 is > the value of each individual average in cells C10,D10,E10, and F10. I tried =IF(B10>C10:F10,"Yes!","No") and =IF(B10>C10,D10,E10,F10,"Yes!","No") both aren't working.
Never mind. I just got it.
Hi,
I was wondering if you could help me. I am trying to format a worksheet where if there is something (in my case columns H, J, L, and N all contain dates) in column H and no value in column I for that same row, the row text turns a different color. Then the same for J to K, L to M, and N to O. Is there an easy way to do this? Maybe I am overthinking? If it's not possible to change the row text color, then just the relative column with the date (H, J, L, N). Thanks!
I am sad that this is not something that can be done. It would make things so much easier.
Hi Gracie,
In fact, you can do this by creating Excel conditional formatting rules. For example, if you want to highlight a row that has a date in column H but no value in column I in the same row, you use this formula:
=AND($H2<>"", $I2="")
Where row 2 is your first row with data (not including the column headers).
Please check out the following tutorial for more details:
How to change the row color based on a cell's value in Excel
This is terrific! I cannot even put into words how helpful this is. Thank you so much!!!!
hi,
I need a help in IF formula.
I have a sheet column AA8 TO AA132 with different due dates, AB column has status as ND,F,WP. I want to change the ND to blank if due date is today.
thanks
hi,
could you please help me on this formula
=IF(AA8=TODAY(), IF(AA8= "ND", ""))
if ND with due date works fine, but any other words LIKE F, WP,CO ETC RESULT SHOWS FALSE
THANKS
VJ
Hello Vijay,
It is difficult to recommend you anything without seeing your data. If you can post your sample workbooks and the result you want to achieve on our forums, our support team will try to help.
I want to highlight one cell of any column When we move the cursor anywhere in the excel sheet. when we will move the cursor up & down then highlight position also move in column only.
ex
let we choose B column and we work in A,C,D column.
now if we will work in A1 or C1 or D1 cell then B1 cell should be highlight.
if we will work in A3 or C3 or D3 column then D3 should be highlight.
plz give the solution.
Hi, I am creating a spreadsheet to analyse UK horse racing. There are 60 courses under the "Course List" column. In the "Course" column I have listed the meetings taking place each day for the next 12 months, 1175 in total. I am using the following formula in the "Total Meetings" column to extract the number of meetings for each racecourse during the next 12 months
Total Meetings:
=COUNTIF($B$2:$B1176,$D2)
I would now like to add another column which shows the number of meetings remaining after todays races have completed. e.g. if there are 25 meetings in total for a race course, each time a meeting is completed reduce the number of meetings remaining by 1 and display in "Remaining" column next to each course in the "Course List"
RACE DATE COURSE Course List Total Meetings Remaining
1Feb15 MUSSELBURGH AINTREE 21
MUSSELBURGH CHELMSFORD CITY 18
2Feb15 PUNCHESTOWN LINGFIELD PARK 54
WOLVERHAMPTON SOUTHWELL 60
KEMPTON PARK CHELMSFORD CITY 55
ASCOT AYR 12
BANGORONDEE 22
MUSSELBURGH 7
Any suggestions please?
Thanks
i trying to put a formula which searches a specific word from the excell cell string and if ts there then then type "True" or false accordingly.
please help me out over this
HOW TO USE ROUND OFF COMMENT WITH SUM
hi,
i have this check list with a checkbox, and i need to know how i can make the question status cell to display a text(done, pending) while changing the cell color (red to green),when i select one of the checkbox answer.
ex. do you have a list of traces on PCB? []yes []no []n/a [question status]
Hello,
Looking for an if statement which may not exist. At work we run an SQL that reports locations of specific items. The location field is always xxxxxxxx. For example 22411103 would be aisle 224, 111th spot, 3rd shelf. I am trying to figure out if I can write an if statement where if the value is between 20000000 and 29999999, then 1. Or really anyway to manipulate the "22411103" so I can work with the locations.
Thanks
Hello Daniel,
You can use both variants. Please specify the one you prefer and we will help you with the formula.
Hi,
Please i need a help, i want a formular for two dates intervals. A suming i have my start date and want to know the ending dates.
For instance my start date of paying back loan is 08/15/2014 and i'm paying for 6 months, what will be the formular for the ending date.
Thanks
Hello Francis,
Please use this formula:
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
Where A1 is the cell with your start date, 6 is the number of months.
Hello, Please help me I need following formula.
I need formula of column C and D
C = matching part part will be colored.
D = Matched or not, in sequence of column B.
problem attached for ready reference with following link.
https://www.dropbox.com/s/xbkh51pp9n9vt28/Formula.xlsx?dl=0
Hi Imran,
The following formula seems to return the results you want:
=IF(OR(NOT(ISERR(SEARCH($A$2,B2))), NOT(ISERR(SEARCH($A$3,B2))), NOT(ISERR(SEARCH($A$4,B2)))), "matched", "not matched")
Or even better  the array formula (remember to press CTRL + SHIFT + ENTER to enter it:
=IF(SUM((NOT(ISERR(SEARCH($A$2:$A$4,B2))))), "matched", "not matched")
I want to figure out a way in column A1 to column A1000 is equal to the word "Done" then enter the current date in the coresponding column B1 to column B1000
1) Enter Done in A1 and B1 gets automatically updated with date and time
2) Enter In Progress in A2 and B2 get nothing updated
3) Enter Done in A3 and B3 gets automatically updated with data and time
4) and so on....
Status Timestamp for Completion
A1 Done B1 02/06/2015 5:17:33 PM
A2 In Progress B2
A3 In Progress B3
A4 Done B4 02/06/2015 5:17:34 PM
trying:
=IF(A1:A10="Done",=(NOW())
or
=IF(A1:A10="Done,B1:B10=(NOW()))
or
?
Hello,
If you want to enter the current date so that it will never change, then you need a VBA macro.
You can ask for it in specialized sections on http://www.mrexcel.com or http://www.excelforum.com.
Hi just wanna ask help on this.I'm confused on how to exactly put many functions in the formula. The legend are the following:
"B"  74 below
"D"  7579
"AP"  8084
"P"  8589
"A"  90 up
For example:I actually tried to enter =IF(E16<75,"B","D")=IF(E16=80<85,"D","AP")=IF(E16=.....ETC...
pls help me...
Hi Michelle,
You need to use a nested IF function like this:
=IF(E16>=90, "A", IF(E16>=85, "P", IF(E16>=80, "AP", IF(E16>=75, "D", "B"))))
Using an IF statement, can more than one action be included if the condition is met? For example, if A1=x then (B1 = "true" and C1=y) else (B1 = "false" and C1=z)
Hi Nancy,
There is no way to do this in a single formula because a formula can return the result only in a cell where it is entered.
You can simply use two formulas:
For B1: =IF(A1="x", true, false)
For C1: =IF(A1="x", "y", "z")
If you need to change a lot of cells, then a VBA macro will be a better solution.
Hi ,
I am using nested IF for age
=IF(M7<39,"Cohort C",IF(AND(39<=M7,M7<48),"Cohort B","Cohort A"))
however the age i am using DATEIF formula as below :
=DATEDIF(K7,TODAY(),"Y") & " Years, " & DATEDIF(K7,TODAY(),"YM") & " Months, " & DATEDIF(K7,TODAY(),"MD") & " Days"
which result as i.e 35 years , 2 months, 1 days
How can i specify the date as above format in the nested IF statement ?
Hi Luxmi,
It's difficult to give any advice without seeing the real data. It will be helpful if you can give more details about the value in cell M7 and other cells the formula refers to.
sorry it is actually M7 = Age . However , for this scenario the age is purely calculated by the year only.
How if i use below formula to calculate the (M7 cell) age & use this M7 cell to do nested if statement :
=DATEDIF(K7,TODAY(),"Y") & " Years, " & DATEDIF(K7,TODAY(),"YM") & " Months, " & DATEDIF(K7,TODAY(),"MD") & " Days"
which result as i.e 35 years , 2 months, 1 days
how can i specify the age using this format ?
=IF(M7<39 years, 00 Month 00 Day,"Cohort C",IF(AND(39 Years 00 Month 00 Day<=M7,M7<48 years 00 Months 00 Days),"Cohort B","Cohort A"))
Hello Luxmi,
Please specify the age in the same way as you calculate data in cell M7, i.e. using this formula:
=IF(DATEDIF(K7,TODAY(),"Y") < 39,"Cohort C", IF(DATEDIF(K7,TODAY(),"Y") < 48,"Cohort B","Cohort A"))
I tried using the If formula for my data but I just could not get it to work.
Example:
I have set a target for John and if he meet the target the cell will display the Rebate (10%) percentage, if not it will display 0%.
Rebate: 10&
QTD: 17,275
Target: 12,500
How do i go about setting the formula for this?
Thanks so much!
>cell will display the Rebate
=IF(E2>=12500, "10%", "0%")
Where E2 is the cell with a target for John.
Hi,
I have successfully used on A3 cell: =IF(ISBLANK(A2),"",IF(A21,A2<=2),"IMPROVEMENT NEEDED")))
But Can't use on A4 cell: (A1 cell is a number i.e. 10000)
=IF(A3="Unsatisfactory",A1*100%,IF(A3="Improvement Needed",A1*105%))
it shows:"FALSE". how to make A4: if A3 is vacant as per above formula, A4 will be A1*1
Hello Zakaria,
I am sorry, your task is not quite clear. Please post a sample workbook with some variants of your data (1 sheet – 1 variant) on our forums and include the result you want to see in A4. Our support team will look into your task and try to help.
Hi there, i am trying to figure out a formula for not leaving cells blank. For instance, if B2 has the word "debit card" the C2 cannot be left blank. If B2 contains the word "cash" then C2 can be left blank.
Does such a formula exist?
Many thanks
Lara
Hello Lara,
You can create a conditional formatting rule to color a cell, e.g. in red, showing that it needs to be filled in.
The formula will be as follows:
=AND($B2="debit card",C2="")
You can find more information about conditional formatting in this article:
https://www.ablebits.com/officeaddinsblog/2014/06/10/excelconditionalformattingformulas/
Hi, I am trying to work out a formula that says:
Work out the average of F5:F11 IF B5:B11 contains data
Is there any chance you could help?
Many Thanks
Rachel
Hi Rachel,
=IF(COUNTA(B5:B11)=7,AVERAGE(F5:F11),"")
Hi there,
I'm using a SUMIF formula to return a required number.
Now, i need a formula to return a required text.
if i enter name in c1 and it matches with b1 i need an answer.
for example:
a1 b1 c1
rey 502 rey
may 502 may
hope you could help me.
thanks,
Hello Clarice,
I am sorry, your task is not quite clear. For us to be able to assist you better, please post a small sample workbook with your data and the expected result our forums. Our support team will look into your task and try to help.
Hi I am struggling working this out any help would be fantastic.
Basically I have 3 businesses I need to keep track of the mileage, I have a spreadsheet set up for that. However I want to keep individual records on the sheet for each business.
so the business is one of three "photography" "building" "design" in cell D6 I want to take the mileage from k6 and add it to a separate cell depending on the business displaying the output at g2, g3 or g4 and I want this to run through the sheet. so it checks column d for which business then depending on the business it takes the result from column k and place the sum totals in either g2,g3, or g4
I am at a loss to sort this. not even sure its possible. but help is appreciated please.
Hello Patrick,
A formula can return the result only in a cell where it is entered. Most likely, you simply need to place three IF formulas into cells G2, G3 and G4 that will show the result or keep blank according to the specified conditions. If you want to get a sum, then you may also need to use the SUMIF function.
If you can send us a small sample workbook with your data to support@ablebits.com, we’ll try to assist you better.
Hello,
I'm having trouble making a formula that will return a date or a value. i.e. I'm making a time off request form, and sometimes the cell will have a single date (2/11/15) and sometimes it will have multiple dates (2/11/15 to 2/12/15). I can get the single date to fill by formatting my formula cell to "date," but I cannot get the range to show up. I want the exact input from the cell to show up regardless of being a date or "text" as the multiple dates would be considered.
My formula that I'm using is =IF(A7,A7,"").
I've tried using a variety of arguments in front of the second A7, but I still haven't gotten the results I want. I don't want to format the original cell to text if I can avoid it.
I found my answer. Thank you.
=IF(ISBLANK(A7),"",A7)
This does exactly what I need.
Hi,
I have a master sheet and individual sheets (with the details) for a survey.
I want to be able to populate the individual sheets with simply putting a Y or N on the master sheet. Is this possible?
ie A clients details and their consent to receiving marketing, research & volunteering are on the master sheet. Can I then automatically populate the individual sheets from the master?
Thanks
Debra
Hello Debra,
You can enter the formula like the one below to the cells on your individual sheets:
=if(MasterSheet!$A$2="y", MasterSheet!$C$2, "")
That means that if cell A2 is in the master sheet contains "Y", copy data from MasterSheet!$C$2.
Thanks for posting If Function in a very easily manner.
can you tell me the function to extract text from cell
Eg: Paid to Sub cont Rafiq/against balance payment.
Paid Sub cont Shafeeque/against part payment.
In this I want to extract only from the text sub cont to /.
I am sorry, your task is not very clear. For us to be able to assist you better, please post a small sample workbook with your data and the expected result on our forums. Our support team will look into your task and try to help.
Hi. I want to extract a number from a cell(B2) at an exact time. I have two times on my sheet, one is the start time of an event and the other is the actual time right now. When there is a differance of 2 minutes between both times, then i want to extract the number in cell(B2) and i want the value to remain there.
My sheet is connected to a website and the sheet is updated every second, so the value in cell B2 is constantly changing.
Is this possible???
Hello Aengus,
I am sorry, but there is no way to fulfill your task using formulas. Since you want the value to remain in the cell, a VBA macro is needed for this task. You can look for it on http://www.mrexcel.com or http://www.excelforum.com.
Class Name Test 1 Rank
year 9A Student 1 20
year 9A Student 2 30
year 9A Student 3 20
year 9A Student 4 21
year 9A Student 5 22
year 9A Student 6 52
year 9A Student 7 54
year 9A Student 8 14
year 9A Student 9 25
year 9A Student 10 26
year 9A Student 11 21
year 9A Student 12 24
year 9A Student 13 22
year9B student 1 23
year9B student 2 54
year9B student 3 52
year9B student 4 52
year9B student 5 21
year9B student 6 22
year9B student 7 12
year9B student 8 22
I am sorry, it is not quite clear what result you want to get. For us to be able to assist you better, please post a small sample workbook with your data and the expected result on our forums. Our support team will look into your task and try to help.
Hi, i want an output with two conditions. That is if A1=I OR II OR III and if B1<=5 then the output in C1 should reflect as 'yes' otherwise it should be 'no'.
I typed as =if(and(A1="I", A1="II", A1="III",B1<=5),"yes","no").This is not working.
Kindly let me know the error or is this formula totally wrong? Thanks in advance.
Here you go
=IF(OR(A1="I",A1="II",A1="III"),IF(AND(B1<=5),"YES","NO"))
Thank you Aengus. The formula worked wonderfully well.Delighted.
Cheers.
Aengus, thank you for your help.
Shiva, you can also use the formula below (the result will be same):
=IF(AND(OR(A1="I", A1="II", A1="III"),B1<>"", B1<=5),"yes","no")
Thank you Alexander. The result was correct. Elated to learn new techniques.
Cheers.
i am trying to set up an IF function where it searches a cell for a word that has the letters UCI if true gives the result 2.2 if false gives 2.8. i have done this but doesnt seem to work :(
=IF(ISNUMBER(SEARCH(E3="UCI")),"2.2","2.8")
please help
Hello James,
=IF(ISNUMBER(SEARCH("UCI",E3)),"2.2","2.8")
Ah brilliant much appreciated seems i fell at the last hurdle.
cheers
James
I have 2 cells that contains month and year. The first cell is date when qualification was received. Second cell is date when qualification expires. The 3rd cell below these 2 cells I want to say "Expired", "1 Month Left", "3 Months Left" based on these dates.
Please help me.
Thank you very very very much!!!
HELLO,
i need a formula to show that if my first cell is greater than the average then to show me 0 otherwise to show the difference between the first cell and the average(some cells are 0) ,so i was trying =IF(D3>D$291;TRUE=0;FALSE=D3D291) but doesn't work .THANK YOU IN ADVANCE!!!
Hello Sofia,
Just enter the following formula:
=IF(D3>D$291; 0; D3D$291)
i want to check if the data in one cell is contained in any other or not. what is the code??
Hi. I have a word in cell A62. I want to search the range A5:A45 to see if it contains my word. If it finds the word, then i want to highlight that row and then insert a formalua, =IF(B41="a","yes","no") in the Q coloum of that row.
Is this possible?
Using IF function write a formula if " Date" is Blank pick the Value from "System number", else name it as Date In.
data
system date
d322 16/2/2015
f3532 17/3/2015
Hello Svetlana.
Great Work.
I need to find a two pairs of value in a cell, A/B OR C/D.
A = 5 [value in cell C31]
B = 5 [value in cell C31]
C = 8 [value in cell C32]
D = 8 [value in cell C32]
blank cell, give me nothing.
How can i do to find A or B ( same with C or D)
=IF(C33="A OR B";(C$31);IF(C33="C OR D";(C$32);""))
also one question, how can i make a addiction of various IF conditions? Like:
=IF(C33="A OR B";(C$31);IF(C33="C OR D";(C$32);"")) + =IF(D33="A OR B";(D$31);IF(D33="C OR D";(D$32);"")) + =IF(E33="A OR B";(E$31);IF(E33="C OR D";(E$32);""))
Hello Svetlana,
Your site is very helpful!
Could you kindly help me with below:
I need a formula where if the cell in particular row contain values from a table, then return value "trade", otherwise "unknown". The list contains over 100 values, therefore the ISNUMBER(SEARCH) function cannot be used.
"Table" example:
11 trade
12 trade
13 trade
14 trade
15 trade
186 trade
...
List example:
13 trade
13,14 trade
15,2222 trade
2882,2213 unknown
14,11,75,2882,2213 unknown
14,11,75,2882,2213 trade
Hi,
I have to find difference between two dates (Ex. A1 & B1). B1'=(now)' . I want results to display either one of these"Minutes Ago","Hours Ago", "Days Ago","Week's Ago","Months Ago". whichever is the least.
EX:A1= 2/16/2014 7:05:05 PM, B1=2/18/2015 2:29:13 PM.
My formula gives me this result =1 years 12 months 367 days
DATEDIF formula {=DATEDIF(A1,B1,"y")&" years Ago "&DATEDIF(A1,B1,"m")&" months Ago"&DATEDIF(A1,B1,"d")&" days Ago".
This does not give Hours and Minutes. Can you help?And also is there a way to use If formula to display only 1 result i.e, "Minutes Ago" if this is the closest? Please help !
Hello Svetlana,
I need a formula that could generate email from from a given list of names.
I need the formula to be in such a way that it could satisfy both the below condition's.
For ex:
if there is:
Condition 1:
==>> First name + Middle name1 + Middle name2 + last name
Ex : ( how ) + ( are ) + ( you ) + (today )
to generate : how.are.you.today@gmail.com
Condition 2 :
==>> First name + Middle name1 + Middle name2 + last name
Ex : ( how ) + ( isblank ) + (isblank) + (today )
to generate : how.today@gmail.com
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
looking forward to hearing from you soon.
Thanks in advance.
i need a formula for first date is less then 2nd date how to check
Hi Svetlana,
I want to create the formula that select the cell P if the life time less than 10 years ,I put this formula =IF(S5<10,P5,"")but I also need to have same result if life time "15", (between 1 to 5) and I put this =IF(AND(S8<10,S8= 1  5),P8,"") didn't work, could you please help me.
Thank you,
Hi,
1) I need a formula in I16 that can check cell B12 for # of servings (if any).
example: B12="Sangría Rojo (2 glasses per guest). 10 servings."
2) If B12=x servings then x*h12
3) If B12 does not contain x servings then h8*h12
example no servings: B12="Sangría Rojo (2 glasses per guest)."
Thank You
Hi,
Building a transcation register. Currently using this formula =IF(D13>0,H12D13,H12+F13).
But, when I copy this to the rows below I'll get the last result shown in the rows below. I would like to check the cells for values, before using the above mentioned formula. So when there are no numbers in cell D14 and F14, it will return an empty cell in H14. If there are numbers in either D14 or F14 use above if formula.
Thanks for your help!
I am trying to add 1.5 to values higher than 3. Values 13, I need to remain the same. How can I do this?
Ex. person enters 4 in a cell it is automatically calculates to 1.5
Person enters 2 in a cell it remains the sames.
Hi Svetlana Cheusheva and Alexander,
could you please help in solving my problem
my excel column contains 5:55 2:26 2:19 1:38 1:06 1:16 1:17 2:20 like wise nearly more than 100
my result column to have Neg Neg Neg Neg 12 HR 12 HR 12 HR 23 HR
could you please provide me an IF formula
am using 2007 excel
RemainingTAT(h:m) TAT
2:19 Neg
1:38 Neg
1:06 12 HR
1:16 12 HR
1:17 12 HR
1:20 12 HR
2:02 23 HR
2:05 23 HR
1. i have a table with 2 columns (A,B)
2. column A has a list of control measures and
column B has 2 options(yes or no)
3. what i want is if the answer is "yes" to NOT extract the control measure in a new worksheet and if the answer is "no" to EXTRACT
e.g A1 (warning signs in place?) B1 (no)
A2 (workers are aware of local emergency procedures?) B2 (yes)
Cell A1 is a hand entered date. Cell B1 is a total dollar value. Cell C1 is # of months. Starting with Cell D1 and going right are 12 more columns for the months of the year. Need a formula in D1 that says this: If the date in A1 is between January 1st and 31st, then take B1 and divide it by C1. Once I can get that, I can nest it with ifs to cover all of the months. My problem is how to do the date portion. Can you help??
=SUMIF(January!A5:A200,January!U4,January!B5:B200)
The January!B5:B200 has names in it but it is coming up 0. i am sure that i am using the incorrect function
Hi,
I am using the =if(isnumber(search) to look for partial value. However, in my list I have 180 and 18. the end result will only pick up 18 instead of 180.
What other functions can I use to ensure that both 18 and 180 will be pick up?
18,19,43 in a column with test date at next column. 18's validity is 1 year,19's 5 year and 43's 6 month how can we show the due date in excel at a glance.
Worker Type Emp # Days Worked Total hours Worked Over Time Over Time Charges Basic Pay Net Pay
A 121 20 190
B 122 19 200
C 123 24 231
A 124 21 210
B 125 25 215
B 126 28 180
C 127 15 140
1) Over time=Total hours worked(days Worked*8)
2) Using If Function calculates over time charges
3) Over time charges=Overtime *(100 if worker type is “A”, 50 if worker type is “B” and 35 if worker type is “C”)
4) Basic Pay =Days worked *(400 if worker type is “A”, 300 if worker type is “B” and 200 if worker type is “C”)
5) Using SUM() function compute NetPay=Basic Pay+Overtime Charges
please solve part 2 and 3 , i m just got mad
Hello!
Thank you for this guide. It's very helpful. I do have one question though, I am trying to copy a date on a cell. Say, date is in A1 and a restriction is in B1. My formula goes like this  =IF(B1="0","",A1). C1 should have be a copy of the date in A1 right? Wrong. Mine shows 420417. It shows numbers T_T not the date. Please help. Thank you in advance.
Sorry about this one. After a few trial and error, I managed to get it :D. Here's how I did it...
Make sure that the cell you are copying is in a proper format. Don't let it automate to DATE, CURRENCY and etc. Then, before you create a statement, say =IF(X=A,"","B"), make sure you CLEAR FORMAT that cell. Good luck to you guys :D Thanks again.
Column
A B
Company A
Rpt1
Rpt2
Rpt3
Company B
Rpt1
Rpt2
Rpt3
I want analyse the similar data above by puting the header name (company name) next to each sub item cells
example I want to see like this.
Column
A B
Campany A
Rpt1 Company A
Rpt2 Company A
Rpt3 Company A
Campany B
Rpt1 Company B
Rpt2 Company B
Rpt3 Company B
Please anyone to help on this formula?
Hello dear friends
I need help I have
(column A is time in) (column B is time out) A1: 7:30 PM B1: 11:00 AM, I'm using the following formula to calculate the hours worked, less than 30 minutes lunch
=MOD(B1A1,1)*24"0.5"
BUT I only need to take the 30 minutes lunch ONLY if the employee worked more than 6 hours.
"IF EXPERTS "HELP!", (THANK YOU) "" :)
(employees are nurses so they work overnight and all kind of crazy hours). Can you help me to figure out an "IF" that works for something like that?
Hi. I'm trying to figure out how to make excel go to the next sheet if a particular cell value is equal to "true".
example: if cell a1 = "true" go to sheet 2
I need to display a cell from another tab row using today's date. How would I do this?
if row xx column xx = (todays date) display row xx in column xx (for example)
Please some one help...
I need can enter the data if the nearest cell having only as "OK" otherwise should not able to enter any data.
EX:
E10=OK> E11= Possible to enter data
E10=asfa> E11= Not possible to enter data
I want a cell showing  , when i use it for calculation it's value should be 1.
Please tell me how to do it?
Is there a way to create an IF statement based on whether a number is a date? For example, If the value in A1 is a date, return the value of the date, otherwise, do nothing.
How to check if there are 10 characters in a cell or not
Hi Svetlana ,
I need help with this one. I have one column lets say A1 : A38 that have some values / text . Those values can repeat themselves through A1 : A38 many times . I would like to get in another row or even sheet only one of each of those values or records.
For Example : A1 have TB231 ; A2 TB236 ; A3 TB555 ; A4 TB555 ; A5 TB231 etc , now in separate sheet or row i should get only one of those , it dosent matter how much it has same records i just need that i get value of all different in separate sheet or row.
Results of example should be : D1 TB231 ; D2 TB236 ; D3 TB555 , i tried with IF function but for 38 or more rows would be huge so maybe there is other easier way.
Thank you
Hi, I need some help with this formula. A1 = $25000 B1= if(A1>0,"Total (A1)","Total"). If A1 is greater than 0, I would like B1 to show [ Total ($25,000.00) ] and if A1 = 0, I would like it to show [ Total ]. Does anyone know if this is possible?
Assume that even numbered respondents (identified by RespondentID) are business travelers and odd numbered respondents are leisure travelers who travel infrequently. In column F on the surveyData tab, create a formula that identifies each respondent as either a Business traveler or Leisure traveler. You will need to use Excel’s If() function (see your Excel handbook or Excel help). Title this column travelerType. Ensure that cells F2:F501 are filled with the formula. Please help me create a formula.
Hello sir, I'm Trying to enter Data in Sheet in form of numbers
for example:
RollNo: 801
Maximum Marks
20
20
20
20
20
20
20
Sig. of Class Incharge
RollNo: 815
Maximum Marks
20
20
20
20
20
20
20
Sig. of Class Incharge
but the problem is when i try to drag or paste the same cells in another line.......Rollno has changed 814 instead of 802. My all cells are refrence cells of sheet1.
Now which formula is best during drag or paste the rows.If i paste the row after 14 Rows ..roll no show 815 instead of 802.
please guide me what should i do?
if u want i can send you the copy of Sheet.
Thanks & Regard
Gandhi
Hello, I have a date in cell D4, let's say 1/2003 displaying as 2003 due to custom formatting to YYYY and a formula in E4(also set to YYYY) which is D4+4385 because I want E4 to display 12 years after the date in cell D4. This formula works great if I have data in cell D4. If I don't, it will display 1912 in E4. What formula can I add to E4 to make it blank if D4 is blank without deleting the formula =D4+4385? Losing hair as we speak :/
Is it possible to create an IF calculation that asks a "greater than this number, but less than this number" question? i.e., if E17 is greater than 1800 or less than 2400, than perform E17540*60%, otherwise place a zero. I get errors when I try this example...
=IF(E17>1800<=2400, (E17540*60%), 0)
Thanks
=IF(I10K10, IF(I10="",N10K10, IF(ISBLANK(N10),"2")))
the first one will be formula then the other if blank of i10 IS BLANK USE FOR N10 THEN IF N10 IS BLANK IT SHOULD BE EQUAL TO "2" THANKS
hi how to comine this formula thanks
SORI ITS N10.. THANKS
hello sir,
I want to know how to add a1 value.in a2, a3, a4.....
If i change value a1,all a2, a3, a4 ...value change auto.
Sir if my output value is 12100, 12200,12300, 12400 all value change auto 12500.
And my out put is 12600, 12700, 12800, 12900. That value change 13000.
Please help me check my error
=if((Q339,"D"),(Q3>49,"C"),(Q3>59,"B"),(Q3>69,"A"))
I am computing exam result
i am working on aproject. I need help with
f2 contains both words and numbers. How do i display the text and numbers in 2 separate cells?
got it
i need some help from you.
=IF(M9="yes", C9," ") How can i put this command in sheet 1 for the result of sheet2
Need a formula to calculate following: if city =x zip =y. I keep getting a value error.Note I have approximately 10 cities and zips to include.
Also need a formula stating: state always =CA
Thanks for the help.
Hi Svetlana,
Thanks for the great post, it helped me a lot. My case is a bit different. I want to use IF function in this way:
IF xxx has the word "thanks" inside it, then "welcome" or "ok".
I hope there is a way.
Thank you.
I need a formula to automatically display the current accrued PTO in a specific cell every time the excel sheet is open.
The accrued PTO is 6.15 hours every two weeks. I have created a table with the dates and the corresponding accrued hours for reference.
I tried IF and VLOOKUP statements but can't get the formula to work.
Can someone suggest a formula?
Any help will be greatly appreciated!
Thanks,
Raphael
Not sure if this can be done. Need to search a range in A1:A20 for a text string and then return the value in the same row but 2 columns right. So for example look in A1:A20 for "Find me" and if find me is in A4 return the value that's in C4. Any ideas if this can be done?
Hello I need to use a formula that will display YES if the amount is $100 or more and remain blank if it is under $100.
So far this is what I have =IF(B2>=100,"Yes")
I request to you i make a time , using if statement =if(A110,"A10")
I request to you i make a time sheet , using if statement =if((A110,"sum(A110))
i was making employee salary sheet , i am using three rows,1st row is total hours, 2nd row is basic overs & 3rd second row is over time , our company basic hours is eight.
Example :
total overs = A1=10 B1=12 C1=A .....Ext.
Baic overs = A2=8 B2=8 C2=A ....Ext.
Over time = A3=2 B3=4 C3=X...Ext
iam using basic overs A2 =IF(A1=8,"8",IF(A1="A","A"))))
A3=(=IF(A1=8,X,IF(A1=8.5,.50,IF(A1=9,"1",IF(A1=9.5,1.50,IF(A1=10,2,IF(A1="A","X"))))))
iam using this function ,but Sundays also working overtime . i need help you Sundays hours u need over time row which function use .......... please give him the reply
asskm..
saya ingin membuat satu rumus if :
jika A1 berisi desimal maka B1 adalah 1, jika A1 berisikan teks maka B1 adalah 0,
mohon bantuannya untuk bisa sy membuat rumus excelnya. terimakasih..
Please help, I need a formula like when you subtract A1B1 if the answer is 0.something the answer will be 0.
Like this one: =IF (A1B1<1,"0")
Thank you...
hi,
i need help please,
how can i add number if my result is less than 0
example;
=(P9DAY(TODAY())) and i got the result 1 i dont need to see  so i wanna add 30 how can i do that
thx
Hello,
I want to write an If then statement that takes cell contents and adds the same word to different cells. For example. suppose CELLS A3:A36 are words related to education (i.e. test, assignment homework etc...) Now suppose I want to add the word "calendar" to the words in cells A3:A36. Which would return "test calendar", "assignment calendar", "homework calendar" etc..
How would I go about doing that?
=IF(F2:F152="Yes", "G3"),(F2:F152="No", "G3") returned #VALUE! What is the issue?The logic I am trying to achieveshould be if F2 thru F152 = Yes then return the name G3 and if f2 thru f152 = No return the name in column G3. Can you give me the correct formula to achieve this?
i need to make a formula in excel to put bonus marks, for example if kevin got 25 marks he will got another 25 bonus, if he got 50 then another 25, each 25+ mark got bonus of 25... i will put only the mark on one cell and i need in the bonus mark to calculated by excel in another cell... please help
Please Help me :
=if(a1=2366,2355,2353 then b1 will be 120, if(a1=2377,2357,2354 then b1 will be 130)) how can i put the formula..
I have 3 cells (L3,M3, N3) with either true or false as responses to earlier coding but need to highlight where I get answers that are True,False, True in that order . How do I achieve this?
Hi,
I am hoping to create a formula to use in conditional formatting that would allow me to perform the following:
I have a list of employees in column A, date in column B, and number of sales in column C.
I am wanting to conditionally format the sales cell (in column C) as new sales data is entered. I want to compare the new sales figure against the most recent number of sales for that particular employee. With a reduction in sales of >3 formatted red.
Your assistance is greatly appreciated.
Hello, I am not sure if what I want done is possible. I am looking to say if "apple" is in cells a2:a36 and there is an amount in cells h2:h36 then I would like it to total those amounts. Is that possible?
I have two columns of numbers, side by side. New numbers are loaded into the left column each day (column 1). I want to save the historical high number in a cell beside it (in column 2). If the new number in column 1 is greater than the old historical number in column 2, I want the historical nigh number in column 2 to be changed to equal the new number that came into column 1.
How do I do that?
I have a spreadsheet that has maximum number of locations in column B and and minimum number of locations in column C that a client uses as a reference when choosing services. the client seeing these number enters their desired number of locations into column D which is where the formula would be entered. Want them to be able to put in their own number that will show on the spreadsheet BUT...I do not want it to let them enter a number that is lower than the minimum or higher than the maximum. I also do not want it to say false or OK I would like the number they have added to show.
Additionally if they try to go less than min or more than max, I would like a pop up box saying "You must enter a number that is less than the maximum and greater than the minimum number allowed"
Hi  need some help in extracting xxxx from a column that has"router bgp xxxx". xxxx varies across cells. What function can i use to extract xxxx?
Appreciate the help in advance!!!
Hi,
I need some help, I'm new to the formulation.
I need to create a subproduction plan link to another subplan
but I don't know at all how to do it.
Example subproduction have 2 shift opening stock,need create auto plan minus current balance 2 shift with quantity issue
I need to create a subproduction plan link to another subplan.
With opening 2 shift stock, current shift auto plan which minus stock issue from opening 2 shift stock to get return of 2 shift stock and 2nd auto shift will continue, once reach to one date which no plan, so auto plan automatically stop generate plan.
Could it be done.
Your deliberation on helping me is much appreciate.
Hi Svetlana,
I would like the next cell to represent if it is higher, lower, or equal to the previous cell. Each cell represents a total of items for a month. Ideally, it would be an up or down arrow (in green or red) for the execs to immediately identify changes.
Hi,
I am trying to figure out a formula for m aging. If its a negative number then its current, if it is between 129 days it's "30 Days", if it is between 3059 days "60 Days",if it is between 60119 days "90 Days" and if it is between 120365 days its 120 days.
Please Help!!!!
IN YOU TUBE I AM FEELING EASY TO UNDERSTAND ANY CONCEPT REGARDING EXCEL BUT IN GOOGLE IT IS LITTLE BIT COMPLICATED FOR ME AS BECAUSE THERE IS NO VIDEO LIKE YOUTUBE AND ALSO I AM A PLAIN GRADUATE.PLEASE DON'T MEAN IT WHAT I HAVE WROTE.I AM HAVING VERY EAGER TO LEARN EXCEL BUT I AM 35 YEARS OLD.PLEASE SEND EASY SOLVED SOLUTION OF EXCEL VIDEO IN YOUTUBE AND IN GOOGLE.
THANKS & REGARDS
Hey guys  it seems as though I came to the right place...I am using excel 2007 and trying to work a formula to check cell A1 for a value of 1 and if the value of 1 is there, then insert the current date in cell A2. Please help.
HI Svetlana,
I'm trying to write a formula that returns me 'true' or 'false' depending on whether there's a £ sign in a cell with numbers, ie: £32 gives 'true', but
€32 gives 'false.
I've tried your:
=IF(ISNUMBER(FIND("£",G7)),"true","false") and
=IF(ISTEXT(FIND("£",G7)),"true","false") ,
and both of them with SEARCH instead of FIND
but they don't work, presumably because £ isn't text or number. I vainly tried various things like ISCHARACTER and ISSYMBOL as well....
Hope you can help.
Thank you.
Geoff
Hi,
I want to have a formula whereby i am doing an itinerary costing. i have a start date and then dates with prices of accommodation...the dates with the price of accommodation i already have IF(B3=DATE(2015,1,1),"7500","0").....
Now I want to have a formula where by I when the start date of the itinerary changes, then all the dates in the in the itinerary change and this change in starting date will also lead to a change on the prices.
Thanks
Hi,
I'm looking for a formula that will return the last value greater than 0 from a range if the cell initially referenced is equal to 0.
Seems like it should be easy but I'm stuck on it.
Thanks
Hello,
I have a due date column that is calculated at 30 days after a transfer date column. However, when I put the formula "=A1+30" in the due date column it returns a date of 01/05/1900 for rows that don't have a date entered in the transfer date column. How do I get this to stay blank unless there's a date entered in the transfer column?
Thank you,
Jordan
Hello,
I ave a situation, i whish you can help me with: i need to work on a formula with the if funtion. i have to mark a range of values, for example: all the values are in the cell "B2"
range: 1  100
markA: 1  20 ( "a" )
markB: 21  35 ( "b" )
markC: 36  50 ( "c" )
markD: 51  89 ( "d" )
markE: 90  100 ( "e" )
i have try the next formula:
=(IF(B2=21,B2=40,B2=71,B291,"VFEL"))
but it says the is a error, can you help out ?
thanks
Hi Ussiels,
I am not sure I exactly understand the task, but it looks very similar to this one that can be solved using nested IFs.
Is this what you are looking for?
=(IF(B2=21,B2=40,B2=71,B291,"VFEL"))
it doesn´t paste all the formula.. sorry
Hi Everyone,
Nice Day !
All medical representative send their reports in 2 excel sheets,
1.sales Visit – with date,product,doctors list
2.Office Word
now i have to compare these 2 lists and make a new report that whether they have send report or not according to names and date.
if they send 01 mar, then i put yes
if they not send 02 mar, then i put no
can anyone tell me which formula can use to compare these 2 list and make it automatically “yes” and “no” according to medical rep names and dates.
Advanced Thanks for reply
Hello everyone,
I'm looking for a formula that looks at date ranges  so if the date of the line item is between 'date 1' and 'date 2' then display the line item in on a separate sheet.
Similarly if the date of the line item is NOT within 'date 1' and 'date 2' then display the line item on another sheet.
Any assistance would be greatly appreciated!
Hi Lorna,
You need two different formulas like these:
1. If between 2 dates:
=IF(AND("line item date">date1, "line item date"<date2), "line item", "")
2. If outside the date range (not within 2 dates):
=IF(OR("line item date"<date1, "line item date">date2), "line item", "")
If you can provide more details about your date structure (in what columns are dates and where are line items), I think we will get the real formula.
Hi there I am wondering if you can help me
I have the following data across columns and the desired outcome would be merge the cells sideways into a new cell and delete the duplicates.
Column J Column K Column L etc
Row 2: Article sign up  City1  City2 = Article sign up, city 1, city 2
Row 3: Article Sign up  Article sign up  City 1  City 1 = Article sign up, city 1
Row 4: Article sign up  Article sign up  City 2  City 2 = Article sign up, city 2
Can you help?
=if(A1=12," ","12"),if(A1=8,"16"),if(A1=16,"8")
can anyone fix it... it will a very helpfull for me...
thank you very much
Hi Aidil,
It looks like you have to either concatenate those 3 IFs or use nested IF as explained in this tutorial:
https://www.ablebits.com/officeaddinsblog/2014/12/03/exceliffunctioniferrrorifna/#excelifmultipleandor
If you can explain exactly what result you are after, I think we will get the right formula.
Hi,
in column A user will be entering date. In column B the month will get updated automatically with =Month(A1) function.
Now I want if the cell of A column is blank, then in column B will be showing "" & the moment user enter the date it will return the month.
What would be the formula in excel... pl surrgest
Hi!
I believe this is what you are looking for:
=IF(A1="", "", MONTH(A1))
How could we change the color of the Cell if the logical_test is true or false?
Thank you.
You cannot change the color with a formula. You have to use Excel conditional formatting for this, as demonstrated in this tutorial:
https://www.ablebits.com/officeaddinsblog/2014/06/10/excelconditionalformattingformulas/
Hi,
How can I restrict the cell in excel, where user have to enter the date in "MM/DD/YYYY" format only.
I am trying to populate a cell with the description "Current", "30 Days" and so forth.
I know I can use this Formula
=IF(L2>0,, "Current")
=IF(L2>30,, "30 Days")
=IF(L2>60,, "60 Days")
=IF(L2>90,, "90 Days")
=IF(L2>120,, "120 Days")
How do I make a full sentence (Combining all) of this so i don't have to do this individually?
Hi,
I'm trying to get my formula to say if I5=Y or N/A then 0 value, if not then 1 value. I'm using this: If(I5="Y""N/A",0,1) but I'm getting the value 0 no matter what's in I5. I5 is either Y, N, or N/A, and I only want it to populate when there's an "N". I thought the problem was that "N" and "N/A" both contain "N", but I typed in "fill" to test and still 0 value. Please help. Thanks!
Hi Felicia,
Since you have 2 conditions, you should include the OR statement in your formula, like this:
=IF(OR(I5="Y", I5="N/A"), 0, 1)
Hi Svetlana how would you write this all in the same cell ... (If Cell A1 says “PUT” then Subtract cell B1 from C1 OR If Cell A1 says “CALL” then Subtract C1 from B1)
Hi Jason,
You need a nested IF like this:
=IF(A1="put",C1B1,IF(A1="call",B1C1,""))
HOW TO MULTIPLY ONE VALUE OF A CELL BY THE PERCENT OF ANOTHER CELL
Hi Thello,
For example like this:
=A1*(A1*10%)
If you are looking for something different, please provide a bit more details.
i need help putting this in the if function, if an employee has a taxable income of greater $739, then the federal income equals $110.85 plus the federal tax rate found in cell B19 multiply by the taxable income excess of $739. if an employee taxable income is $739 or less, the federal tax witheld equals the taxable income multiplied by the federal taxrate found in b18
Thank you Svetlana! To take this one step further and I tried couldn't get it to work
=IF(A1="put",C1B1,IF(A1="call",B1C1,"")) then take answer of this and calculate the difference between the answer and D1 *100* E1
This is more in line with what I was thinking all wrapped up in one formula  =IF(A1="put",C1B1,IF(A1="call",B1C1,""))  This is the first operation
IF cell O25 is a positive number subtract U25 or IF cell O25 is a negative number add U25  This is the second operation
How would you write them separate and within on formula sequence?
Thank you again!
Hi Jason,
Please specify if these are AND or OR conditions?
For example, if A1="put" and O25 is a positive number, then what do we do?
If A1="put" and O25 is a negative number, then... ?
And the same for A1="call", please.
What I believe I'm trying to do is calculate the positive or negative variance (on a non absolute basis)
My apologies, so I have your formula =IF(A1="put",C1B1,IF(A1="call",B1C1,"")) ... in column "U"
And in column "V" I'm trying to calculate the actual difference between cell O25 and U25(i.e. Add if a number is negative, Subtract if a number is positive)say if U25 Is positive and O25 in positive then O25U25 or if U31 is negative and O31 is positive then U31+O31
I was also would like to combine this formula with your =IF(A1="put",C1B1,IF(A1="call",B1C1,""))
Let me check if my understanding is correct. If values in both columns U and O are positive, then O25U25. If a value in either column or in both columns are negative, then U325+O25. If so, I would suggest that you keep the formula in column U as is and put the following one in column V (it is actually for cell V25, but you can easily adjust the references:
=IF(AND(U25>0, O25>0), O25U25, U25+O25)
Also, please note that the formula equates zero to negative numbers. If you want to treat 0 as a positive number, then change the logical test to AND(U25>=0, O25>=0)
If I misunderstand something, please correct me.
Nope you made my nonsense into sense;) So in that last formula =IF(AND(U25>0, O25>0), O25U25, U25+O25)... how would I make any negative result display 0 and any positive result show the full value of the calculation?
If somebody can please help me.
Mentioned below is my problem
I have a time format and I am trying to put an If formula which should give me a yes or no if its greater than or equal to a specific time
FOR EXAMPLE:
If (04:07:47)is greater than or equal to (04:00:00)then YES else NO
Hi Raymond,
If both times are entered in some cells, then you can use a simple IF formula like this:
=IF(A2>=B2, "yes", "no")
Where B2 is 04:00:00.
If you want to specify one of the values directly in the formula, then you will need the TIMEVALUE function is addition to IF:
=IF(A2>=TIMEVALUE("4:00:00"), "yes", "no")
I've two columns A & B, out of this i want to check against A column name if B column is not blank then it should show complete else Partial, for more understanding see below example
CA CB Output
A ABC12 Complete
A 105 Complete
A 13 Complete
B 213 Partial
B ABC12 Partial
B Partial
Hi Rajendra,
I am afraid our blog engine has made a mess of your data :( I understand about B column being nonblank. But exactly what shall we check against A column?
Thanks a lot Svetlana that worked as expected :)
Cheers
I have created a spreadsheet for working out the pay of our employees. The data starts in Row 14. Col A contains that week's pay date eg. 24/3/15 etc. Col B has the total hours the employee has worked that week. Column C then multiplies the hours from Col B by the employees pay rate to give me their gross pay for that week. Cols DF contain various deductions relevant to that employee & Col G lists net pay. Following that there are 4 more rows containing the above info with the hours listed as 0 unless it is used for that week's pay, and the sixth row adds up the totals for that month & displays them as *??.??.
In cell b3 I want a formula that tells me how many hours the employee worked in the last 4 weeks e.g..it will look down column B, find the last hours entry (ie. the last number that is greater than 0 & does not have an *), then add to this number the previous 3 weeks hours (ie. the previous 3 numbers greater than 0 & without an *). If it makes it easier I am happy to enter in the cell reference that contains the last hours entry (rather than Excel working that out) as I am not going to need this information on a regular basis.
In cell b4 I want to do do the same thing as b3 except i want to know how many hours the employee worked in the last 52 weeks (or if they haven't actually worked 52 weeks yet, their total hours so far).
I'm sure that this is not actually a difficult formula to write but excel is not my forte! Please let me know if you need further clarification.
Hi, Svetlana!
I'm trying to make an Excel 2013 formula that will extract a person's last name from a cell that has their full name. Unfortunately, the fullname cell can contain any of these formats:
First Last
First M. Last
F. Middle Last
F. Middle Second_middle Last Jr.
The formula to extract the last name form a "First Last" cell is easy (where A5 is the fullname cell):
=RIGHT(A5,LEN(A5)FIND(" ",A5))
...but I need to be able to find the last instance of a space character to lead off the RIGHT() function location. That would work for the first three examples, but not the last. Not sure what to do about that one, unless maybe an exclusion list of common suffix titles?
I'd be most interested in your help.
Cheers!
Ted
How many times i can add IF in one formula.
In Excel 2013, Excel 2010 and Excel 2010, you can add up to 64 nested IF functions. In older versions of Excel, you can use a maximum of 7 IFs in one formula.
can you help with the below:
i am creating a sheet for associates in my company. 1 tab has all details of the associate that a company database should have. column 'F' has the designation of the associate and column "A" has names
what i want to do (in another tab in the same excel). wherever column "F" has designation as manager the cell in the new tab should show me the name of that associate.
eg: if cell value f4=manager then value in new cell (in new tab) should be name as in A4.
i want the same to be applied to every cell.
Hi Ankit,
Hopefully, this is what you are looking for:
=IF(F4="manager", A4, "")
I created a quiz and used data validation. I want to get 1 mark for the answer of 42 otherwise 0. I used if(A4="42", 1,0).it give me 0. am I using wrong syntax
Hi Istkar,
You don't need to enclose numbers in double quotes unless you want them to be treated as text strings. Once you remove "", your formula will work great:
=IF(A4=42,1,0)
HI,
I WANT TO CREATE IN EXCEL 2007 Which shows that in a selected cell (specifically Column K in spreadsheet in each cell downwards)if I will type a Value on it(numbers. 19) it will correspond to a word. say for example 1 means okay, 2 means not yet and etc. some sort of shortcut techniques in typing for that word. what kind of Formula should I use? Please let me know.
Thanks Svetlana!
Hi JAS,
You can use nested IF functions, like this:
=IF(A1=1, "ok", IF(A1=2, "not yet", IF(A1=3, "smth else", "")))
Excel 2007 allows adding up to 64 nested IF in one formula.
Great, thanks a lot!
hi,
In my data all the values are in Indian currency in rupees. While I create pivot table summary, all the values comes with in rupees. But I needs those figure should come in Lacs.
Thanks in advance.
I need to use IF function on below case;
If one item is available from a column, then Yes or else No. Please advise the fuction.
Hi Jay,
Here is an example:
=IF(A1="item you want", "yes", "no")
i need help, i have data which i need to analyse, in a cell i have entered data for males and females with the figures of household size, e.g male 2 and female 3, so i need a formula to find the total of males and females separately.
Hello Givious,
I believe you need a SUMIF formula. Please check out this tutorial:
https://www.ablebits.com/officeaddinsblog/2014/11/04/excelsumiffunctionformulaexamples/
( >90 then 4, 8090 then 3, 7080 then 2 and <70 then 1
i tried to put a formula on this but its not working.
can you please suggest
Hello Rupesh,
You need a nested IF formula like this:
=IF(A2>90, 4, IF(A2>=80, 3, IF(A2>=70,2,1)))
Hi, i am wondering if it can be done when i sale an item and lets say inventory is 20 so i sold one and inventory changes to 19 i want a column to go plus every minus i do in the inventory.
I am trying to run an IF formula this is what I was trying to do =IF(A1>0,[A1=B1],[A1=0])
Basically if cell A1 is greater than zero I want the cell to be replaced with the data from cell B1, however if cell A1 is not greater than zero I want the cell to equal zero.
Can someone please help?!!!
Hi Mel,
You don't want square brackets in IF formulas:
=IF(A1>0, B1, 0)
Hi I want to use this formula with =If condition
=RIGHT(A2,LEN(A2)FIND(" ",A2,12))
i tried many time but it not fits in.
=if(A2<len(9),"Write your full name,RIGHT(A2,LEN(A2)FIND(" ",A2,12)))
why this is not working????
Please guide me
Hi Nadeem,
Sorry, I cannot understand what your RIGHT formula does. Can you explain in words?
Anyway, the IF function may look similar to this:
=IF(LEN(A2)<9,"Write your full name", RIGHT())
It returns "Write your full name" when A2 contains less than 9 characters. Otherwise, what is it supposed to return?
i want a formula in excel that will return a value with it superscript Eg 1st
I've got two columns of numbers to compare and see if the difference is + or  5. How would I make a formula to do that?
I need a formula for:
If A1=1, then B1:B4=1, otherwise B1:B4=0. If A5=1, then B5:B8=1, otherwise B5:B8=0.
I can also do:
If A1=1, then A2:A4=1, otherwise A2:A4=0. If A5=1, then A6:A8=1, otherwise A6:A8=0.
When I try to copy this formula down to record 10,000 I am getting a loop error. Excel is returning:
A1
1
0
0
0
1
0
0
0
How can I fix this??
Hi Brianna,
Try entering the following formula in cells B1 through B4, and A2 through A4:
=IF($A$1=1, 1, 0)
And this one in cells B5 through B8, and A6 through A8:
=IF($A$5=1, 1, 0)
Hi,
If you are give amount that between 6 to 10 is equal to 0.5 however I have to formulate it in such a way that below 6 it is to be number divided by 24 and above 10 it must be number divided by 24.
If(A3>0=6=10,Number/24,"")))
please how do I go about.
Hi, I wanted to know how can I get numerical value for 18 and below and 46 and above
thanks
Hi Svelana,
How are you today. I appreciate the time you're taking to help me with my problem. I'm sure all these individual questions can be burdensome. My question is:
I have the first 80 cells numbered 180 in sequence. I have this in every row as well, and then after that I have 20 columns each with a number 180. I wanna highlight one of the 1st 180 if one of the 20 cells has a number that matches it. Is this possible?
Thank you,
Hi Eddie,
Sorry, I am not sure I fully understand your data structure. If you could post your sample workbook on our forum, our support team will do their best to help.
Hi Guys,
I am new to VBA. I need a VBA macro code that will convert HTML data into Text in my microsoft 2007 excel.
one column A contain n number of rows data with HTML data I want to run a macro so it can be convert to Text data in just near by column that is B.
Hello,
I have a workbook with multiple sheets. In column A, of sheet 1, is a list of names. I have been able to get those names to populate on sheet 2 and three using ;
=IF(INDIRECT("Sheet1!A" & ROWS($1:1))="","",INDIRECT("Sheet1!A" & ROWS($1:1)))
This works well to a point. What I would like to happen is when a name is deleted from column A, sheet 1, I would like the data in sheets 2 and three in the corresponding row to be deleted as well. i.e. if row 17 (AL) on sheet 1 is deleted, then row 17 AL) on sheets 2 and 3 will be deleted.
Thanks
There was a simple fix. Just group the sheets together. Adjust as nessasary. And then un group.
I want to test the value of a portion of a cell. For example: The cell says Approved: 12345 where 12345 is a changing order number, the list has Declined and Refunded transactions as well. Is there a way to test only for the word Approved without consideration of the remaining data within the field?
Hello Krystalya,
You have to use ISNUMBER and SEARCH function for partial match, like this:
=IF(ISNUMBER(SEARCH("approved",A2)), "yes", "no")
Where A2 says "says Approved: 12345".
Please check out "Example 3. IF formula for text values with partial match" in this tutorial for full details.
I want to write a formula where if the value in a particular cell is less then 18 I want the cell to indicate the cost is $48 but if the value is greater than 18 then I want the value to be $48 plus the value greater than 18 (or the value +30)
if A10 < 18 then = $48.00 but if A10 < 18 then = (A10 + 30)
Can this be done?
Figured it out, thanks =IF(E225>19,E225+30,"$48.00")
I need help with a if statement formulae
The formula is:
=IF(H18='Booking Date Log'!$J$13:$J$1048576, "Not Available", "Available")
H18 contains a date e.g. 9/9/2015
$J13:$J$1048576 contains a range of dates e.g. 9/9/2015 9/10/2015 ....
True Value = "Not Available"
False Value = "Available"
what i need is that if H18 = any of these cells ($J13:$J$1048576) for it to come as "Not Available"
and if H18 doesnt = any of these cells ($J13:$J$1048576) for it to come as "Available"
Can this be done?
thanks
Fraz
I am doing an excel on monthly growth of values based on employees. Issue is some people start during the month and some quit. So I have some people with no value at the beginning of the month and other with no value at the end. I do not want those to show up as values.
A B C D
1 1/1/15 2/1/15 Growth Percentage
2 225,749 248,324 200,471 88.8%
3 420,284 420,284 0%
4 48,744 48,744 0%
For D2, I did =IF(OR(A2="",B2=""),,(B2A2)/ABS(A2)) which gives me the percentage of growth through the month and returns 0% if either is blank. Can I make column D actually be blank instead of showing 0%? Or, I want to show a total average growth at the bottom of column D without being penalized for all the 0%. If D5 was going to be the "average" cell, could I do =AVERAGE(D2:D4) but somehow tell it to not include the cells which have a value of 0%? Some people have a negative percentage and I need to include those.
Well that table got all messed up after submitting it. Column A is 1/1/15's value, column B is 2/1/15's value, column C is the growth from 1/1 to 2/1 and column D is the percentage that they grew (or dropped) over the month.
Hi Doug,
I would add "" to value_if_true to return an empty string if either A2 or B2 is blank.
=IF(OR(A2="",B2=""),"",(B2A2)/ABS(A2))
if i5 is less then D5 value is zero and if D5 is greater then I5 value is D5 is show
=IF(I5<D5, 0, D5)
The formula will return 0 if I5<D5, and the value in D5 if I5>=D5. If you are looking for something different, please clarify.
Hi Svetlana,
I tried your formula here below to replace a text into a number and it works fine.
But I do not actually know how to make it work for different datas on the same colomn.
Let me quickly explain.
I'm collecting in 1 sheet customers feedbacks from 7 different languages (sheets).
So in the same colomn A, I have "Yes, Oui, Ja, Si, No, Non, Nein...." answers.
The formula works fine for 1 language "=IF(A2="SI",1,0)" but do not know how to add the others..I would need number 1 to appear if it's an answers "Yes, Ja, Si, oui.. and a 0 if it's a "Non, No, Nain...".
Any idea how can I compile all of this in one formula please?
That would be much appreciated if you could help with this.
Thanks a lot,
Erin
first of all ,thanks in advance for the people whom create these very nice tutorials.I wanted to do math operation on the results of nested IFs.how could it possible with one formula??? my formula is as this:
=IF(HOUR(D26)> 0; HOUR(D26)*45357; IF(MINUTE(D26)> 0; MINUTE(D26)*755.95; IF(SECOND(D26)> 0; SECOND(D26)*12.6; 0)))
I wana add a statement to above formula in order to sum the true values , it calculate the first but ignores others!!!! I wanna sum hour+minute+second
= IF(A10,A1, IF(A20,A2,IF(A30,A3,"BAD")))
BAD is mean all 3 cells are blank
Hi!
Sorry, I am not sure I can follow you. Can you explain the conditions (logical tests), please?
I have values in cells E6,H6,K6,N6,Q6 & T6 that I want added together. The values in these cells are from vlookups from other sheets in the workbook. Any of those 6 values (E6,H6,K6,N6,Q6 &T6) could contain a blank or have returned nothing as a result of the vlookup. I need to total these numbers but if I put a simple addition formula in I get an error because of the possible blanks. I am thinking it needs to be a complex nested if sentence but can't figure out how to tell it that I need it to add all the numbers and discount those that are blanks or empty cells.
Hi,
i need the if condition formula for the below i need to allocate some of the qty so if total qty is less 5000 need allow 25 Pcs like details are below
Qty Allow Qty
12500 400
Hi Svetlana,
Could you please help me with my question below?
I'm collecting in 1 sheet customers feedbacks from 7 different languages (sheets).
So in the same colomn A, I have the answers "Yes, Oui, Ja, Si, No, Non, Nein...." or "Non, No, Nain...".
The formula works fine for 1 language "=IF(A2="SI",1,0)" but do not know how to add the others..I would need number 1 to appear if it's an answers "Yes, Ja, Si, oui.. and a 0 if it's a "Non, No, Nain...".
Any idea how can I compile all of this in one formula please?
That would be much appreciated if you could help with this.
Thanks a lot,
Erin
Hi Erin,
You can add multiple OR conditions in your logical test, like this:
=IF(OR(A2="SI", A2="Yes", A2="Ja"), 1, 0)"
Hi,
I have a drop down selection in Column M with the choices: "China  UK", "China  EU", "China  ME"
In column P I have a formula: =INDEX(Products!M:M,MATCH(C5,Products!$B:$B,0)) to automatically populate the duty rate for bringing into UK/EU  this is a variable percentage depending on the product.
If in the drop down "China  ME" is selected I want to override column P with conditional formatting to show 5% which is constant for all products
I tried to put the two formulas in priority order on conditional formatting
=IF(M5="China  ME", "5%", "0")  selected stop if true
=INDEX(Products!M:M,MATCH(C5,Products!$B:$B,0))
Can you please tell me where I am going wrong?
Kind regards,
Alex
Hi,
i shall appreciate if you could please guide me as to how I can use IF function formula while making general journal (Accounts) to auto post entries into respective ledgers(account heads/expense heads) ?
I look forward for a positive response.
Thank you,
Danish Abbasi
Hi Danish,
Please try to explain the task from another viewpoint. In what columns do you have data, and where do you want to auto post data and based on exactly what conditions?
Hi,
How would I write the formula to get the following:
If A1 3 but 6 BUT 9 But <=12 "Q4"
THANKS IN ADVANCE FOR YOU HELP!!
Michelle
Hi,
How would I write the formula to get the following:
If A1 IS BETWEN 1 & 3 then "q1" however if between 4 & 6 then Q2, If between 7 & 9 then q3...
Thanks!
Mishaal
Hi Michelle,
You can use a nested IF formula similar to this, beginning with the largest values:
=IF(AND(A1>=7, A1<=9), "q3", IF(A1>=4, "q2", IF(A1>=1, "q1", "")))
If there is no upperbound limit, it can be simplified it to:
=IF(A1>=7, "q3", IF(A1>=4, "q2", IF(A1>=1, "q1", "")))
Hi,
Thank you in advance for your time. I am trying to creat an IF formula with a logic test that recognizes the most current date in the row and if the date is within 6 days of the todays date another cell will indicate NOT AVAILABLE
So far I have IF(???>6;AVAILABLE;NOT AVAILABLE)
I dont know how to create the date formula for the row only for a specific cell.
Thank you
Hi Stephanie,
You should use the MAX function to determine the most recent date in a row and then compare it with TODAY(). For me to be able to suggest the right formula, please clarify the conditions. I assume "within 6 days of the todays date" means within the next 6 days, right? And what shall the formula display if the most current date in the row is a past date? And what if it is more than 6 days in the future?
Row A: Will show "Open" if the date entered in any cell in that row is more than 6 days from the current date or show "Closed" if ay date in the row is within 6 days of the current date (future or past).
So far I have =IF(??TODAY()>6;"OPEN";"CLOSED")
Thank you
Hi Stephanie,
Try this one:
=IF(ABS(TODAY()MAX(A2:N2))<6, "open","closed")
Where A is the first and N is the last column containing dates.
Hi Svetlana, It would be great if you could help, I think I'm nearly there but getting frustrated :)
=IF(AND((E4AG4>=5),50,0),IF(AND(E4AG4<=5),50,0))
I'm building a sports sheet, whereby E4 is the actual spread and AG4 is the predicted spread. If the predicted spread is within +5 0r 5 then 50 points are awarded.
Appreciate your help
Kind regards
Barry
Hi Barry,
The correct syntax of your formula is as follows:
=IF(AND(E4AG4<=5, E4AG4>=5), 50, 0)
And you can make it even more simpler by using the ABS function that returns an absolute value regardless of the sign:
=IF(ABS(E4AG4)<=5, 50, 0)
Thank you Svetlana!!!
Hi! I am trying to use an IF Formula to provide me a date result.
=IF(H2=S, [Approval]+(7*[Estimated weeks from Start to Approval]),IF(H2=P, [Start]+(7*[Estimated weeks from Start to Approval])))
I'm not sure what to enter at the end for the result to show a date.
Thanks!
=IF(AH5>69, AG5*5, IF(AH5>59, AG5*4,IF(AH5>49, AG5*3,IF(AH5>44, AG5*2,IF(AH5>39, AG5*1,IF(AH569) and so on.
pls, can you help
Hi Fred,
The formula looks good. You just need to finish it properly, by closing all IF statements. I.e. the number of closing brackets should match the number of opening ones. Also, in the value_if_false argument of the last IF, enter the value to display when none of the conditions are met  it's an empty string ("") in the example below:
=IF(AH5>69, AG5*5, IF(AH5>59, AG5*4, IF(AH5>49, AG5*3, IF(AH5>44, AG5*2, IF(AH5>39, AG5*1, IF(AH5>29, AG5, ""))))))
HELP! :( Working on a sheet I have made a drop down in a cel and I would like the cel next to it to enter text if a certain word is selected in the drop down, is this possible?!?! I am not very good at explaining so I can try my best with whatever questions you have!
For example, if the drop down menu is selected Oranges I want cel G to now have Bananas in it, but if from the drop down Grapes is selected I want cel G to now say Grapes, and so on and so on, honestly have 27 so far in the drop down and need an IF in G if something is in the drop down menu, is this possible?!?!
Hi Ryan,
If you want a cell next to your drop down list to display the same value as currently selected in the dropdown, all you need is a simple formula like =$A$1 where A1 is the dropdown cell.
If you want to display different values, then you should use nested IF functions, like this:
=IF(A1="bananas", "bananas2", IF(A1="oranges", "oranges2", IF(A1="grapes", "grapes2")))
hi , can you help with my sheet ? I can't translate it to codes in excel .this is how i read it "if e20 is less than j21 and d21 , i should get the cell with the highest value in f11 to f15 cells . thanks .
Hi Clarence,
Here you go:
=IF(AND(E20<J21, E20<D21), MAX(F11:F15), "")
it's not working :'(
Hmm... strange, it works just fine on my test sheet.
The formula is in A1.
=(D3/B3)if(B3="0"),D3,E3 I get error please help
D3=hour B3=Sales E3=final number
Hi Daniel,
Sorry, I cannot follow you. Can you explain the condition (logical test), please?
Hi, im trying to place a formula that, according to a "word" I can get a number as true or false value.
ex.: A1= Casa .......... B1= If=(A1Casa,"2","5"), something like that.
Thanks in advanced. :)
Hi Roman,
I believe you meant =IF(A1="Casa", 2, 5)
Yes,sorry...... but I get a warning that I shoud put an apostrophe, but is possible to make work somehow?
Most likely this is because you have ";" set as a List separator (you can check this in the Regional settings). If this is the case, then try =IF(A1="Casa"; 2; 5)
Sweeeeet, perfect, thanks Svetlana :)
Sorry again, work at prefection, but something else that I want to know if possible, Is possible also to make a Image to show in a cell linked to a word in a formula?, if possible can you direct me to a link of how to do it?, thanks again :)
Hi,can you help me with formula if for this condition, if F3=A,D3=1,the result for H3=C3 & if F3=A,D3>1 the result for H3=1 in 1 formula if. Thank you :)
Hi Pichan,
Here is the formula for H3 as per your conditions. If nether condition is met, it will return a blank cell (empty string).
=IF(AND(F3="A", D3=1), C3, IF(AND(F3="A", D3>1), 1, ""))
Hi,
I have a formula that is returning "Recent" in cells that do not contain dates.
=IF(C6>DATEVALUE("4/21/2014"),"Recent","")
Cell C6 = "1 File(s)"
What can I do to exclude cells that do not have dates?
Never mind this, got it figured out.
Hi Svetlana
I would like to create in "IF" statement or an "ISBLANK" statement that says that if C2 is blank, populate from D2. If C2 has text, do nothing.
=IF(C2 is Blank, Populate from D2) or =IF(ISBLANK(C2)=TRUE,D2,0)
Thank you!
Hello Donni,
You can use either of the below formulas:
=IF(C2="", D2, "")
If cell C2 is "visually blank" (including an empty string returned by some other formula, if any), the formula returns a values from D2, otherwise returns an empty string.
=IF(ISBLANK(C2), D2, "")
The second formula returns a value from D2 if cell C2 is absolutely blank (no value, no empty string).
Please see the Excel IF examples for blank, nonblank cells section in this tutorial for full details.
Hi Svetlana,
I need to create the following if statement.
IF the member type in B6 = "TC" then take the date from B3 and + 10 Days, If the member type in B6 = "T2C" then take the date from B3 and + 15 Days.
Hi Adam,
Here you go:
=IF(B6="TC", B3+6, IF(B6="T2C", B3+15, ""))
Please remember to change the format of the cell with the formula to the Date format so that is displays the date correctly.
Thank you for the article. I don't know if there is a function to do what I want, but here is basically what I am looking for.
I have a list of items that I have set to display on a different sheet. I would like a large number of those items (those that start with a 4) to be combined into one cell with a different name in the same list (The 400 Series). Is there a function that will keep my list intact, but combine all the names that start with 4 into my new cell? I don't need a count of them by the way, just the list to transpose itself over as it is now with those specific cells pushed down into one.
Thank you for the help.
As a follow up and to complicate my question, I am hoping to establish a function to do this dynamically every day when I import new information. If I were doing it only once, I think I could just remove the "4" items and put in a new block, but this would become a timely process if I were doing it every day, because I have the list being dynamically generated, which would bring back all my "4" blocks with the new import each day.
Can I assume my question is too complicated?
What is the formula when my conditions are;
If 0<= A < 10 the result is 3
If 10< A < 15 the result is 2
If 15 < A 20 the result is 0
Assuming that the variable A is the value on a certain cell.
Thanks
Forgot to mention;
if 15< A < 20 the result is 1
Hi Faith,
Here you go:
=IF(AND(A1>=0, A1<10), 3, IF(AND(A1>=10, A1<15), 2, IF(AND(A1>=15, A1<20), 1, "")))
Hi,
I am currently working with a schedule, and I am trying to make an IF function that if I have a table that will show me who is working each shift, it will look at the schedule and pull the person that is scheduled at a certain time.
An Example:
If I have person A scheduled at 11:15 AM and I want there name to be pulled into the table which shows all the employees that work at 11:15 AM how would I do so. Currently the function I am trying to use (=IF(B13="11:15 AM", A13) is not working. B13 is representing the starting time of the shift, while A13 is representing the Employee name. I would greatly appreciate any help you can give on this matter, thank you!
Hi Phillip,
When comparing dates or times in Excel, you have to use the DATEVALUE and TIMEVALUE functions, respectively, in order to convert a date or time unit in the text format to a serial number that Excel recognizes as a date / time. For example:
=IF(B13=TIMEVALUE("11:15 AM"), A13, "")
I need help figuring out 2 calculations
1.If cell is blank, then leave cell blank, but if cell has a value then add another cell.
2.Total cells and then divide by cells that only have value. Example if there are 10 cells, but only 5 have value, you would divide by 5.
Hi Toni ,
You can use formulas like these:
1. =IF(A1="", "", B1)
A1 is the cell you check for being blank/nonblank, and B1 is the cell you add if A1 is blank.
2. =SUM(A1:A100)/COUNTIF(A1:A100, "<>"&"")
Hi There,
I use this formula but nothing is coming out:
=IF(B6>2),"3.86"
what i need is if b6 is > 2 i need excel to put 3.86 and 3.99 if b6 is <= 2
Thanks
Hi Teame,
Please try this one:
=IF(B6>2, 3.86, 3.99)
Mant Thanks Sve.
I want to replace F2 with value in G2 if value in F2 is 0.
Rukia,
It is not possible to have a value and a formula in the same cell.
how to create a subscript in an "if()" function? for example if(1<2,"FS","") the "S" should be in subscript. thank you!
Please help me providing with encoding stating Week 1 Week 2 Week 3 Week 4 so on for the Year, to come automatically depending on Date in other cell.
Hi can you help me formulate one formula for these situations:
1. If the actual completion date (F9) is less than or equal to the target completion date (E9) then it's ON TIME
2. If the actual completion date (F9) is greater than the target completion date (E9) then it's OVERDUE
3. If the target completion date (E9) is greater than the date today (B5) and the actual completion date (F9) is unfilled it's NOT YET DUE
4. If the target completion date (E9) is less than or equal the date today (B5)and the actual completion date is unfilled it's UNDONE
My formula I have right now is:
"IF(F9E9,"OVERDUE",IF(E9>B5,F9="","NOT YET DUE",IF(E9<B5,F9="","UNDONE"))))
But it says I'VE ENTERED SO MANY ARGUMENTS
Thanks!
Hi Tetay,
You have to use the AND function when entering more than 1 condition, like this:
=IF(F9<=E9, "ON TIME", IF(F9>E9, "OVERDUE", IF(AND(E9>B5,F9=""),"NOT YET DUE", IF(AND(E9<=B5,F9=""), "UNDONE", ""))))
Thanks but it does not work for the NOT YET DUE and UNDONE. I think there is something missing?
Because it still displays ON TIME even if the actual completion date (F9) is unfilled.
Oops... I forgot to check if F9 is not empty in "ON TIME" and "OVERDUE" logical tests.
Please try this one:
=IF(AND(F9<=E9, F9<>""), "ON TIME", IF(AND(F9>E9, F9<>""), "OVERDUE", IF(AND(E9>B5,F9=""),"NOT YET DUE", IF(AND(E9<=B5,F9=""), "UNDONE", ""))))
OMG Svetlana! Thank you so much, it's working. YOU'RE AWESOME
Dear Svetlana Cheusheva
Is this formula correct
=IF(H2="#N/A", "No", "Yes")
where #N/A is a result of vlookup which is applied in H2
Please help i am stuck here
Hi Khan,
You have to use the ISNA function to check another cell for N/A error, like this:
=IF(ISNA(H2), "No", "Yes")
Really v thankful to you it worked
Thanks Svetlana, I was using ISNA but wrong, this helped.
Hello,
I have the following formula so far:
=IF(A1>=40,"40",""), If the value is less than 40 I need the result to be the exact value in the cell.
For example if A1= 39.5 I need the result to be 39.5 not blank as I currently have it in the formula. What can I replace "" with to get this result?
Thank you!
Hi Olga,
Just replace "" with the cell reference, like this:
=IF(A1>=40, 40, A1)
A B
10 0
Please help, B=A"Paid", B=0"Outstanding" and 0<B<A"Pending" Thank you!
Hi Kea,
Here you go:
=IF(B1=A1,"paid", IF(B1=0,"Outstanding", IF(AND(B1>0, B1<A1),"Pending", "")))
Hi Svetlana, I working well thank you again for your help and sorry for late checking
if value entered B 1 40 to 45 the value C 2 36 and if enter 46 to 50 , the value C 2 37
Here is the formula for C2:
=IF(AND(B1>=40, B1<=45), 36, IF(AND(B1>=46, B1<=50), 37, ""))
Using the data in C16:C18, generate in Cell E16 the formulation that will check Cell C16 for a Y, an N or any other character and if this cell contains a Y the formulation will round the figure in Cell C18 (19.2589) to the number of decimals indicated in Cell C17 (3), if Cell C16 contains an N the formulation will not round the figure in Cell C18 and simply return the number that is in Cell C18 and if Cell C16 contains any other character, the formulation will return the error message “Invalid Character”.
Would this be an IF function?
Hi..I am currently working on sales report and using IF formula. However I encountered IF error message  "IF only takes 3 arguments but this is argument number 4". Appreciate your help on this....thank you
Hi Alynda,
Yes, the syntax of the IF function allows for 3 arguments only. You can find the detailed explanation of each argument at the beginning of this article. If you provide more details about your formula, we will try to get it right.
hi svetlana
i am working as a planning officer in textile. i have to make/maintain multiple sheet. can you help me?
imran
i want to make a mark sheet with result of different students, i want result fail if value=<34, then what will be the arguement, how can i write the cell range in formula. i have done it, but it is wrong! plz help
Hi Svetlana
Im trying to make a dynamic sheet for car configurations according to track and weather conditions, not sure if possible specially if they are around 30 tracks or is limited to X number of tracks. The idea is like this
A1= Track01, Track02, Track03, Track04, etc......
B1= Dry, Wet
C1= IF((A1="Track01" and B1="Dry";1;3)(A1="Track02" and B1="Dry";2;4)(A1="Track03" and B1="Dry";3;9)(A1="Track04" and B1="Dry";4;12)) etc.....
Thanks in advanced :)
Hi Ernesto,
You can express all of your conditions using nested IF's and AND. For example:
=IF(AND(A1="Track01", B1="Dry"); 1; IF(AND(A1="Track01", B1="Wet"); 3; IF(AND(A1="Track02", B1="Dry"); 2; "")))
Works at perfection, thanks :)
I want to add the value of certain cells only if the value = 1.
I have a list of guests to attend an event; I conditioned the cells so if they are attending and I type a 1 the cell goes green, 2= RSVP TBD and cell goes yello and 3= not attending and cell goes red.
I want to know the TOTAL number of guests attending (I will have to add all the cells if the value = 1)
I tried:
=IF(C3:C18=1, SUM(C3:C18)) but that returns the addition of all the numbers in that range of cells, regardless if they are 1, 2 or 3)
THANKS!
Hi Manolo,
All you need is a simple SUMIF formula like this:
=SUMIF(C3:C18, 1)
For more info, please check out our Excel SUMIF tutorial.
Hi Ms. Svetlana,
Can you help me on this?
IF today's date, range from 1 to 15; my date input should be mm/15/yyyy in a cell.
But IF 16  31; date input should be mm/30/yyyy.
I have formula but not working properly:
=IF(DATE(YEAR(TODAY()),MONTH(TODAY()), TODAY())>15, DATE(YEAR(TODAY()),MONTH(TODAY()),30), DATE(YEAR(TODAY()),MONTH(TODAY()),15))
Hope you can help.
Many Thanks!
Hi Gibson,
Try this one:
=IF(DAY(TODAY())<15,DATE(YEAR(TODAY()), MONTH(TODAY()), 15), DATE(YEAR(TODAY()),MONTH(TODAY()),30))
Whoa! Thanks for the response. Many Thanks! :)
Btw, do you have any eBook I can buy?
All I have is the articles published here on ablebits. But thanks for the idea :)
I wanted to use if function of changing last digit of 3 digit number.
example: 102, 103, 104, 105, 106
2 is admin
3 is HR
4 is Finance
5 is research
6 is field staff
If A1 in sheet1 contains "AAB00001A3" and A5 has 45
A2 in sheet1 contains "AAB00001A4" and A5 has 54
I want add 45 with 54 using IF function Comparing A1 and A2 with "AAB00001" which is there in other sheet and cell.
Please help.
dear all
please clear the following detail.....
stock Value RATE physical
44 1872 43 44
if physical and stock is equecl print the value 0 and physical value 45 into the rate value and physical value 43 into the rate, so clear the urgent basis.
regard
Hi Svetlana,
I have two problems I can't solve. I hope you can help me!
I have a value in Column D. I want to subtract that value from 20. If the result is 0 or less, I want Column J to show 0. If the result of 20D is greater than 0, I want to display that value.
For example:
The value in D4 is 22, 2022 = 2, therefore J4 shows "0".
The value in D4 is 14, 2014 = 6, therefore J4 shows "6".
Here is the formula I have in Column J: IF(SUM(20D4)=0,),"0",(SUM(20D4)
It doesn't work.
Also, I have a value in Column F. If the value in Column F4 is equal to 100, then Column L4 should show 5. If the value in Column F4 is between 90 and 99, Column L4 should show 4. If Column F4 is between 80 and 89, Column L4 should show 3. If the value in Column F4 is between 70 and 79, Column L4 should show 2. If Column F4 is between 60 and 69, Column L4 should show 1. If the value in Column F4 is equal to or less than 59, Column L4 should show 0.
For example: The value in Column F4 is 87, therefore Column L4 shows "3".
I don't understand how to write this formula either. I am new to formulas in Excel and I don't understand the syntax.
Hi Kathleen,
Here is the first formula:
=IF(20D4<=0, 0, 20D4)
As for the second task, you need nested IF's here:
=IF(F4=100, 5, IF(F4>=90, 4, IF(F4>=80, 3, IF(F4>=70, 2, IF(F4>=60, 1, 0)))))
I hope these are the formulas you are after :)
Oh, dear! Can anyone please help?!?
I have been working on finding a formula to work in a spreadsheet and can't find the correct one to produce the results I need.
I want to say: IF C6=X,or C7=X, or C8=X, or C9=X, or C10=X, or C11=X, or C12=X, or C13=X, THEN C4=X.
Every time I try using an IF statement or an OR statement, it doesn't work properly and it says my formula is broken.
Does anyone know what the correct formula would be for this? I have spent hours using tutorials and working out formulas but still can't get this to work. ANY help would be greatly appreciated!!
Hi Julie,
Try entering one of the following formulas in cell C4.
If X is a text value:
=IF(OR(C6="X", C7="X", C8="X", C9="X", C10="X", C11="X", C12="X", C13="X"), "X", "")
If X is a number, say 1:
=IF(OR(C6=1, C7=1, C8=1, C9=1, C10=1, C11=1, C12=1, C13=1), 1, "")
Oh, Svetlana! I am forever indebted to you!!! THANK YOU so very much for the formula! It worked perfectly!! I cannot tell you how grateful I am for your help!
THANK YOU Svetlana, this was exactly the help I needed!
I am trying to design a function to produce a "grade" based on a number of columns.
There are three possible grades given: UNSAT, SAT, SUP
There are eight graded sections(columns), each with one of the given grades above.
I want the ninth section to produce an OVERALL grade based on the previous grades.
SUP on 7 or 8 of the previous eight grades (columns)
UNSAT on 2 or more of the previous eight grades (columns)
SAT in all other cases.
Thanks for any help you can provide!
Never mind! I found the answer by using a mixture of IF and COUNTIFS Functions:
=IF(COUNTIF(A24:G24,"SUP")<7,"SAT","SUP")
This answer actually nullifies the requirement to have an UNSAT answer in the field, only because it was easier for me to count an UNSAT manually vice work the multitude of function. If i do find a better answer (to my own question haha) I will update it!
Hi, looking to do the following:
Cell C1 has mobile #
Cell D1 has mobile # or is blank
 we insert rows manually below each data entry which has values in C1 and D1 (throughout the sheet)
 if there is an easier way, please advise
I now want to have the value of the newly created row reflect the value of D1 if there is data in D1; but
I want to be in a position to drag the formula through the whole sheet and there won't be rows inserted below data entries that doesn't have values in D1; thus
I want those data entries to remain the same.
My problem is that it creates a circular reference.
Virgin sheet ex.
A B C D
1 Dave Scott 0845685841 0824865892
2 Jill May 0725984287
3 Jhon Snmit 0605493216 0915876431
Manually insert row below person with 2 numbers
A B C D
1 Dave Scott 0845685841 0824865892
2 xxx xxxxx xxxxxxxxxx xxxxxxxxxx
3 Jill May 0725984287
4 Jhon Snmit 0605493216 0915876431
4 xxx xxxxx xxxxxxxxxx xxxxxxxxxx
Thus I want to be able to do the following:
Input a formula in column C to display the value of D in the above row or remain the same if D is blank
What I tried, but gave me circular reference:
In C2 =IF(D1"",D1,C2)
The same will apply to the names, will also want to be able to populate the persons name in the newly created row for their second number:
In A2 =IF(D1"",A1,A2)
The result that we are looking for is:
A B C D
1 Dave Scott 0845685841 0824865892
2 Dave Scott 0824865892 xxxxxxxxxx
3 Jill May 0725984287
4 Jhon Snmit 0605493216 0915876431
4 Jhon Snmit 0915876431 xxxxxxxxxx
Therein lies my problem, kindly advise
I have a column of repeated names and a column of values against the names say C2:C101 (names) and I2:I101 (values). I have tried to work out a formula that looks for one of the names in column C and gives a total of all corresponding values in column I.
I have tried variations of =IF(C2C101="Walker, David","SUMI2I101","0") but I just cannot get it to work.
Can you help please.
Thank You
Microsoft Excel has a special SUMIF function for this purpose, which makes the formula as simple as:
=SUMIF(C2:C101, "Walker, David", I2:I101)
All is Ok, I have managed to solve the problem by using SUMIF.
Thanks Svetlana
I didn't see your reply but thank you for answering
Hi I would like to ask how do I type in the formula box if for example I want it the multiplier to be like this:
If the value ranges from:
1 ~ 10 = 150
10.1 ~ 50 = 120
50.1 ~ 250 = 110
251.0 ~ 500 = 100
501.0 ~ 750 = 95
751 ~ 1000 = 90
If I have typed a value of 57, it should be 57 * 110 (since 57 is at range of 20.1 ~ 250 = 150 (150 as the multiplier))
Please help.
Thanks
Hii...
I need a help, question is
PF is 20% of Basic salary or Rs.1000 whichever is less
Please give me answer as soon as possible.
I have an if function question I can't seem to find a similar question in your article. For example Tenant lease start D12 and lease end dates E14; has to give me the date in G14. The same date that is in D12. This tell me when I can start doing improvements. How is the if function written out?
hi I want to find a value if a1=less then 20 "80" more then 20 bt less then 40 then calculate(a120)*7)+80) bt a1 is greater then 40 then calculate (a140)*10)+230 please help me ma
PER GRADE GRADE points
66.5 C
80 B
90 A
65 D
if A=4 ,B=3.5 ,C=3 ,D=2 HOW CALCUATE (FORMULA)
Friends I want to a formula who auto calculate with given material with its value.
Ex.
If Material is Sand and its value 41....its divided by its net weight(12000)
I want to just type different Materials Name like sand and its auto divided with weight.
=IF(CV2<=28,"Poor", IF(28<CV2<=42,"Borderline","Acceptable"))
I wrote the formula above but it's only selecting "Poor" and "Acceptable", leaving out "Borderline" Any help?
Hi Phil,
Regrettably IF cannot understand expressions like 28<CV2<=42. You have to use an AND statement is this case:
=IF(CV2<=28,"Poor", IF(AND(CV2>28, CV2<=42),"Borderline","Acceptable"))
But in fact, checking for CV2>28 is superfluous and you can put it simply as:
=IF(CV2<=28,"Poor", IF(CV2<=42,"Borderline","Acceptable"))
=IF(B2="delivered", TODAY(), "")
I use this formula to generate delivery date, but i found that the date will updated to the date of next day... So how to maintain the date there for me to check the delivery date few days later? thanks.
hi..
i have just got answer from your previous post that
"How to convert date to text using Excel TEXT function and noformula ways"
...ha, it`s helpful, thank you very much, but i am not sure whether it will be updated tomorrow
Hi, working on an excel spreadsheet for invoices.
Column A is quantity, b & c are description, D is unit price and E total
I need a formula that calculates A*D in column E but remains blank when column A is blank
Thank you
Hi Fitz,
Try the following formula:
=IF(A2<>"", A2*D2, "")
awesome. perfect. thank you Svetlana
Hi, I am trying to write a formula that pulls in a number of 1, 5 or 10. The numbers in cell A1 range anywhere from 1  25. I presently have it set up as follows:
=IF(AND(A1>=1,A1=5,A1<10),5,10))
The formula takes, but it always returns the number 10 in every instance. Can't figure this out. Hoping you can help.
Hi, I am trying to write a formula that pulls in a number of 1, 5 or 10. The numbers in cell A1 range anywhere from 1  25. I presently have it set up as follows:
=IF(AND(A1>=1,A1=5,A1<10),5,10))
The formula takes, but it only returns the number 10 in all instances. Help!!
For some reason, every time I submit my question, it cuts down on how I originally wrote my formula.
=IF(AND(A1>=1,A1=5,A1<10),5,10))
This will only return the number 10.
Hi Eric,
Our blog engine often mangles formulas in comments, sorry for this. Could you please describe your conditions in words, so that I can understand the logic? So, the formula should return:
5 if ?
10 if?
1 if?
Dear Svetlana,
I tried =TEXT(O2,"ddmmmmyy") and which (O2)=IF(B2="delivered", TODAY(), "")
it workeed yesterday and (cell P2) shown 12May15 but it updated to 13May15 today morning.
So how to lock the date(cell P2) at 12May15?
thank you.
Hi Zhao,
Regrettably, it's not possible to lock the date using formulas. You either have to replace the formula with its value each time manually (Copy > Paste Special > Value) or write a VBA script that automates this.
How to set if condition for following:
=ROUND(VLOOKUP(G6,'2012 IAM Table raw'!A$2:D$122,4,FALSE)*((1VLOOKUP(G6,'2012 IAM Table raw'!A$2:E$122,5,FALSE))^F6),3)/1000
this round value will return some value, if values is not return need to set the field value to 0 (zero)
Hi Harish,
You can try to wrap your VLOOKUP function with IFERROR like this:
=ROUND(iferror(VLOOKUP(G6,'2012 IAM Table raw'!A$2:D$122,4,FALSE) * ((1VLOOKUP(G6,'2012 IAM Table raw'!A$2:E$122,5,FALSE))^F6), 0), 3)/1000
Hi Svetlana,
What is the formula to highlight nondate cell on particular future dates?
e.g.
A1 cell contains Name and I want to highlight this name between 20 May 2015 to 22 May 2015.
Can you please, help me with the formula for this?
Regards,
Sid.
Hi
Need to insert if formula where there are four dates (one in each column) and need a yes answer if any of the dates are greater than one date but less than another date. eg. if any of the four dates were greater than 01/06/15 but less than 30/06/15 I need the answer to be yes
Thanks. LP
Lindsay,
Please use this formula:DATEVALUE("01/06/15")), AND(A2 DATEVALUE("01/06/15")), AND(A3 DATEVALUE("01/06/15")), AND(A4 DATEVALUE("01/06/15"))), "Yes","")
=IF(OR(AND(A1
Where A1, A2, A3, A4 are cells with the dates.
Hi Guys, how are you?
I imported data from TXT file when the field BDATE came like below:
09/09/2013
09/09/2013
09/09/2013
09/09/2013
30/12/2011
14/03/2013
24/09/2013
24/09/2013
30/12/2011
This format is British style (DD/MM/YYYY), when I convert to American style (MMDDYYYY) the date that begin > 12 were not converted.
Somebody knows what to do?
Thanks!
Hi,
I have two membership type groups; member and nonmember (column A). I have registration start dates (column B) and registration end dates (column C). I need to confirm that the start and end dates for registration fall within the acceptable date range for both members and nonmembers. Members range (6/1/15 – 8/31/15); NonMembers (6/8/15 – 8/31/15).
Thanks!
What if I want to say if cells F1, G1, and H1 are blank, I1 should be blank. If F1, G1 and H1 have numbers, then I1 should be F1+G1+H1.
Hi James,
Should I1 be blank if any of cells F1, G1, and H1 is blank or if all 3 cells are blank?
And what if any of those cells contains a text value?
I NEED YOUR HELP IN THIS FORMULA:
=IF(K26="CHEMICAL TANKER",IF(AND(K26>=0,K26=5000,K26=10000,K2620000,550))))),IF(K26="GENERAL CARGO",IF(AND(K26>=0,K26=5000,K2610000,490))))
THE ANSWER IS= #VALUE
IS THERE ANY POSSIBILITY TO COMBINE MORE THAN ONE IF STATEMENT IN ONE CELL?
I WANT TO ADD OTHER IF CONDITIONS IE,"BULK","CARGO" AND SO ON.
IS THERE ANY LIMITATION FOR THIS CONDITION? TQ
I have a tricky question...I want sheet 2 to return an if statement that calculates on sheet 1.: =if(MaterialBreakdown[SARA Title 313}="*YES", MaterialBreakdown[Material Name], NA. That is the formula I tried to use, but it gave me an error. I want all materials in column A to list in another sheet if they have yes in column C. I am really bad at explaining this!
all help appreciated!
i cannot figure the formula out help!!!
In cell F20, enter an IF function that tests whether the order quantity in cell E20 is greater than zero. If it is, return the the charge for this item, which is the value of cell E20 mulitplied by cell D20. Otherwise, return a space by entering " " (that is, double quote, space, double quote). Autofill this formula into the range F21:F25.
Hi Ana,
Here's the formula for F20:
=IF($E20>0, $E20*$D20, " ")
Simply copy it down to other cells in column F:
 select cell F20;
 move the mouse cursor at the lower righthand corner of the cell and you will see it changing to a plus sign (fill handle);
 click the plus and drag it down to fill other cells with your formula.
i need a formula to display a number (2) if the cell says "yes" and display a (4) if the cell says "no"....
can anyone help?
Here you go:
=IF(A1="yes", 2, IF(A1="no", 4, ""))
Can you help me with this formula:
=IF(AND(AE303="",AK303=""),Incident),IF(AND(AE303""),AK303="",(NOW()(AE303))),(DATEDIF(AE303,AK303,"D")))
I am trying to have the formula:
(1) if cell AE303 and AK303 are both blank put in the word "Incident", but
(2) if cell AE303 has a date in the cell and AK303 is blank insert number of days between AE303 and Today, but
(3)if both cells have dates, subtract them and tell me the # of days between
Hi Lana,
Try this formula:
=IF(AND(AE303="",AK303=""), "Incident", IF(AND(AE303<>"",AK303=""), TODAY()AE303, IF(AND(AE303<>"", AK303<>""), DATEDIF(AE303,AK303,"D"))))
Just keep in mind please that DATEDIF requires the start date (AE303) to be always less than the end date (AK303), otherwise a formula will return the NUM! error. An easy workaround is simply subtracting one date from the other in the last value_if_false argument:
AK303AE303
I need help with a formula using dates:
What I am trying to accomplish is if cell G2< 7/1/14 then insert 7/1/14 but if not insert the date that is in G2.
=IF(G2<DATEVALUE("7/1/2014"),"7/1/2014",G2)
Thanks for any help,
Hi Monica,
Your formula is correct. If it returns a serial number rather than a date, you should simply apply the Date format to the cell (press Ctrl+1 to open the Format Cell dialog and select the Date format you want).
Please advise how to use below formula in excel
=if(L2>60,"Active"),if(L2<60,"Critical"),if(L2<0,"Overdue")
Hi Vinath,
You'd rather put it like this:
=IF(L2>60,"Active", IF(L2<0,"Overdue", IF(L2<60,"Critical", "")))
Just pay attention, please, that the formula returns an empty string if none of the conditions is met, e.g. if L2=60.
I am trying to change a formula from =IF(H2="2bdrm/2bth",$K$3,IF(H2="2bdrm/1bth",$K$4,IF(H2="1bdrm/1bth",$K$5,))) which determined the average rental price to a formula that determines the what the highest rental price is for each size apartment. Anyone that can help I would greatly appreciate
I have my fantasy baseball spreadsheets. There are in two different files. File 1 has all of my palyers noted in column A and their positions in column B.
I am looking to transfer the Info from Lets say "biils Row C" column A and B in my master file to "bills Row C" A and B in the updated spread sheet. I have the following formula that works on one line. but how do I get it to search the entire master sheet and pull that data over. The first C3 file is my updated sheet
=IF(C3='[REENTRY AS OF 5515.xlsx]Hitting'!C3:C500,'[REENTRY AS OF 5515.xlsx]Hitting'!B3)
i have to do this in excel ,guyz help
if b=250 value is 15
if b=260 value is 12
Hi Ajay,
Here you go:
=IF(B1=250, 15, IF(B1=260, 12, ""))
Hi,
I made a table for a high, medium,low with corresponding digits.
Like for example,if the probability of failure is 1(rare) and the impact rating is low, then the result should be low.
Now, my problem is how do i connect the table to my another sheet.
please help
try vlookup
Please help me!
For my job, I work a lot in excel. I have a spreadsheet to calculate per diem for drivers to receive on their paychecks. I have seven blank "date" cells. I'm wanting an if/then function so that if I write a date in one of the blank date boxes, it will automatically put $20.00 in the corresponding "per diem" box. Likewise, if the date box remains blank, the per diem box remains blank.
Please help.
Hi Tiffani,
Supposing your dates are in column A, you can put the following formula in the "per diem" box:
=IF(A1<>"", 20, "")
The formula will return 20 if cell A1 in not empty. To format the returned number as currency, select the cell with the formula, press Ctrl+1 to open the Format Cell dialog, select "Currency" on the Number tab and choose the currency format you want.
If Column A contains Column B then Yes if not then No
Can the formula take into account the case sensitivity as well?
Column A Column B
\\CCBF0FP21\DCLabels Doejj
\\CCBF0FP21\DCLabels$ Doejj
\\CCBF0FP21\DCReports$ Doejj
\\CCBF0FP21\Direct$ Doejj
\\CCBF0FP21\Review$ Doejj
\\CCBF0FP21\DEV$ Doejj
\\CCBF0FP21\Shipto Doejj
\\CCBF0FP21\shipto$ Doejj
\\CCBF0UD2A\UD1$\DOEJJ Doejj
Please Help :)
Sorry  the sample data on here is not displaying as 2 columns. To clarify:
Column A
\\CCBF0FP21\DCLabels
\\CCBF0FP21\DCLabels$
\\CCBF0FP21\DCReports$
\\CCBF0FP21\Direct$
\\CCBF0FP21\Review$
\\CCBF0FP21\DEV$
\\CCBF0FP21\Shipto
\\CCBF0FP21\shipto$
\\CCBF0UD2A\UD1$\DOEJJ
Column B
Doejj
Doejj
Doejj
Doejj
Doejj
Doejj
Doejj
Doejj
Doejj
There are also hundreds of different names in my data so Column B will not always be the same length. :)
I figured it out in case anyone ever has the same question :)
=If(ISERROR(MATCH(B1,A1:A1,0)),"False","True")
Thanks!
hi, i am currently trying to generate a dashboard for my company and am having trouble inserting data into particular categories. there is a general data input sheet and then the dashboard. on the dashboard there are 3 separate categories with about a dozen free rows beneath for data to be inserted. i wish to insert data from the general data input section based on value of dates given etc. eg if the date is now then insert under category 1 if more than 2 years away, category 2 etc. could you please suggest any ways that i can do this the easy way etc, I'm trying to avoid inserting the data manually under each separate category as there will be a lot to enter. thanks in advance (:
assit me with nested if statement of a traffic robot set up with out come or result of GO,Stop and prepare to stop.
Hi Ronnie,
You may find the following example of the nested IF function helpful:
https://www.ablebits.com/officeaddinsblog/2016/12/07/nestedifexcelmultipleconditions/
If you need assistance with a particular formula, please provide more information about your data structure and conditions.
Hi I am new to Excel and i am trying to format a mailing list that has been sent to me. basically the query is
if f2 is blank then copy the contents from E2 into F2. blanking out E2 when done. i hope that is clear any help would be appreciated.
Thanks
Hello
I am trying to work out a formula for a table of rental charges for rooms in a house.
Column E is 'date from' and Column F is 'date to' and column H is number of days. Rent runs from the 19th of each month.
I used the following formula to work out rent cost for the number of days they were there which is basically saying
'if the 'to date' is 19/05/15 then return the monthly rental value as shown in cell C14, but if it is not 19/05/15 then use the number of days multiplied by the daily rental value to give me an amount'
=+IF(F4=DATEVALUE("19/05/2015"),C$14,0)+IF(F4DATEVALUE("19/05/2015"),(H4*E$14),0)
I realised though that this only works if the month starts on 19/04/15. I have one tenant that moved in on 08/05/15 and i therefore need to adjust the formula to say that if column F= 19/05/15 and column E=19/04/15 then return the monthly rent but if F doesnt equal 19/05/15 and e doesnt equal 19/04/15 then use the number of days multiplied by the daily rent.
Is this possible as when i adjust the formula with an AND it comes back as invalid.
Any help would be very much appreciated!
Thanks in advance
Hi Amanda,
Just add the following AND statement in the logical test of both IF functions:
=IF(AND(F4=DATEVALUE("19/05/2015"), E4=DATEVALUE("19/04/2015")), C$14, 0) +
IF(AND(F4=DATEVALUE("19/05/2015"), E4=DATEVALUE("19/04/2015")), H4*E$14, 0)
Many thanks Svetlana!
Hello, Excel will not accept the following formula:
=IF(F3= "yes", [G3*1.1], [G3])
All I want it to do is return Cell G3 x 1.1 if cell F3 = Yes, else just cell G3. What is wrong with my format?
Hi Paul,
Just remove square brackets:
=IF(F3= "yes", G3*1.1, G3)
hi, how to do this, ex:
22.7 auto increment to 23 or 22.3 to 22 ?
if 22.5 till 22.9 to be 23 or 22.1 to 22.4 to 22 ?
Hi,
Use this formula, your data in a column
=ROUND(A1,0)
HI!
I'm trying to use a certain cell name from a list in my IF formula, but it will not allow me to use the character within the name without trying to use the characters function. is there a way to override this? Thank you!
ex. =IF(D5=3Y) it's wanting to use that Y as a YEAR function and not allowing me to use it simply as a Y.
Hi Stephen,
If 3Y is a usual text string, then enclose it in double quotes like this:
=IF(D5="3Y", value_if_true, value_if_false)
I am having a problem with the if function with this question9. cell C10 is equal to 0.
a. If this condition is true, the current cell should be made empty (that is, equal to "").
b. If this condition is false, the current cell should display the result of multiplying cell C10 (hours) by cell D7 (hourly wages). Use a relative reference to cell C10 and an absolute references to cell D7 so that the formula can be copied to other cells
Hi Janay,
Here you go:
=IF(C10=0, "", C10*$D$7)
Hello,
i want to make one condition like
party payment days are going between 70 days so that's mention in excle
inv. date amount 3060 days ,6090 days, above 90 days
1 1/4/15 50000 0 50000 0
pls give me that type of formula ,
i use one formula =if(now()datevalue(b1)<30,+c1,0) but it's now working
pls give me other formula
sorry it's not working
Hi NIRAV,
I am not quite sure what +c1 means. Anyway, if B1 is a date rather than a text value, the DATEVALUE function is not needed:
=IF(NOW()B1<30,C1,0)
If B1 is a text value, then the following formula works fine:
=IF(NOW()DATEVALUE(B1)<30,C1,0)
I am trying to change a formula from =IF(H2="2bdrm/2bth",$K$3,IF(H2="2bdrm/1bth",$K$4,IF(H2="1bdrm/1bth",$K$5,))) which determined the average rental price to a formula that determines the what the highest rental price is for each size apartment. Anyone that can help I would greatly appreciate?
Hi,
I need a solution for below,
Given data
Name Subject marks
Raj FFV01 70
Vino FSO03 80
In a desired cells, I need a formula that allows me to pick the Raj's "Subject" only and mark in the next cell. whereas if it's vino, Cell should be blank.
Please revert if quest not clear.
Thanks for your help in advance.
Hi Raj,
You can use a VLOOKUP formula similar to the below one:
Subject: =VLOOKUP("raj", A1:C100, 2, FALSE)
Mark: =VLOOKUP("raj", A1:C100, 3, FALSE)
Where Name is column A, Subject is column B and marks are in column C.
Hi,
I am trying to create an IF formula on a date cell that states if the date is before or equal to 01/02/2015 then give me 'A' otherwise give my 'B' (ie. if the date is passed).
I have used =IF(D7<=01/02/2015,"A","B") but it does not work and gives me 'A' for dates before and after 01/02/2015.
Hope you can help.
Many Thanks
Hi Ross,
Just use the DATEVALUE function in the logical test:
=IF(D7<=DATEVALUE("1/2/2015"),"A","B")
Hi,
I need to compare the data between two xls sheets(cell to cell validation).
Hi Svetlana,
I'm having difficulty with making a formula.
What I want, is that if A1 is not Blank, that C1 Prints "N/A", but if C1 is not Blank, then B1 and A1 Print "N/A".
I currently have: =IF(NOT(C1=""),AND(A1="N/A",B1="N/A"),IF(NOT(A1=""),C1="N/A","Error"))
(If not possible please let me know as I've been having trouble with these recently, thanks)
How to put a single formula for the following;
If F31 is '0' then the value should be of H12...
and if F31 is more than '0' then the value should be the value of F32.
Hi Anwaar,
You can use a nested IF formula like this:
=IF(F31=0, H12, IF(F31>0, F32, ""))
Thanks a lot!☺
Good morning!
Now could u also help me out for the following:
I have different ranges of a quantity and one single cost for that particular range.
For Ex. 5000 to 10000 = Rs. 120000
11000 to 20000 = Rs. 115000
21000 to 30000 = Rs. 100000
31000 to 40000 = Rs. 98000
and same pattern continues for further more values...
request for early reply...
thanks in advance
Anwaar
Hi,
i want a formula that will not show data on another workbook if the column is blank eg
yes no
\
\
\
\
\
\
i only want it to show the yes column on another tab but dont want any space between the date. Is this even possible using this function? I know its possible from using filter but dont want to do this
Good afternoon, I am trying to create a simple spreadsheet calculating a price per square foot value based on an optional selection of option A (wood material)or option B (carpet, which would be translated to square yards). both options A & B will also need to be increased by 20%. If I have all the correct numbers in the spread sheet, why cant I apply an if then statement to the column / cell I want to create the equation for? I am trying the following.
=IF(H7=Option A,"((F7*D27)*1.20))",(H7=Option B,"(((F7*.111111)*D28)*1.20))"
F7 represents the square feet
D27 represents the Price
H7 would be the field I would like to type option A or option B into
thank you for your help.
Hi Doug,
I believe the correct syntax is as follows:
=IF(H7="Option A", F7*D27*1.2, IF(H7="Option B", F7*0.111111*D28*1.2, ""))
I WANT CALCULATE LIKE THIS:
IF A1 Grater than date 31052015
than A2*12.36% otherwise A2*14%
thats it but formula not giving me correct value
so Plz help me on this
IN ONE CELL
=ROUND(IF(A1>"31052015",A2*14%,A2*12.36%),0)
AND I ALSO TRIED IN THE PLACE "31052015" TO DATEVALUE("31052015")
BUT NOT WORKING.
IT SHOWS #VALUE
YES MY FRIEND I FOUND MY SOLUTION AND IT WORKS
LIKE THIS
=ROUND(IF(T8>=DATEVALUE("01062015"),E8*14%,E8*12.36%),0)
THANKS
AND DOING GOOD JOB (GENIUS WORK)
HI! I HAVE TEXT INPUT FOR A2 TILL A100, EACH TEXT INPUT IS EQUIVALENT TO A NUMBER, EX, IN A3 (LC) AND IN B3 I PUT THE EQUIVENT IS 0.75 , IF I PUT A FREE TEXT IN C1 (LC) I NEED TO DISPLAY THE EQUIVALENT IN D1.
A B C D
1/ BS 1 LC 0.75
2/ NC 1.75
3/ LC 0.75
4/ WL 0.50
EX ABOVE : I INPUT LC IN C1 , I NEED TO DISPLAY THE EQUIVALENT WHICH IS 0.75 IN D1 ( WHICH IS FROM B4) I WANT IT TO DISPLAY AUTOMATIC ONCE I PUT ANYTHING FROM COLUMN B IN C
NEED YOUR HELP THANK YOU
Hi MARRIAM,
Try using the following VLOOKUP formula, where $A$1:$B$100 is your range of data:
=IFERROR(VLOOKUP($C1, $A$1:$B$100, 2, FALSE), "")
Hello, I would like to increase each numerical value by $100 in every cell in a very large table that has a number, but if there is text in the cell, then I would like to simply copy the text from the call. Can this be done with the IF function?
Hi Marlene,
You can use a formula similar to this:
=IF(ISNUMBER(A1)=TRUE, A1+100, A1)
Hi Svetlana
=IF(A1="Active",AVERAGE(B1),0)
The above formula works for one cell, of course, but I need to construct it to work for a range of cells.
If "Active" appears in column A,(A1:A9), then I need to calculate the average of all numbers in corresponding cells in column B, (B1:B9). Do I need to nest a VLOOKUP into this IF statement? Either way, can you please help?
Many thanks :)
I want to use IF to discriminate between individual turtles! I have drop downmenus describing each shell segment, e.g. box A segment one has a mark/ is white, box B the mark is single/double, Box C it is shaped like a circle, square, line, cone, other..then this combination of shell segment descriptions means the turtle is number 32. (All turtles have individual markings)
How can I use if to return a specific individual identifier, rather than a yes/no, true/false answer?
thanks for your comments in advance
I found another way to find my average % completed:
=IF(O5>0,(SUMIF(F9:F15,"Active",I9:I15)/O5),"100%") where O5 equals total active students. This one stumped me when there were no active students. That is O5 is zero. Anyway, it works!
Hello Svetlana,
I am not sure if I am doing this correctly. I would like to check in a row in the columns A through D for the keyword "PLAN". If it is present the result would be "Yes" and if not present for the cell to remain blank. I tried this function but it does not work: =IF(ISNUMBER(SEARCH("PLAN",A2:d2)),"Yes","")
Thank you,
Matt
Hi Matt,
Try this one:
=IF(COUNTIF(A2:D2, "plant")>0, "yes", "")
Hi Svetlana,
Worked perfectly.
Thank you,
Matt
Hi there.
I would like to highlight a row based on a certain cell being a value greater than 0. ie. if cell H8>0 "Highlight row 8 (or a range of cells)
Hi Garth,
Simply select the rows you want to highlight and create a conditional formatting rule with the formula for your topmost row, e.g. =$H8>0
Hi,
I wanted to know how to do the below scenario in Excel using if function eg
I have mentioned no. of days worked in a month cell(K1) and no. of working days in month another cell (J1),if K1<j1 then there is value mentioned in I1 which is got using this formula =ROUND(L3*12/100,0) or else 1560
Hi,
Cani use If function for different criteria,actually i want to do ageing of invoices i want to know since how many days the invoice is pending outstanding.There are different no of days for each invoice i want to know the days bracket. i want the formulae as per below for eg.If cell A1306090,"90days"
hi Svetlana Cheusheva,
can you please guide me to make a farmula for this calculation
IF A16 and 9 and <12 than * 450
hope you understand what i want in result like i need to multipuly the B1 with 150 if A1 is less than 6 and if it is more than 6 and less than 9 than with 300 and if A1 is more than 9 and less than 12 than multiply B1 with 450 and if A1 is more than 12 than multiply B1 with 600
please rply ASAP
Hi Sharfi,
Here you go:
=IF(A1<6,B1*150,IF(A1<9,B1*300, IF(A1<12, B1*450, B1*600)))
Hi Svetlana,
Could you please help me on below ?
I have 5 supplier, SupplierA have 10 purchasing order (eg. PO#0001  PO#0010), PO no. 0001 have split to 5 transactions with different amount.
How do i make a formula to find out total amount for each PO follow by each supplier ?
Thank you in advance for your kind advise.
Hi Josephine,
You can use a SUMIF formula similar to this:
=SUMIF(A2:A100, "SupplierA", B2:B100)
Where column A is the supplier name and column B is amounts to sum.
how about i need something less than 50 turn into yellow color word, less than 0 turn into red color word.
Is that possible?
Hi Richard,
You can do this by creating Excel conditional formatting rules with the following formulas:
Red: =$A2<0 (should be the 1st rule in the list)
Yellow: =$A2<50
Where A2 is the topmost cell with data.
Can u tell a formula for this, using if condition.. If grade is PP, credit point multiply to 1. If grade is CR, credit point multiply to 2. If grade is DN, credit point multiply to 3. If grade is HD, credit point multiply to 4.
grade credit quality points
PP 12 12*1
CR 12 12*2
DN 12 12*3
HD 12 12*4
Hi Ahmadh,
Supposing that your grades are in column A and credit points in column B, you can use the following nested IF functions:
=IF(A2="PP",B2*1,IF(A2="CR",B2*2,IF(A2="DN",B2*3,IF(A2="HD",B2*4,""))))
thank you so much!!
I would like to create an IF formula to calculate the following and can't seem to get it right. If Cell L71 is empty, leave it blank, if it is Y, put Can Be Deducted in Cell M71, if N, put Can Not Be Deducted in Cell M71.
I keep getting it to only say either Can Be Deducted, or Can Not Be Deducted, It wont do both for me. This is what I used =IF(ISBLANK(L71)=TRUE,"",IF(ISTEXT(L71="Y")=TRUE,"Can Be Deducted"=FALSE,"Can Not Be Deducted"))
Hi April,
I believe the formula for M71 could be a bit simpler:
=IF(ISBLANK(L71), "", IF(L71="y", "Can Be Deducted", IF(L71="n", "Can Not Be Deducted", "")))
Thank you so much!
Hello,
I am trying to see how many people made a gift last year that is less than this year, and the difference is greater than $500, then get the sum total of all those records. Any help is appreciated. Thank you!
=IF(O1=N1,"Yes","Difference noted of "&O1N1>500)
Hi Patcha,
To output "yes" and "Difference noted of O1N1>500" messages, you can use the following formula:
=IF(O1=N1,"Yes", IF(O1N1>500, "Difference noted of O1N1>500", ""))
To find the total of ">500" records, use the following array formula (remember to press Ctrl+Shift+Enter to complete it):
=SUM(((O1:O100)(N1:N100)>500)*1)
Currently I have a formula:
=If(k2"",k2,if(l2="",date92015,5,29)l2
Need to add one more field = I2, that contains these five values
Regional or
Panama or
Costa Rica or
Colombia or
Singapore
Please advise of how to handle these additions
Currently I have a formula:
=If(k2"",k2,if(l2="",(date92015,5,29)l2
Need to add one more field = I2, that contains these five values
Regional or
Panama or
Costa Rica or
Colombia or
Singapore
based on these 5 fields date will be dirrent
Please advise of how to handle these additions
I hv worked out some formulas but need to get it right. I have data on the above 5 cells (a5 to a9) and in textscompany names.
I have values at the bottom cells (b5 to b9). i have to get the lowest value in b5 to b9 and it is $1. it is found under company XXX iof cell a7 and is b7. Now i have to put the company name next to cell b11 because cell b10 is value of comp XXX in cell a7. for example, after i am thru entering values from b5 to b9 cell b10 will hav $1 while b11 should have company name XXX in it.
Pls help me on this project.
i need a column to change based on the choice chosen from a drop down menu in another column.
Hi!
I'm trying to find a formula that returns Yes or No if the cell format is a Date or text.Is there a formula that can identify if the cell is a date or text??
Example:
6/12/15 Yes
Complete No
Hi Dan,
Because Excel dates are numbers in their nature, you can use the ISNUMBER and ISTEXT functions to identify dates and text, respectively:
=IF(ISTEXT(A2), "No", IF(ISNUMBER(A2), "Yes"))
If there are numbers in your column as well and you want to skip them, the formula won't work properly. In this case, you may need a VBA function to identify valid dates:
http://www.mrexcel.com/forum/excelquestions/36619there%22isdate%22typefunctionexcel.html
Thank you!! this is exactly what I was looking for.
Why i can't write this?
=SUM(IF(AND(J1012),(M10K10+(L10J10)*6060),IF(AND(J1016),M10K10+(L10J10)*6015,M10K10+(L10J10)*60)),IF(AND(J1112),M11K11+(L11J11)*6060,IF(AND(J1116),M11K11+(L11J11*)6015,M11K11+(L11J11)*60)))
there have another two need to add up.
=SUM(IF(AND(J1012),(M10K10+(L10J10)*6060),IF(AND(J1016),M10K10+(L10J10)*6015,M10K10+(L10J10)*60)),IF(AND(J1112),M11K11+(L11J11)*6060,IF(AND(J1116),M11K11+(L11J11*)6015,M11K11+(L11J11)*60)))
=SUM(IF(AND(J1012),IF(AND(J1016)IF(AND(J1112)IF(AND(J1116),
IF(AND(J10 less than 13, L 10 more than 12), (J10 less than 17, L 10 more than 16)
J11, L11, same as above.
L10 12 , L1016
I have work with formulas previously but I need to get this formula correct:
=IF(K2"",K2,IF(AND(L2="",I2="Regional"),DATE(2015,5,29),""))
How to write a nested if from above when :
I2=Panama date(2015,7,29)
I2= Singapore date(2015,6,8)
I2= UK date(2015,10,8)
I2= Mexico date(2015,6,10)
I2=Italy date(2015,11,22)
Thank you for your help
A B C D E
1 Date 3X5.5 6X5.5
2 0323 6X5.5 345 345
3 0324 3X5.5 580 580
please help, thanks!
Good morning!
Now could u also help me out for the following:
I have different ranges of a quantity and one single cost for that particular range.
For Ex. 5000 to 10000 = Rs. 120000
11000 to 20000 = Rs. 115000
21000 to 30000 = Rs. 100000
31000 to 40000 = Rs. 98000
and same pattern continues for further more values...
request for early reply...
thanks in advance
Anwaar
How do I calculate days using info in 2 separate columns but tell the formula to only calculate it using the column that has a value in it?
Hi Julianna,
Please explain how exactly you want to calculate dates. Simply pull a date from a column that has a date in it? And what if both columns have dates?
Good Day! Please help.
I'm trying to get a total of the amount of days used in a month in a numerical form (to be used for a running total formula). The days are either full days and half days used, but the input is in mixed form.
This is what I tired but it didn't work: =IF((OR(B5:AC5=F,"1",B5:AC5=0.5,".5"),SUM(AE5:AE5))
Thank you so much for your help.
Alaine
Hi Alaine,
I do not exactly understand your criteria, but you definitely need the SUMIF function, or more likely SUMIF + SUMIF to add up values with the OR logic. You can find an example here:
https://www.ablebits.com/officeaddinsblog/2014/11/12/excelsumifssumifmultiplecriteria/#sumifmultiplecriteria
I AM TRYING TO GET A CATEGORY LIKE FOLLOWING.
123 42 HR
456 52 MR
258 50 HR
159 55 MR
756 70 LR
HELP TO SOLVE
All I want is D33 to return 100 if B33 is Yes and 0 if B33 is No. So I tried =IF(B33="Yes",100,0) but I keep getting a #NAME error. What am I doing wrong?
Hello Cindy,
The formula is correct, given that B33 is always either "yes" or "no".
In Microsoft Excel, the #NAME error occurs when Excel cannot not recognize the function's name. Hardly someone can misspell IF's name, but maybe you are using a nonEnglish localization of Office?
Hi,
I am trying to use IF function with multiple conditions but I am not getting the results I want to. Some times it works on one or two values but as I change the values to validate the formula it start making wrong outputs.
So far I made this formula;
=IF(C7>=70,"In Range",IF(C7<=100,"In Range",IF(C7<=69,"Low Alkalinity",IF(C7<=59," Too Low Alkalinity Can damage Plaster, Vinyle, Grouting etc",IF(C7<=49,"Danger Zone can't control pH levels",IF(C7=110,"Scaling can happen any time",IF(C7>=150,"HIGH Alkalinity",IF(C7>=200,"Pool equipment damaging",IF(C7>=250,"Stop Bathing in Pool HIGHLY DANGEROUS"))))))))))
safe range is between 70ppm to 100ppm and I am trying to get remarks according to findings if findings are getting as low as 70 it should show relative comment in remarks column and if finding are more than 100 it should show accordingly or else if its in range it should say "In Range" in Remarks... Please some one help I need this one. Thanks in advance .....
I have two worksheet, in the first one it comes only one column with electronic component designators(example R3resistor), in this column for each designator(resistor, capacitor or diode), corresponds a row. however in the other spreadsheet i have like more than one designator(the same designators as the first one) and it corresponded part number. what i want is a formula which compares designator and give me the part number in the first column. thank you
how can i right a formula if i want result of that cell is equal or less than zero to return zero value?
Thank you,
Tien
Hi Tien,
You can use a formula similar to this:
=IF(A1<=0, 0, "")
Hi Svetlana,
i have this issue with my formula:
D1 : due date
E1 (value) : =NOW()D1
F1 : open or closed
what i'm trying to do is, when i choose F1 as closed, i would like to have E1 value to be '0'. this is my formula which is still in error : =IF((NOW()D1),(F1=Closed)),'0'.
please help,thanks in advanced!
i managed to find the error and fix the formula. Here is the fixed formula =IF(F1="Closed","0",NOW()D1). =)
Respected Mam
Actually we have some data base sheet in excel and we want to get a particular data from the sheet as we have "Ch. No. 000122 issued to Mr. Dilip I317018893 khargone(Cancelled)" in row and we want to get only "I317018893" form this data so please guide me the proper formula of excel so that I can easily do it.
Thanks and regards
Satish
Hi Svetlana,
Can you please resolve the issue with my formula. I am trying to enter multiple logics with multiple values.
=IF(D1>22,"This is a VERY HIGH risk study"), IF(D1>17,"This is a HIGH risk study"), IF(D1>12," This is a MEDIUM risk study"), IF(D1<=12,"This is a LOW risk study")
Thanks
Hi Jamil,
When using nested IF's, all closing parentheses should be at the end of the formula, like this:
=IF(D1>22,"This is a VERY HIGH risk study", IF(D1>17,"This is a HIGH risk study", IF(D1>12," This is a MEDIUM risk study", IF(D1<=12,"This is a LOW risk study"))))
Thanks Svetlana, Works like a dream.
BW
J
Hey so I have a file with 5 Columns and 5000 Rows, there is data in each row, some rows are in Times New Roman, others are in Ahora.
I want to create a statement which basically says,
IF X is in Times New Roman then its a Yes, If not then its a No....PLEASE HELP ME!!!
how do you count if a column has two words like phone, mail
and if a filter is applied at another column , the formula should count as per the filter applied not all the cells of the texts of the first column
If i put red as value in one cell,what is the formula for appearing the same color in next cell
thank u vry mch
Hi,
This is what I am trying do
if value on A1 between
0154 A2 0
155462 A2 1
463770 A2 2
7711049 A2 3
Hi,
Thanks for above test eg.it lot me help to learn IF formula.
Thanks.
What if I want more than one logical test in my formula?
eg: If I want "IN TRANSIT" and "DISPATCHED" to be covered by one formula only.
Awaiting Response
Hi Aditya,
You can do this by using nested IF functions, for example:
=IF(A1="IN TRANSIT", value_if_true, IF(A1="DISPATCHED", value_if_true, [value_if_false]))
Thank you so much. you were a great help.
Hi
I am a total excel self taught newbie working on an Excel Workbook recording expenditure and wondered if there was an easy way to input the following in formula format:
If a number is 50 to 100 minus 5% if a number is greater than 100 minus 10% in excel
EG if the number in cell K20 is between 50.00 & 100.00 then 5% is deducted and if the number is 100.00 or more then 10% is deduced, I have managed to create a formula to deduct 5% if the number is 50.00 using the IF function =IF(K21>$M2,N2,)
K21 = total
M2 = 50.00
N2 = % sum
The above works fine however it is picking all amounts above 50.00 as I am unable to set the parameters so it only picks up amounts above 50.00 but less than 100.00
Any suggestions?
Many thanks
Hi Svetland,
Please help me write this IF formula correctly. i have a cell with different figures but some are missing data sets.
i want a command that says IF F3>1 then the answer should be 1, IF F3<1 then it should be 0 but if F3 is empty (meaning there is no number there) it should be empty. meaning if F3 is empty, leave blank.
Kindly assist.
thank you
Hi NASCO,
You can use a nested IF formula like this:
=IF(F3>1, 1, IF(F3<1, 0, ""))
Please pay attention that the formula will also return an empty string if F3=1. So, you may probably want to add one more condition to the formula or replace F3>1 with F3>=1.
Hi Svetland,
I am using this formula to find out whether 2 values exists in a column, if it doesn't, I want the result to indidate "NO", if it does, then "YES". However, it shows us "FALSE" for those that does exist. Can you check what I did wrong?
Thank you!
=IF(ISERROR(VLOOKUP("AP1902"&TRIM($J51)&TRIM($Q51),$C:$C,1,0)),IF(ISERROR(VLOOKUP("A80201"&TRIM($J51)&TRIM($Q51),C:C,1,0)),"NO","YES"))
Hi Svetlana Cheusheva,
am just looking to put one formuls to calculate the tax value using IF function.the things are like follows....
Suppose if there three columns f j and h
if i enter the value in f as 5.5% then it should give me the value of the f*h in the column j
ex: (in column j) =IFf=5.5% then it should calculate the value of f*h.
could you plese help me out in this...
Hi KIRAN,
If you need a formula exactly for 5.5%, then enter the following one in cell J2:
=IF(F2=5.5%, F2*H2, "")
The above formula multiplies F2 by H2 if F2=5.5%, and returns an empty string otherwise.
If you are looking for a more universal formula that multiplies a value in column H by whatever percentage you enter in column F in the same row, then use this one:
=IF(F2<>"", F2*H2, "")
I want to know the formula as per following data:
F G H
1 50 100 (I want if G1 value is less than 100 so F1 multiply with 10 but if G1 value is greater than 100 and less than 200 so F1 multiply with 20 and if G1 value is greater than 200 so F1 multiply with 30 and answer came in H1)
kindly provide assistance
Hi Adeel,
Here's the formula for H1:
=IF(G1<100, F1*10, IF(G1<200, F1*20, F1*30))
I want to link a document so that if it is greater than today it =A4 and if it is less than today it =A3. Say the document with A3 and A4 is called pizza. What would this formula look like?
hi Svetlana, thnx for your help but i need it again. please guide me to make this farmula.
A1="UA" i want cell A1 to be color in red
how it will work?
Hi Sharfi,
You can do this by creating a conditional formatting rule with the formula =$A1="UA"
@Adeel wait 2 min bro miss Svetlana already given me this formula.. let me share it with you
@Adeel
put this formula in H1
=IF(G1=100,G1=200,F1*30)))
Try this @Adeel
=if(G1=100,G1=200,F1*30)))
hi Svetlana, thnx for your help but i need it again. please guide me to make this farmula.
A1="UA" i want cell A1 to be color in red
how it will work?
like if i put the word UA in A1 i want A1 to be filled with Red color otherwise if i put anything else it stays normal
Hi Sharfi,
Simply create a conditional formatting rule with this formula:
=$A1="UA"
Hi Svetlana, I need help with a formula. I need to compare E1(total sale) to F1(goal)and display 10% of E1(total sale) if the store made or surpassed its F1(goal) and 0 if it did not make its goal.
Hi Sharee,
Here you go:
=IF(E1>=F1, E1*0.1, 0)
Can IF function for dates be used in a library system?
I want that the book I've borrowed is in its due date, the date would turn to red or there will be notification that it's already due.
Hi Ruben,
You can use an IF formula to display a notification, for example:
=IF(A1<=TODAY(),"Due", "")
It will display "Due" is the Due date in A1 is equal to to less than today's date.
To highlight dues dates in some color, please see How to conditionally format dates and time in Excel.
I would like to return a list of numbers separated by commas and & signs in an if statement, such as
=IF((B14<8.75), 5, IF((B14<11.25), 5 & 7.5, IF((B14<13.75), 5, 7.5, & 10, IF((B14<17.5), 5, 7.5, & 12.5, IF((B14<22.5), 7.5, 10, & 15, IF((B14<27.5), 10, 15, & 20, B30))))))
Hi there.
I'm trying to create an IF statement that will return nothing if there is nothing in the cell adjacent, but if there is anything text or number in that cell, then it will perform a formula.
Example
A1 Says, "Hello" then B1 will perform (B1*B6)
I can get an IF statement to result in nothing if A1 is blank, but I can't figure the right way to have it do a formula if there is something in A1.
Hi George,
Simply put the calculation in the value_if_false argument. For example,
=IF(A1="", "", B1*B6)
I have a name in column A. Depending on what it is, column B should have another name. For example. A says "Alaska" B would say "West" or if A said "New York" B would say "Eastern", etc. Can't make it work. Thanks for your help.
Hi Paula,
What you need is a nested IF formula like this:
=IF(A1="Alaska", "West", IF(A1="New York", "Eastern", ""))
Hi,
I want to make a cell turn blue if I enter a date or red if I enter text.
I cant figure this out....searched everywhere for info.
Thanks,
Jack
Hi JACK,
You can create the conditional formatting rules, with the following formulas:
Blue: =ISNUMBER($A2)
Red: =ISTEXT($A2)
Where A1 is the topmost cell with data in the column, not including the header.
I have a list of names in column F and numbers in columns K and P. In column R I have formula =if(p11k12,p12k12,""). Then in column Q I have, so far, =if(r11,f11,""). This shows the name in F11 in Q11 if there is a value in R11 and it works.
I need to extend this formula so that if there is a value in column R then show the name in column F but if there is a value in column T then show nothing (leave blank).
Help please
Kind regards Tony
the first formula should read =if(p11K10,P10K10,"")
I will say again that for some reason greater than and less than symbols won't show
Hi Hardwareman,
Our blog engine cuts off greater than and less than symbols quote often, sorry for that. I think you can use a nested IF formula like this:
=IF(T11<>"","", IF(R11<>"",F11,""))
Hi,
I'm using an IF formula which is designed to look at a data tab and find a match for a number in one column and return the corresponding values from another column. This is working well expect one of the numbers, 3370, is also returning the values attached to 333700 and 533700. How do I ensure that the values returned are just for an EXACT match and not numbers which include 3370??
Hope you can help!
Hi Tim,
It's hard to say anything with certainty without seeing your data. Probably you can use a VLOOKUP formula with exact match, as demonstrated in:
https://www.ablebits.com/officeaddinsblog/2014/07/17/excelvlookuptutorialbeginner/#exactapproximatevlookup
can u give me any formula for allouting different room to different age group
as example
age is 30,32,45,56,66,75,65,78
there are 5 room as j1 j2 j3 j4 j5
what should be the formula
please reply fast
Sir/Madam
what I want is, if, a1=>1 then b1 will be "sometext"(whatever iwant)
I used one formula =IF(A3>="","","rana").
but when i am typing in cell a1 is 090, b1 is not responding. Means, before 90 the 0 is not considering.
Pl. let me know the salvation.
Thanks
Svetlana
I have a cell with text such as TX,EP. If TX appears it should
be mutiplied by 5%. I tried the following but it does not seem to
work
=IF(AF2739= "TX", AF2739*0.06,)
Need your help to point out whether this is the correct manner
of doing it. Thanks
Sorry should be AG2739*.06
H Thong,
You probably meant "multiplied by 6%". If so, the formula =IF(AF2739="TX", AG2739*0.06,) is correct.
Svetlana
Thanks
Hi Svetlana,
I have a query regarding cell value restriction. I want to restrict the use of a certain letter in a cell if a corresponding cell value is greater than a certain amount i.e. if D35 has a value greater than 3, then D32 restricts me from entering the letter D. Is there a formula to do this?
Regards,
Paul
Hi Svetlana,
Can You help me to this formula.
I want to correct this beacuase it always appear ) or #value
=IF(F13>1,L13=F13),IF(F13<1,ROUND((B100.065*2)/F13+1),"")
Thanks,
ken
Hi,
can someone help me answer the below;
I would like to search, for example, "major" or "minor" in C1, if C1 contains either major or minor, i want to return "True".
table below;
C1= major minor young old , return "True"
C1= major young, return True
C1= minor old, return True
C1= young old, return False.
Much appreciate for your help.
Regards,
Hi Bravo,
Here you go:
=IF(OR(ISNUMBER(SEARCH("minor",C1)), ISNUMBER(SEARCH("major",C1))),TRUE, FALSE)
Could you tell me what is wrong with this formula
=IF(R3<=24,24Q3,R3=0)
The value_if_false argument. You cannot use R3=0 because the formula can return a value only in the cell in which it is entered.
Hi there,
Wondering if anyone could help me. I need a formula where it searches in a column for a particular word, then it shows a particular cell.
If(A:A,"WOR",C5)
Is this possible? Many many thanks,
Yuko
Hi Yuko,
You need to write a formula for one cell, and then you can copy it to as many cells as you want or across the entire column:
=If(A1="WOR",$C$5, "")
I have an excel that I am trying to get to display the number that is in a column (AB) in to column (AO)if it has specific words in it. This is the formula that I tried but I am getting a value of 0 which is not correct.
=IF(H15="2nd Level Support Indiv. Prod. Mahwah", SUM(AB15))
I am guessing the SUM function is wrong but couldnt think of anythign else to use. Any ideas?
The formula actually did work. The page was not refreshing. The only issue I am having now is that it is returning a "False" if it doesnt meet the criteria. How can I get it not to do that? I want to average my column and it doesnt work with the False.
I need a formula where it sum the amount of column B if the date in column A is between the dates in columns C and D.
The date in column A should be greater than the date in column C and less than the date in column D.
Hello Mili,
You can use an array formula similar to this:
=SUM(IF((B2:B9>C2:C9) * (B2:B9<D2:D9), A2:A9,""))
Remember to press Ctrl+Shift+Enter to enter it correctly.
I'm trying to do an 'IF' formula where =IF(P7>0,IF(ISBLANK(A256),Type B7 in A256, IF(ISBLANK(A257),Type B7 in A257,IF(ISBLANK(A258),Type B7 in A258, nothing))), nothing)
I can't seem to find the exact way to type it. If you could help, that would be great. Thanks.