IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading
Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks
by Svetlana Cheusheva, updated on
Comments page 27. Total comments: 4822
Column A = Date 30-June-2020, Column B = Date 30-Sep-2020 (*Overdue morethan 2 months)
How to condition formatting highlight the cell in Column B in red color
Hello!
If I understand your task correctly, use this formula in a conditional formatting rule:
=IF(EDATE(A1,2) < B1,TRUE,FALSE)
Hope this is what you need.
=if ((a1="g"),1,or(a1="mg"),1,0)
i want 1 if a1=g and a1=mg also if a1=kg then 0 and so on.
= IF(OR(D32="g",D32="mg"),1,"")
got it Thanks
Hi Alexander,
I have a column with some digital values. I am writing an IF statement on the neighbouring column to add paranthesis around the number in the previous column.
Ex. =IF(D10>0,[D10],0)
Hoping that if D10 ha a value 5, the IF statement returns [5] adding the square parenthesis. But it does not. How do I fix this? Thanks!
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(D10>0,"["&D10&"]",0)
But at the same time, the number will be converted to text.
You can also use a custom format without formulas.
Please go to Format Cells, choose Number -> Custom Format and set
\[#\];-#,##0;0
I hope this will help
I need help to excel sheet formats can works auto print with data find as per order...
Example :
first name ()
Second name (). Etc.
I am working same method in word the mailmerge using for auto data print.
Give me solution for excel. How can use to autoprint in excel.
Hi sir,
Please advice me the formula
I have three ccolumn in excel sheet - Today date ,City and Departure date.for today date i used the formula =today().but i need the formula in departure date i person is coming from same city then today date = departure date and if outside from my city then today date less one day i.e yesterday date
Hello!
Yesterday's date can be calculated using a simple formula
=TODAY()-1
But to give more detailed advice, I do not have the necessary information. Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
I have a Date in B6, I want it to show a date in M6 thats 90 days out and if there is words in B6 regarding status "In installation or In permitting" I want it to be blank or show 0. Is that possible?
Hello Charles!
Do you want the formula, text and date to be in B6 at the same time? It's impossible. If I misunderstood you, explain your problem in more detail.
Hi,
Please assist as my formula does not seem to be working. It gives wrong answers even for correct checks.
Formula currently in use: =IF(A2=B2, "Wrongly Charged", "OK")
The idea is to confirm if NUMBERS in A2 MATCH THOSE IN B2. IF SO THEN ANSWER SHOULD BE "OK"ELSE "WRONGLY CHARGED".
Formula returns "WRONGLY CHARGED" even for numbers that are the same/equal.
Hello!
I do not see your data, so I can only guess. Possible reasons why the equality condition for numbers in the IF formula does not work:
1. Some of the numbers are saved as text. This is indicated by a green triangle in the upper left corner of the cell and left alignment.
2. Numbers do not match in decimal places, which are hidden by number formatting.
i am calculating the rent payment alert, where in sheet where rent is 2000
month sr due date due amount paid balance
1 1-04-2020 2000 1000 1000
2 1-05-2020 2000 3000 0
so i need logic for the due amount once the date of due smaller than todays date
Hello!
I hope you have studied the recommendations in the above tutorial. Please specify what you were trying to find, what formula you used and what problem or error occurred. What do your numbers mean? It’ll help me understand the problem you faced better and help you.
I have 3 columns, one column showing actual costs, one row showing the estimated cost and 3rd column showing my forecasted costs. I want to write an if statement for the forecasted column that will default to the value in the estimated column if our actual costs are zero. However if we do have actual costs I want it to reflect those costs. For example
Actual Estimated Forecast
12 5 12
0 10 10
3 0 3
Hello Candyce!
If I understand your task correctly, the following formula should work for you:
=IF(A1=0,B1,A1)
I hope this will help
I am working on a spreadsheet relating to points (for employees). Each employees late and/or sick is recorded and they are given .5 or 1 point depending on late/sick call. After a year the employees point is removed. To keep the spreadsheet updated, there are formulas in place to change the cell to red once a year has passed. We recently made a change that the employees late will now be removed after 6 months. I need a formula that after 6 months late calls (column E), date is highlighted (column C). For the late point to be removed.
Columns are as stated below.
B - Employee ; C - Date ; D - Status ; E - Actual Leave (sick, late etc.) ; F - Points ; G - Total points
Thank you in advance for your help.
Hello!
To highlight a cell with a date that happened more than 6 months ago, use the conditional formatting formula
=EDATE(TODAY(),-6) > C1
I hope this will help
Thank you for the reply, Alex. I appreciate your guidance.
This is a running spreadsheet that is tracked daily, with different conditional formatting rules, making it easier for dates to be tracked and points to be removed/added.
I was hoping there was a formula to only track lates from column E, within a 6 month time frame for dates from column C. Would this need to be an IF formula inputted as a rule?
I am hoping this makes sense.
Thanks again for your help.
Hello!
Your problem is not entirely clear to me. If you want to compare dates with columns C and E, then you can use conditional formatting as a rule
=EDATE(C1,6) > E1
This condition is TRUE if between dates C1 and E1 is less than 6 months
Column C are specific dates the employee called in. Column E is what the employee called in (late, sick, FMLA etc.). I need only the lates (from column E), the dates (from column C) within 6 months to be highlighted in order to remove points the employee has accumulated.
Again, your help is very much appreciated. Thank you for your time.
Hello!
Each time you describe your problem in a different way.
What does "I need only the lates (from column E)" mean?
What do you mean by "lates"?
What is wrong in the formula
=EDATE(TODAY(),-6) > C1 ???
What result do you get? What result would you like to get?
Do you need dates for the last 6 months (you wrote "within 6 months") or older than 6 months (you wrote "after 6 months")?
I apologize for being so frustrating/confusing. Nothing is wrong with =EDATE(TODAY(),-6) > C1, except it is highlighting ALL dates from column C with 6 months or older. The formula works (and thank you for that), it's just missing one part I need.
I only need dates highlighted from column C that are labeled "LATE" in column E. Column E has no dates, only words with the type of leave the employee was marked that particular date, e.g LATE, Sick, FMLA(family and medical leave act), LOA(leave of absence), NCNS(no call no show).
Thank for for your continued help (because clearly I am no excel wizard).
Dear Alex Hi,
Small Query what to write today Date in cell:(A1) if i type "OK" in Cell:(B1).
please note Date Should not be got changed as the day change.
Thanks
KP
Hi,
I have a spreadsheet which has one column of company names, and one column of numbers. There is a table at the bottom, what I need is for one line in the table to add all of the number cells for each company name? They are in date order so the company names are not in alphabetical order.
How can I do this?
Thanks,
Hello Jack!
You can select values by condition using the VLOOKUP function or using the Excel filter
Hi,
If my company is giving me salary on every mid month and month end basis (Eg. 15 and 30 or 31 (depend on month end date) / for Feb month 28 or 29).every fortnight i received the 10000 dollar.But suppose i am leaving the company on 12-06-2020 then how much amount i will get?
I need advance formula where we can drag the formula and take it out the amount for other employee details also.
E.g.
Leaving date Amt
12-06-2020 10000
26-02-2020 10000
please let me know if additinal details required.
Hello Sera!
If I understand your task correctly, the following formula should work for you:
=IF(DAY(A11) < = 15,DAY(A11)/15,(DAY(A11)-(DAY(EOMONTH(A11,0))-15)) / (DAY(EOMONTH(A11,0))-15))*10000
I hope it’ll be helpful.
I hope you're still able to respond to this thread! I'm creating a bonus tracker and I'm wanting Excel to add $50 whenever someone achieves $500 over their goal. For example, the goal is $1,000 and someone achieves $1,500, then they would get an additional $50 payout. Another example would be the goal is $1,000 and someone achieves $2,000 then they would get an additional $100 in payout. Thank you!
Hello~
I have a birth date in one cell (example 7/22/15), and I need to another cell to auto populate the age in years based on the date 9/1/20. The value would be 4, but what would be a formula to populate that automatically?
Hello Kathy!
To calculate age, the IF function is not needed. I recommend to study these examples and guidelines.
In addition, I need the number to come out to a whole number that isn't rounded up.
Hi,
i need help with a formula
If column "M" is less than £3.50 then it needs to say £3.50 and if its higher than £3.50 it needs to return the value stated in column "M"
=IF(M5<3.49,"£3.50","M5")
My formula is this so far but the higher value is saying M5 instead of the values higher than £3.50
Hello Matt!
Please try the following formula:
=IF(M5 < 3.49,"£3.50",M5)
IF MY CELL CONTAINED #N/A FROM A VLOOKUP VALUE HOW TO PUT 0
Use the IFERROR function to handle errors. Read more here.
if have in one cell 196.513K, then i want another cell 196.513/1000. Can it possible?
Good day,
I am trying to create an 'If' formula for tasks that picks up a range from another sheet and returns the result on a summary tab.I am not sure if 'IF' is the correct formula as I can't get it to work.
If C38:C42 (on the detailed tab) status is completed then return the text 'Completed, if FALSE then return 'To Complete' on the Summary tab.
I have tried =IF(Revals!C38:C39="Complete","Completed","To Complete") but it didn't work.
Hello Nisha!
If I understand your task correctly, the following formula IF should work for you:
=IF(IFERROR(MATCH("Complete",C38:C39,0) > 0,0),"Completed","To Complete")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thanks Alexander. I tried it but when I change the status it still returns the same value as if completed. Meaning, if I then went to the detailed tab and changed the cells one to complete and the other to to be completed, it still gives me a result on the summary page that the tasks have been completed when it hasn't.
On the positive at least it is returning a value, i could not get it to even do that :-)
Thanks
Hello!
I expected that you pay attention to the fact that my formula does not have such cell references as yours. I do not have your workbook, so the links may not be the same. I think you just copied the formula and now want it to work? I have given you a sample. Use your cell references in it.
Hi Alexander, yes I changed to my cell references to pick up from the separate tab. However with me putting one as Complete and the rest as To complete, it still returned a value of Completed
Hello!
I wrote this formula based on the description you provided in your original comment. But now it’s clear that you didn’t say everything. How many cells in your range? 2 or more? There were 2 of them in your formula. But now, according to your words, I see that this is not so. Do you need at least 1 match or matches in all cells? The formula you wrote speaks of one coincidence. Now is this not so? Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.
Sure, thank you for you help.
It will be ranges of cells in one column. In this particular example, there are 5 rows in the column. The user is required to change the status to either Completed or Not completed. This is on the detailed tab. There are about 5 different headings with a range of tasks in each one. So I wanted to create a summary sheet that will then Look at each area and then return a result. If all tasks are not completed, even if only one tassks is the summary sheet should say not completed for that particular area. Eg below
Sales
Column B Column C
Row 23 Payroll Reval Not Completed
Row 24 Orders Reval Not Completed
Row 25 Bank Reval Completed
Row 26 Tax Reval Not Completed
Row 27 Interco Reval Completed
I hope this clarifies a bit
Could some one help, please? I am trying to analyse the sales data for a new housing development. I have set up a table with my raw data for the 126 properties, with the following columns:
A = Street number
B = Street address
C = developer’s plot number
D = developer’s house type name (in text)
E = developer’s house type code number (mix of numbers and letters)
F = number of bedrooms the house has
G = whether the house is a flat/terraced/semi-detached/detached
H = size of house (ft2)
I = sale price
J = date of sale
K = sale price/ft2
I am trying to consolidate the raw data into a second table in the same worksheet so that I can calculate the average selling price for the 22 different house types. I find that if I sort the raw data table on house type name to group each house type together and then use the AVERAGE function In the consolidated table, when the raw data is then resorted on a different parameter (say street name) the data pull through for the average is the new cell content rather than the original cell content (ie the cell reference remains the same rather than changing to follow the original data). I have tried to set up a logic test (=IF(D5:D130)=D135,AVERAGE(I5:I130), ) where the range D5:D130 is the range of house type names in the raw data table, D135 is the specific house type name in the consolidated data table, and I5:I130 is the selling price in the raw data table. All I get is an error, either #VALUE! or #NAME! if I try fiddling with the logic test set up. What am I doing wrong?! Can anyone help, please?
Hello Andrew!
Try using the AVERAGEIF function, which calculates the average value based on the condition. Read this guide.
Thank you. It worked! Much appreciated. Gold star!
Hi!
I've been using the IF function with this formula:
=IF(C26-5*$G$25<0,"N/A","YES")
Now that equation is putting "YES" in the correct cells where I want them to. Now I want to change "YES" with a new condition that actually follows a recursive formula; I want to copy the column E7:E18 to another table I'm working on, but only start copying if that cell no longer satisfy the first condition I set up. Is there a way to create the formula such that if the previous IF function is false, I will still start at E7? Is there another way to do this?
I look forward to your response :D
Hello Hannah!
I’m sorry but your task is not entirely clear to me. What does E7: E18 have to do with your formula? What does "start at E7" mean? What condition do you want to include in your formula instead of “Yes”? Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Hi,
How do I, if condition met, the cell equals to the value of another cell?
For example;
Cell C4 is empty. If the formula in cell U2 generate a '1', the value of C4 will automatically be equal to the value stated in cell R2.
By the way, the value in cell R2 is 'A,B,C,D'.
Please advise and thank you.
Hello Nicholas!
If I understand your task correctly, the following formula should work for you:
=IF(U2=1,R2,"")
I hope it’ll be helpful.
Hi,
I could use some help please.
I have 3 variables that depend on the last letter of the main word and I would like to separate them as follows:
XXXXXXA Yesterday
XXXXXXD, XXXXXXXE, XXXXXXF Today
XXXXXXK, XXXXXXL, XXXXXXM, XXXXXXN Tomorrow
If try IF, OR, ISNUMBER SEARCH AND FIND but I can’t figure it out.
So I just want it to write ‘Yesterday’ if last letter A, ‘Today’ if D,E or F and ‘Tomorrow’ if K, L, M or N as last letter.
Thanks
Hello Matea!
If I understand your task correctly, the following IF formula with nested IF conditions should work for you:
=IF(SUM(--(RIGHT(N1,1)={"K","L","M","N"}))>=1,"Tomorrow", IF(SUM(--(RIGHT(N1,1)={"D","E","F"}))>=1,"Today", IF(RIGHT(N1,1)="A","Yesterday","")))
I hope this will help
Dear Alexander,
You've been so kind to help me with the following formula: =IF(OR(B35"";C35"";D35"");CONCATENATE(B35+C35+D35;"x ";C28);"")
Would you be so kind to also guide me on how to include a second IF-formula (just with following cells B56, C56 and D56 with the name from cell C49? Preferably separated with a comma between the formulas, so the output would look like e.g.: 5x Ronni, 2x Alexander
I've tried with following =IF(OR(B35"";C35"";D35"");CONCATENATE(B35+C35+D35;"x ";C28);IF(OR(B56"";C56"";D56"");CONCATENATE(B56+C56+D56;"x ";C49);"")) but I can't get it to work.
Once again, thank you very much for your kind help!!
Ronni
Hello Ronni!
You just need to add another formula with the condition
=IF(OR(B13<>"",C13<>"",D13<>""), CONCATENATE(B13+C13+D13,"x ",C7),"") & ", " & IF(OR(B35<>"",C35<>"",D35<>""), CONCATENATE(B35+C35+D35,"x ",C28),"")
I hope it’ll be helpful.
Dear Alexander! Thank you very much for your answer and time. I'm not really sure why, but the output of the cell ends up only calling the first IF formula (C7 without C28). I've checked all the cells, made sure that they aren't empty. If I change the order of the two formulas, it calls (C28 without C7). And I'm not getting any error messages on the formula. Do you have any idea why that can be? Once again, thank you for your time!!
=IF(OR(B13"",C13"",D13""), CONCATENATE(B13+C13+D13,"x ",C7),"") & ", " & IF(OR(B35"",C35"",D35""), CONCATENATE(B35+C35+D35,"x ",C28),"")
Hello Ronni!
In my Excel workbook, the formula works. There are no ideas other than copy error.
Try a different version of this formula, where instead of the & operator, the CONCATENATE function is used
=CONCATENATE(IF(OR(B13<>"",C13<>"",D13<>""), CONCATENATE(B13+C13+D13,"x ",C7),""),", ",IF(OR(B35<>"",C35<>"",D35<>""), CONCATENATE(B35+C35+D35,"x ",C28),""))
I have a data from an ERP system which gives the amount with a symbol suffix 'Dr' for debit balance, and a 'Cr' for a credit balance. I want to capture the debit balances as a positive value and the credits with a negative value. Please help
Hello Thomas!
I do not know how your values are written. You did not say anything about this. The IF function will not help here. But I think that you’ll find this formula useful
=IFERROR(VALUE(SUBSTITUTE(A20,"Dr","")), -VALUE(SUBSTITUTE(A20,"Cr","")))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi,
My scenario is the following: if a date is more than 3 years old then Good...if is less than 3 years Not Good. For example if 04/17/2016 is more than 3 years old from 01/01/2020. Thanks.
Hello Chris!
To calculate the difference between dates, use the DATEDIF function. I recommend this article. Use DATEDIF with the Y parameter as a condition in the IF function.
I hope this will help, otherwise please do not hesitate to contact me anytime.
I believe I found the error in the formula. The parentheses were not placed correctly..... Correct formula: IF (A1>0,TEXT(B1-A1,"[mmmm]")," ")
I have three columns. Columns A and B contain date & time information and column C contains the difference (in minutes) between A and B. The formula being used in column C is TEXT(B2-A2), "[mm]". Currently if nothing is in column A or B the formula in column C shows 00. I would like for it to show blanks.
Please help me understand why I am unable to use the following formula so as to not see 00 in column C:
IF A2>0, TEXT(B2-A2), "[mm]", " ". Explanation: If columns A and B are not populated, column C should appear as blank and not contain 00.
Are you able to create an IF then statement in excel and embed another function formula?
Example:
Column A2 contains a start date and time: 5/15/20 14:15.
Column B2 contains an end date and time: 5/16/20 08:09.
Column C2 contains a formula to caculate the duration between columns A and B in minutes: TEXT(B2-A2), "[mm]".
I am looking for a formula where blanks will appear in column C2 if column A2 is not yet populated. I want to include the TEXT formula throughout column C, in order to auto populate the column C once columns A and B have data. Currently, using the formula TEXT(B2-A2), "[mm]" yields 00 in column C2 if A2 is blank.
I tried the following formula, but cannot get it to work: If A2>0, TEXT(B2-A2,"[mm]", " " Explanation: If nothing is in column A, column C should be blank (i.e. not show 00). Thanks in advance for your assistance.
Hello Joe!
Use the IF function with two conditions
=IF(AND(A2<>"",B2<>""), TEXT(B2-A2,"[mm]")," ")
If there is anything else I can help you with, please let me know.
Hi. I am trying to create a formula that says:
If (a1 =1 then c1 = 50) ( a1= 2 then c1 = 60) (a1= 3 then c1 = 70) (a1 = 4 then c3 = 10)
Thanking you in advance.
Hello Jeff!
A formula can only set a value in the cell in which it is written. The formula in C1 cannot change cell C3.Therefore, I assume that C3 is a mistake. Change C3 to C1. The formula in C1 is
=IFERROR(CHOOSE(A1,50,60,70,10),"")
Or use the If function
=IF(A1=1,50,IF(A1=2,60,IF(A1=3,70, IF(A1=4,10, "") ) ) )
Hi,
Could you please help me? I have column J as text - apple;orange;bananas;vegetables (spinach, fenugreek, etc); acai berry. I want to create Column K as 1 if there is orange in Column J and 0 if not OR 1 if there is spinach in Column J and 0 if not. How can I do that? Please advise. Thank you for your help!
The formula will return 1 if there is "orange" in column J.
=IF(COUNTIF(J:J,"orange") > 0,1,0)
Hope this is what you need.
Hi Alexander,
Thank you for your help but unfortunately the formula did not work. Just to clarify each row in J has continuous text such as Row 1 = apple;orange;bananas;vegetables(spinach,fenugreek,etc);acai berry
Row 2 = orange;bananas;vegetables(spinach,fenugreek,etc)
Row 3 = apple;orange;bananas
and so on
Also there is no space between the text and semi colon and following text
Please advise
Hello!
The formula I sent to you was created based on the description you provided in your first request.
However, as far as I can see from your second comment, your task is now different from the original one. Hence, the formula fails to work.
I also assume that the phrase "each row in J has continuous text" actually means "every cell in column J contains text."
Try the following formula:
=IF(IFERROR(SEARCH("orange",J2,1),0)>0,1,0)
After that you can copy this formula down along the column.
Hi,
Let say i have text "export" in C2 and C20 . Can I use formula =if(C2=C20,countifxxx,0) ?
So if I want to change the text 'export' I don't need change every formula. is that possible?
thanks to advice.
Hello!
In your IF formula, it is important that C2 = C20. What will be recorded in these cells does not matter. You can use nested functions as a TRUE or FALSE condition, including COUNTIF.
There are payroll list for June 2020
1) Fill June Payoff with this rule: if monthly Wage is less then 20 000, then pay 20 000. In other case pay just Wage itself.
What value should put in Value _if_ false
Hello Christina,
Please try the following formula:
=IF(A2<20000,20000,A2)
Hi Alexander!
Thank you for a great and helpful site!! I have three cells (B13, C13, and D13) that can contain numbers (usually nothing or somewhere between 1 to 10). If one (or more) of these three cells contain a number (if none of them are empty), I would like to extract a name that is written in cell C7 - and with the sum of the three cells (B13, C13 and, D13) listed with an "x" before the name. Example: B13 [1], C13 [empty], D13 [5], C7 [Ronni] - would generate: "6x Ronni". Is this possible to make? Thank you very much for your help!!
Hello Ronni!
Please use the following formula
=IF(OR(B13<>"",C13<>"",D13<>""),CONCATENATE(B13+C13+D13,"x ",C7),"")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thank you very much, Alexander! It works perfectly! Now, I'm trying to pull different cells with the formula that you helped me with, to list the names into the same cell/sentence, though I can't figure out why I can't make that work. Your single formula works.
=IF(OR(B14"";C14"";D14"");CONCATENATE(B14+C14+D14;"x ";C7);
=IF(OR(B35"";C35"";D35"");CONCATENATE(B35+C35+D35;"x ";C28);
=IF(OR(B56"";C56"";D56"");CONCATENATE(B56+C56+D56;"x ";C49);
=IF(OR(B77"";C77"";D77"");CONCATENATE(B77+C77+D77;"x ";C70);
=IF(OR(B98"";C98"";D98"");CONCATENATE(B98+C98+D98;"x ";C91);
=IF(OR(B119"";C119"";D119"");CONCATENATE(B119+C119+D119;"x ";C112);
=IF(OR(B140"";C140"";D140"");CONCATENATE(B140+C140+D140;"x ";C133);
=IF(OR(B161"";C161"";D161"");CONCATENATE(B161+C161+D161;"x ";C154);
=IF(OR(B182"";C182"";D182"");CONCATENATE(B182+C182+D182;"x ";C185);
=IF(OR(B203"";C203"";D203"");CONCATENATE(B203+C203+D203;"x ";C196);
=IF(OR(B224"";C224"";D224"");CONCATENATE(B224+C224+D224;"x ";C197);
=IF(OR(B245"";C245"";D245"");CONCATENATE(B245+C245+D245;"x ";C238);
=IF(OR(B266"";C266"";D266"");CONCATENATE(B266+C266+D266;"x ";C259);
=IF(OR(B287"";C287"";D287"");CONCATENATE(B287+C287+D287;"x ";C280);
=IF(OR(B308"";C308"";D308"");CONCATENATE(B308+C308+D308;"x ";C301);
=IF(OR(B329"";C329"";D329"");CONCATENATE(B329+C329+D329;"x ";C302);
=IF(OR(B350"";C350"";D350"");CONCATENATE(B350+C350+D350;"x ";C343);
=IF(OR(B371"";C371"";D371"");CONCATENATE(B371+C371+D371;"x ";C364);
=IF(OR(B392"";C392"";D392"");CONCATENATE(B392+C392+D392;"x ";C385);
=IF(OR(B413"";C413"";D413"");CONCATENATE(B413+C413+D413;"x ";C406);
=IF(OR(B434"";C434"";D434"");CONCATENATE(B434+C434+D434;"x ";C427);
=IF(OR(B455"";C455"";D455"");CONCATENATE(B455+C455+D455;"x ";C448);
=IF(OR(B476"";C476"";D476"");CONCATENATE(B476+C476+D476;"x ";C469);
=IF(OR(B497"";C497"";D497"");CONCATENATE(B497+C497+D497;"x ";C490);
=IF(OR(B518"";C518"";D518"");CONCATENATE(B518+C518+D518;"x ";C511);
=IF(OR(B539"";C539"";D539"");CONCATENATE(B539+C539+D539;"x ";C532);
=IF(OR(B560"";C560"";D560"");CONCATENATE(B570+C570+D560;"x ";C553);
=IF(OR(B581"";C581"";D581"");CONCATENATE(B581+C581+D581;"x ";C574);
=IF(OR(B602"";C602"";D602"");CONCATENATE(B602+C602+D602;"x ";C595);
=IF(OR(B623"";C623"";D623"");CONCATENATE(B623+C623+D623;"x ";C616);
;""))))))))))))))))))))))))))))))
Ideally, I'd also try to separate the strings with a comma and space, so it'd generate 5x Ronni, 2x Alexander, 1x Pedro - though trying to take small babysteps here, haha.
Once again, thank you for taking your time to help!
Sorry, let me correct my question:
I have three cells (B13, C13, and D13) that can contain numbers (usually nothing or somewhere between 1 to 10). IF one (or more) of these three cells contains a number, I would like to extract a name that is written in cell C7 - and with the sum of the three cells (B13, C13 and, D13) listed with an "x" before the name. Example: B13 [1], C13 [empty], D13 [5], C7 [Ronni] - would generate: "6x Ronni".
If A+ then "Outstanding"
If A then "Excellent"
If B+ then "Very Good"
If B then "Good"
If C+ then "Satisfactory"
If C then "Acceptable"
If D+ then "Partially Acceptable"
If D then "Insufficient"
If E then "Very Insufficient"
For this condition I used "=IF(N7, IF(N7="A+", "Outstanding", IF(N7="A", "Excellent", IF(N7="B+", "Very Good", IF(N7="B", "Good", IF(N7="C+", "Satisfactory", IF(N7="C", "Acceptable", IF(N7="D+", "Partially Acceptable", IF(N7="D", "Insufficient", "Very Insufficient")))))))))" but it show "VALUE!".
Pls Help me
Hello!
If I understand your task correctly, the following formulas should work for you
=IF(N7="A+","Outstanding",IF(N7="A","Excellent", IF(N7="B+","Very Good",IF(N7="B","Good", IF(N7="C+","Satisfactory",IF(N7="C","Acceptable",IF(N7="D+","Partially Acceptable", IF(N7="D","Insufficient","Very Insufficient"))))))))
I hope it’ll be helpful.
=IF(R3="CLower",V3*100%)
How can I make the above formula repeatedly and give me different relevant answers without false or true
Hello James!
I’m sorry but your task is not entirely clear to me.
For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.
Would it be possible to use an IF statement, to say if a cell is still empty after a date has passed the "True" would be "OK" and "False" would be "Late"?
Hello Doug!
If I understand your task correctly, the approximately this formula should work for you:
=IF(AND(B2 > A2,C2=""),"OK","Late")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi,
could you please help.
I am trying to find a way to do this using an IF statement that would do the following: IF(A=B,C,do nothing), in other words if the condition A=B, is true, put the value C in the cell, else if the condition fails, leave the old value in the cell. There is a way to do this in Excel but I haven't been able to find. Instead I have to manually open each report sheet and do a copy and paste from where the report I receive.
Hello Bewar!
If a value is already written in the cell, then it is no longer possible to write the formula into it. The Excel function can’t change values in other cells, it returns a value only in a cell where it is written. You'd better use VBA to solve your task.
Hi,
what a good and clear explanations on the website, credits to the maker. however i am not an excel expert and still cant figure out how to move on with what i want
i have 2 check boxes 1 in H4 (with comment 12hrs) and 1 in I4 (with comment 24hrs)
now i want in the mergedbox below H/I 5 to appear a text when checking one of the boxes
if box H4 is checked i want to see 1 x operator
if box I4 is checked i want to see 2 x operator
every time i try to make an if formula excel gives an error that i start a formula and i need to put an ' before the text. can you please help me :)
thanks in advance,
regards kasper,
Hello Kasper!
If I understand your task correctly, the following formula should work for you:
=IF(H5=TRUE,1*H9,IF(I5=TRUE,H9*2,H9))
I don’t understand what “operator” is, so I replaced it with H9.
Hope this is what you need.
unfortunately its not working, 1x operator is just the text I want to show in cell H5
so like they only need one person for 12 hours work or 2 persons for 24 hours of work
I’m sorry but your task is not entirely clear to me.You did not explain your problem in detail.
For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.
Hi, sorry my story is not clear.
in short i have 2 check boxes and i want to show a text in an other cell when checking the check box 1 and if i checking check box 2 i want to show an other text in a different cell
regards kasper
Hi,
Good morning to all of you.
Please suggest me a formula.
=if(A1>=10,"Same Value of A1", if(A1<10, "Please Put One Zero Before Value of A1"))
Hello Praveen!
Unfortunately, you did not write what result you wanted to receive. Perhaps your formula should be written as follows:
=IF(G2> = 0.5 * C2, "APPROVE", "DISAPPROVE")
Hope this is what you need.
Hi, I've been reading your article and i must say it's outstanding. However though it was useful in some areas I still found myself stuck.
I've been trying to insert an if function
=If(G2>=0.5,C2,"APPROVE", "DISAPPROVE")
But its not working
Any Help is appreciated
Hello Cresia!
Unfortunately, you did not write what result you wanted to receive. Perhaps your formula should be written as follows:
=IF(G2> = 0.5 * C2, ”APPROVE”, “DISAPPROVE”)
I hope my advice will help you solve your task.
Thanks for the article.
I need help with writing out a formula that says, if column B = yes and column C = yes, then D should say True.
So far I tried:
=IF(B2="YES",TRUE(IF(C2="YES",TRUE),False))
Looking forward to hearing from you.
Rgs
The formula below will do the trick for you
=IF(AND(B1="Yes",C1="Yes"),TRUE,"")
Write in cell D1.
I recommend that you study this instruction about using AND and OR with the IF function.
Hi
I would like to return a value to be 1 if the division of the two cell equals or greater than 1 and .5 if it's less than 1
=if(c5/d5>="1","1",".5")
I inputted the formula above but it's not showing the proper value..
Hope it makes sense. Much appreciated, ty!!
Hello Priscilla!
If I understand your task correctly, the following formula should work for you:
=IF(C5/D5 >= 1,1,0.5)
I hope this will help
i have "NEFT/BKIDN20137329397/GCHZ0000M12104/ " in one worksheet and "BKIDN20137329397" in other workbook with some values. how to use if formula to bring these values in first worksheet.
Hello
I am struggling with an IF formula, hopefully i have explained it clear enought.
I want to return a value to cell V1 (the cell i am entering the formal into), if the value of cell U1 is greater than 0.1. The value i want to return is in cell T1, if the value is less than 0.1, i want to return a value of 0.
Any help would be much appreciated
Thank-you
Hello Sarah!
If I understand your task correctly, the following formula should work for you:
=IF(U1 > 0.1,T1,0)
I hope this will help
Hello Mathew
How can I delete a group of numbers that have same last digit from a list.
For eg: 10, 22, 31, 32, 35, 38, 40, 42, 44, 50, 51, 52, 62, 63
From this list numbers have last digit '2' are to be deleted. 22, 32, 42, 52 and 62
I appreciate any help.
Thank you.
Hello,
I am struggling with my nested IF formula. I am trying to return a "Y" or "N" if the given date meets certain requirements but sometimes strings of text can be found in the cell. If the string of text matches I would like it to return "N" or "Y" based off the requirements. Any help would be greatly appreciated.
=IF(EDATE(A5,12)<TODAY(),"N",IF(A5TODAY()+30,OVERDUE,IF(SEARCH("OVERDUE",A5),"N","Y"))))
Hello Matthew!
If I understand your task correctly, the following formula should work for you:
=IF(EDATE(A5,12)
Hello,
I have some dates in column K that I periodically add to or update, also some blanks that don't have dates added yet. I would like a formula for column L that gives dates 6 months in the future if there is a date in column K. However, if there is no date (blank cell) in column K, I would like the corresponding L cell to remain blank until a date in inputted into the K cell. Hope that makes sense! I appreciate any help.
Hello Teresa!
If I understand your task correctly, the following formula should work for you:
=IF(K1 <> "",EDATE(K1,6),"")
After that you can copy this formula down along the column.
Hope this is what you need.
I cant find what the problem is with this simple thing
=IF(BASE!MONTH=ENGINE!B1,"yes","No"
Hello Herman!
You did not indicate exactly which error occurs in your formula. However, MONTH is a function and cannot be a link. Therefore, BASE! MONTH does not make sense and is a mistake.
The formula lacks the brackets at the end.
If you give more complete information, perhaps I can still suggest something else.
I have a row of cells which have values in for lab results - I can't figure out a formula to work out a minimum value where values are either numbers or come as say <0.05 etc. i.e where they are less than the laboratories limit of detection (<LOD). I want the minimum number so i want it to register that <0.05 is less than 0.06 either through the presence of a less than symbol or any other way. I tried to play around with any cell being greater than 0 calculate a minimum and for where cells do not to populate as"<LOD". i.e. 1.9 , < 0.05, 0.29 would return a minimum of "<LOD". 1.9, 0.06, 0.29 would return a minimum of 0.06 and < 0.05 < 0.05 < 0.05 would return a minimum of "<LOD". Any help would be greatly appreciated - I can't find any examples where you have an if statement followed by a function if the statement is true, and to present a text value if not ("<LOD").
Hello Nic!
If I understand your task correctly, the following formula should work for you:
=IF(NOT(ISERROR(MATCH("<*",A7:A15,0)>0))," < LOD",MIN(A7:A15))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Alexander,
Thank you for your time - this seemed to work perfectly. Is there a way to do the same for the max, i.e highest number or <LOD if all are less than detection. I've tried playing around with the formula to try for max but can't get it to work for all cases. Please could you assist. Thanks again.
Hello Nic!
I’m sorry but your task is not entirely clear to me.
For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find. It’ll help me understand it better and find a solution for you. Thank you.
Hi Alexander.
So I have two columns in the spread sheet, one which calculates the minimum concentration from all the values from the lab results, which now works using your formula to pick the lowest concentration - <LOD (< limit of detection) or the lowest number if above that. The second column I need your assistance with is to work out the maximum value for all the cells in the row, as before some have < 0.05 for example, if all the concentrations are above this that's fine but it doesn't populate an answer for where all are <LOD. And the maximum in that case would be <LOD. Hope that clarifies things?
Hello Nic!
If I understand your task correctly, the following formula should work for you:
=MAX(--(SUBSTITUTE(A7:A10,"<",""))) Hope this is what you need.