September 15, 2021

I received a lot of questions on how to use IF function with 3 conditions, so I've decided to write an article on this topic.

The IF examples described in this article assume that you have a basic understanding of how the IF function works. All examples from this article work in Excel for Microsoft 365 or Excel 2019, 2016, 2013, 2010, and 2007.

If you are unfamiliar with the IF function, then I strongly recommend reading my article on Excel IF function first. It's a step by step guide, and it includes a lot of useful examples. Make sure you also download the exercise file.

As a data analyst, you need to be able to evaluate multiple conditions at the same time and perform an action or display certain values when the logical tests are TRUE. This means that you will need to learn how to write more complex formulas, which sooner or later will include Excel IF statements with multiple conditions.

Let's take a look at how to write a simple IF function with 3 logical tests.

The first example uses an **IF statement with three OR conditions**. We will use an IF formula which sets the *Finance *division name if the department is *Accounting*, *Financial Reporting*, or *Planning & Budgeting*.

The IF statement from cell E31 is:`=IF(OR(D31="Accounting", D31="Financial Reporting", D31="Planning & Budgeting"), "Finance", "Other")`

This IF formula works by checking three OR conditions:

- Is the data from the cell
`D31`

equal to`Accounting`

? In our case, the answer is no, and the formula continues and evaluates the second condition. - Is the text from the cell
`D31`

equal to`Financial Reporting`

? The answer is still no, and the formula continues and evaluates the third condition. - Is the text from the cell
`D31`

equal to`Planning & Reporting`

? The answer is yes, our IF function returns TRUE, and displays the word Finance in cell E31.

Next, we focus our attention on an example that uses an **IF statement with three AND conditions**.

Our table shows exam scores for three exams. If the student received a score of at least 70 for all three exams, then we will return Pass. Otherwise, we will display Fail.

The IF statement from cell H53 is:`=IF(AND(E53>=70, F53>=70, G53>=70), "Pass", "Fail")`

This IF formula works by checking all three AND conditions:

- Is the score for Exam 1
`higher than or equal to 70`

? In our case, the answer is yes, and the formula continues and evaluates the second condition. - Is the score for Exam 2
`higher than or equal to 70`

? Well, yes it is. Now the formula moves to the third condition. - Is the score for Exam 3 higher than or equal to 70? Yes, it is. Since all three conditions are met, the IF statement is TRUE and returns the word Pass in cell H53.

The final section of this article is focused on how to write an Excel IF statement with multiple conditions, and it includes two examples:

- multiple nested IF statements
- formula with a mix of AND, OR, and NOT conditions

In this example, I have calculated the grade of the students based on their scores using a **formula with 4 nested IF functions**.

`=IF(E107<60, "F", IF(E107<70, "D", IF(E107<80, "C", IF(E107<90, "B" ,"A"))))`

Note: In this case, the order of the conditions influences the result of your formula. When your conditions overlap, Excel will return the [value_if_true] argument from the first IF statement that is TRUE and ignores the rest of the values. If you want your formula to work properly, always pay attention to the logical flow and the order of your nested IF functions.

Note: If you have Office 365 installed, then you can also use the new IFS function. You can read more about IFS on Microsoft’s website.

I have saved the best for last. This example is the most advanced from this article, as it involves an IF statement with several other logical functions.

In the exercise file, I have included a list of orders. Each row includes the order date, the order value, the product category, and the free shipping flag. We want to flag orders as eligible if the following cumulative logical conditions are met:

- the order was placed during
`2020`

- the order includes products from only two categories:
`PC`

or`Laptop`

- the order was
`not`

flagged as`Free shipping`

The formula I've used for cell H80 is shown below:

`=IF(AND(D80>=DATE(2020,1,1), D80<=DATE(2020,12,31), OR(F80="PC", F80="Laptop"), NOT(G80="Yes")), "Eligible", "Not eligible") `

Here's how this works:

`AND`

makes sure that all the logical conditions need to be met to flag the order as Eligible. If any of them is FALSE, then our entire IF statement will return the [value_if_false] argument.`D80>=DATE(2020,1,1)`

and`D80<=DATE(2020,12,31)`

check if the order was placed between January 1st and December 31st, 2020.`OR`

is used to check whether the product category is`PC`

or`Laptop`

.- Finally,
`NOT`

is used to check if the Free shipping flag is different from`Yes`

.

And there you have it. I hope that after reading this guide, you have a much better understanding of using IF function with 3 logical tests (or any number actually). While it may seem intimidating at first, I guarantee that once you start using these functions daily, your productivity will skyrocket.

If you struggle to write more complex formulas, then I strongly suggest investing in an Excel course. Mynda Treacy from myonlinetraininghub.com has an excellent collection of courses, ranging from Beginner to Specialist.

If you have any questions on how to use IF function with 3 conditions, then please leave a comment below and I will do my best to help you out. I reply to every comment or email that I receive.

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.

View coursesExcel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.

Copyright © 2017-2021 ExcelExplained.com

Hi,

If I want to create a formula that for values 33 and under I get a Low result, for values higher than 33 and lower than 66 I get a Medium result and values over 66 I get a High result how would I formulate that formula. Ican get the Low and High but the medium I get a False result.

This is my formula:

=IF(F53367, "High")))

Hi,

I think you have trimmed your formula by mistake, because it only shows a small part of it. Please try the formula from below, replacing cell A1 to match your desired cell:

=IF(A1<=33, "Low", IF(A1<=66, "Medium", "High")) Let me know if that works for you.

Need help for this comment

IF(and(b2=“0005”, c2=“0003”), ”both”, ””), or(if(and(b2=“0005”, c2=“”, ”Sea”, ”Air”)), or((if(and(b2=“”, c2=“”, ”check”)))

Hi Vin,

I am not sure exactly what you are trying to achieve, because your formula is using a lot more parameters than needed for an IF statement. Can you please describe in words what you want to return in each case? I will then help you out with the formula, but now there are too many conditions to determine what is your desired output.

All the best,

Radu

I have created a formula for you. Let me know if this is what you are trying to achieve.

=IF(AND(B2="0005", C2="0003"), "both", IF(AND(B2="0005", C2=""), "Sea", IF(AND(B2="", C2="0003"), "Air", IF(AND(B2="", C2=""), "check"))))

Basically, it works like this:

1) if B2 = 0005 and C2 = 0003, then it will return "both"

2) if B2 = 0005 and C2 = "", then it will return "Sea"

3) if B2 = "" and C2 = 0003, then it will return Air

4) if B2 = "" and C2 = "", then it will return "check"

5) in any other case it will return FALSE.

I have 6 cells(D5 to D10) showing "Completed" or "Pending". I'm trying to get it so that if each cell is "Completed" then show "Finished" in next column (E5 to D10) or if it is "Pending" then show "Unfinished" in next column (E5 to D10) or if it is BLANK, show EMPTY cell. However, I then want cell E15 to show "Completed" if all the cells are "Completed" and/or blank and to "Pending" if at least one cell is "Unfinished".

It seems as if it should be somewhat simple(ish) but I'm racking my brain.

Any Suggestions??

Hi Raymond,

The formula in cell E15 should look something like this:

=IF(OR(COUNTIF(E5:E10, "Finished")= COUNTA(D5:D10), COUNTBLANK(E5:E10)= COUNTA(D5:D10), COUNTIF(E5:E10, "Finished")+ COUNTBLANK(E5:E10)= COUNTA(D5:D10)), "Completed", IF(COUNTIF(E5:E10, "Pending")>0, "Pending"))

Basically, it counts the cells with content from the range D5:D10 and checks how many cells from the range E5:E10 contain "Finished" or are blank. If the numbers are equal, then it means that all cells are either blank or contain the word "Finished".

If this isn't the case, then it checks if there are any cells that contain the word "Pending". Keep in mind that the input needs to be blank, "Finished" or "Unfinished". Otherwise, the formula will return FALSE because it has no default value in case the rest are not true.

How do you write if K1 is greater than but less than 10 then this equation, but if K1 is greater than 10 but less than 20, do this equation, BUT if K1 is greater than 20 but less than 30 then this equation, etc...

Hi Wayne,

The formula would look something like this:

=IF(AND(K1>0, K110, K120, K1<30), "Equation 3", "Value if all are FALSE")))

Just replace "Equation 1" with whatever formula you would like to use. "Value if all are FALSE" should be replaced with whatever value, message, or formula that you want to use in case the value from K1 doesn't match your criteria.

I hope that helps.

All the best,

Radu

=IF(AND(0<K1<10.1, K1*O12, IF(AND(10.1<K1<20.1, (10*O12)+ ((K1-10)*O13), IF(AND(20.1<K1<30.1, (10*O12)+ (10*O13)+ ((K1-20)*O14), IF(AND(30.1<K1<40.1, (10*O12)+ (10*O13)+ (10*O14)+ ((K1-30)*O15), IF(AND(40.1<K1<50.1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ ((K1-40)*O16), IF(AND(50.1<K1<60.1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ (10*O16)+ ((K1-50)*O17), IF(AND(60.1<K1<70.1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ (10*O16)+ (10*O17)+ ((K1-60)*O18), IF(AND(70.1<K1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ (10*O16)+ (10*O17)+ (10*O18)+ ((K1-70)*O19)

Why won't this work?

Correction**

=IF(AND(0<K1<10.1, K1*O12, IF(AND(10.1<K1<20.1, (10*O12)+ ((K1-10)*O13), IF(AND(20.1<K1<30.1, (10*O12)+ (10*O13)+ ((K1-20)*O14), IF(AND(30.1<K1<40.1, (10*O12)+ (10*O13)+ (10*O14)+ ((K1-30)*O15), IF(AND(40.1<K1<50.1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ ((K1-40)*O16), IF(AND(50.1<K1<60.1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ (10*O16)+ ((K1-50)*O17), IF(AND(60.1<K1<70.1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ (10*O16)+ (10*O17)+ ((K1-60)*O18), IF(AND(70.1<K1, (10*O12)+ (10*O13)+ ((K1-20)+ (10*O14)+ (10*O15)+ (10*O16)+ (10*O17)+ (10*O18)+ ((K1-70)*O19)))))))))))))))))))))

AND function is used in a different way. If you want to use two conditions (for example K1>0 and K10, K1<0).

Hello. How do I automatically get a sum of for example the first 10 smallest values from a column of 100 values without sorting them?

And also how to get the average of the same 10 smallest values without.

Hi Francis,

Thanks for reaching out. I think the easiest solution would be to use SUMPRODUCT along with SMALL, ROW, and INDIRECT. The formula would look like this:

=SUMPRODUCT(SMALL(A1:A100, ROW(INDIRECT(“1:10”))))

A1:A100 is the range containing the 100 numbers, and “1:10” dictates the number of values you want to sum. If you change it to “1:20”, then the formula would return the sum of the smallest 20 numbers from your data.

To get the average, divide the result of this formula by 10 (or whatever value you have used for the formula above) or include the denominator directly in your formula, like this:

=SUMPRODUCT(SMALL(A1:A100, ROW(INDIRECT(“1:10”))))/10

Hey,

Suppose i want to apply if formula in an excel and pull it down so that whenever someone going to put any value in the designed table, it will show the result automatically, but is there any option that i can leave the formula cell blank till data put into the cells to calculate the same.

Hi,

You can use the IF function along with ISBLANK to check if the cell is empty. The formula looks like this:

=IF(ISBLANK(A1), "", A1*30%)

The formula checks if cell A1 is blank, and if it is, then it doesn't display anything (represented by ""). Replace the formula A1*30% with whatever formula you want to use, and you are good to go.

Let me know if that works.

All the best,

Radu

What would you do if you have the following:

IF a certain country and IF a certain project type, and IF a certain qtr, then return a value from a table that associates the conversion of a sale to revenue over time based on the country sold and the type of work being performed.

Hi Matt,

It's a bit tricky to answer your questions without actually knowing how your Excel file is structured. I see three possible solutions, depending on your scenario:

1. If you don't have many possible criteria combinations, then using nested IF functions could do the trick. However, this can be difficult to write once you have a lot of possible outcomes.

2. If you have data stored as a matrix, you could use an INDEX-MATCH combo to retrieve the correct data from your table that matches your criteria.

3. Alternatively, SUMPRODUCT can be used to achieve the same result and INDEX-MATCH.

I hope that helps.

Radu

I need help on this calculator. The minimum entry is 90 with 8% earning and maximum is 250 with 15% earning. Help with the formulae to calculate those between 90 and 250 including minimum and maximum limit of 90 and 250 respectively

Hi George,

I am not sure what you are trying to achieve. For example, if you insert 150 into your formula, what should be the % earnings used in the calculation? You just mentioned 8% for 90 and 15% for 250, but nothing in between. How do the earnings fluctuate based on user input?

Radu

Dear Radu

I want to be calculating the % of the score for those that will be scoring between 91 = 8% and 253 = 15% using the IF condition i.e. What will be the % of those that have scored between 92 - 252?

Am developing an incentive and the entry level is 91 scoring 8% and the maximum is 253 scoring 15%. If one scores below 91 that is a zero and if they score more than 253 they earn 15%. What is the formula that will calculate anything between 92 and 252?

Hi George,

The formula would look like this:

=IF(A1<91, 0%, IF(A1>253, 15%, (A1-91)/(253-91)*(15%-8%)+8%))

where cell A1 contains the score. Just replace it within your file with whatever cell you are using and you are good to go. As long as the score is between 91 and 253 the formula will calculate the incentive based on a pro-rata system. If the score is below 91 the incentive will be 0% and when the score is above 253 the incentive is capped at 15%.

You can improve the formula by referencing the cells that contain the low and high points (i.e. 91 and 253) to make the formula adjust automatically if you decide to change the values.

Let me know if it works for you.

Radu

Am developing an incentive and the entry level is 91 scoring 8% and the maximum is 253 scoring 15%. If one scores below 91 thats a zero and if they score more than 253 they earn 15%. What is the formula that will calculate anything between 91 and 253?

I have a settlement sheet where cell I65 total is based on if cell G5="Verses" plus D70>H4 the calculation should be D70-I64 or if G5="Plus" then D70 if neither then 0

Hi Gary,

I am currently away on holiday, so I wrote this on my mobile. I hope it works.

=IF(AND(G5="Verses", D70>H4), D70-I64, IF(G5="Plus", D70, 0))

Let me know if this solves your issue.

Almost but when I have G5 ="Plus" instead of D70 it shows 0

Hi Gary,

Are you sure there are no spaces in cell G5 (i.e. “Plus “ or “ Plus” instead of “Plus”)? This is the most common issue, as the formula is looking for no spaces at all.

I tested in on my iPad as I was writing the formula and it was working properly.

I have three options to select from the drop box namely: YES, NO and SOMEHOW. How do I write a formula such that YES = 10, SOMEHOW = 5 and NO = 0?

Hi Freeman,

You could use this:

=IF(A1="YES", 10, IF(A1="SOMEHOW", 5, IF(A1="NO", 0)))

Just replace cell A1 with the cell that contains your dropdown list and you should be good to go.

Hello,

I am trying to make a formula for below scenario, but I am not sure how do I get it right.

My condition is the column F2 three options a.SOW, b.CR and MA Amendment and C.MA and I would like to set a formula that says if CR and MA Amendment execution time is 45 days (the days are taken from K2) then SLA has met, For a SOW the if the project execution time is 60 days then SLA has met and for an MA if the project is executed with in 90 days the SLA is met.

Would you please help me with the formula that could capture it.

Hi Unnati,

What should your formula return if the SLA is not met? Also, I believe a lower execution time is still good, right? For example, is an execution time of 37 days good or does it need to be exactly 45?

Radu

=IF($H27="", "", IF($G27="W", IF(($H27-J$20)>17, (J$19+2), IF(($H27-J$20)>-1, (J$19+1), (J$19))), IF(($H27-J$18)>17, (J$17+2), IF(($H27-J$18)>-1, (J$17+1), (J$17)))))

Used this formulae in golf scoring system to determine the results to par of individual players "W" identifies woman as their strokes are different. Handicap system changed I need to make provision for handicaps >36 which was not the case in the past

Need to cate for Handicaps >36 and it would require 3 statements

If > 36, do x, if >17 but 36, (J$19+3), IF(($H27-J$20)>17, (J$19+2), IF(($H27-J$20)>-1, (J$19+1), (J$19))), IF(($H27-J$18)>17, (J$17+2), IF(($H27-J$18)>-1, (J$17+1), (J$17)))))

Think problem is that the statement is two tier as it needs to look at smaller than 36 and > 17 to determine result

Hi Coert,

The idea with multiple nested IF statements is that if one is true, the following are ignored. Basically, if you are checking if a numer is greater than 17 and that is indeed higher, then whatever comes next is ignored. I am not familiar with golf, but if I was to rewrite that formula i’d use the AND function to check if the number is between 17 and 36 like this:

IF(AND(($H27-J$20)>17, ($H27-J$20)<36), and then add whatever you want like you usually do in an IF statement.

If you can’t make this work, then let me know and I’ll help you out on Sunday when I get home from my holiday.

Radu

Hi, could I ask if I have a range of cells to which I want to use the IF function to set 3 different conditions?

1. If the cell is empty, return "not completed"

2. If the cell is filled, return "done"

3. If the cell is highlighted in red (via conditional formatting), return "error"

Below is my formula for conditions 1 and 2.

Context: This formula is in a summary sheet, S9 & S13 are headers

=IF((OR('Daily BsC'!S6:S8="", 'Daily BsC'!S10:S12="", 'Daily BsC'!S14:S20="")), "Not Completed", "Done")

How do I get the 3rd condition into the formula?

Hi Jintang,

As far as I know, there is no way to trigger an IF statement based on the color of a cell.

Hi Sir,

I need help to have the result cell in B2. to show either "Pass" "Fail" or "Incomplete."

The range cell entry is B3 to B11 is for user to enter Pass or Fail, if the user leave it blank the results cell must show "incomplete". But I have a condition that is if any cell one in B3 to B11 is Fail the results cell in B2 Must show "Fail". Can you help ?

Hi Leong,

Here is the formula:

=IF(COUNTBLANK(B3:B11)>0, "Incomplete", IF(COUNTIF(B3:B11, "Fail")>0, "Fail", "Pass"))

Keep in mind that you need to restrict the input to blank, Pass or Fail for this formula to work properly. Otherwise, if the user inserts any text except "Fail" in all cells, the formula will still return "Pass." You can easily do this with Data validation.

Let me know if this is what you are looking for.

All the best,

Radu

Hi Radu,

formula:

=IF(COUNTBLANK(B3:B11)>0, "Incomplete", IF(COUNTIF(B3:B11, "Fail")>0, "Fail", "Pass"))

Thank you so much for your help. How do I make the results to show once any cell that "fail" the results cell MUST show " Fail " regardless of empty cell or pass cell. User may not complete or input the entire range cell

Thanks

Try this:

=IF(COUNTIF(B3:B11, "Fail")>0, "Fail", IF(COUNTBLANK(B3:B11)>0, "Incomplete", "Pass"))

im trying to create formula for below , Kindly help

If a2 is bus and a3 is short answer should be 0.17271

if a2 is eco and a3 is shott answer should be 0.17271

if a2 is eco and a3 is med answer should be 0.09695

if a2 is eco and a3 is long anshwer should be 0.08740

Hi Abrar,

Here is the formula:

=IF(AND(A2="bus", A3="short"), 0.17272,

IF(AND(A2="eco", A3="short"), 0.17271,

IF(AND(A2="eco", A3="med"), 0.09695,

IF(AND(A2="eco", A3="long"), 0.0874))))

Let me know if you need additional help.

Hie. What formula can help in this scenario

A student has the following scores

Maths 78, Physics 60, Chemistry 46, Biology 55 and Communication 50.

I want that the passing student should score >= 50 in all the subjects and should also pass if she/he has < 50 in one subject of any of the subjects except in communication. Please help

Hi Mark,

I am not sure that I understand your requirements. Can a student pass if they fail only one exam, as long as it is not Communication? Do they fail when they obtained less than 50 in 2 exams if those are not Communication?

Radu

Hi Mark,

For this formula I have assumed that columns A to E have the following grades:

column A: Math

column B: Physics

column C: Chemistry

column D: Biology

column E: Communication

So, when I reference a cell from column E, I refer to the grade for Communication. Here is the formula:

=IF(COUNTIF(A2:E2, "<50")>1, "Fail", IF(AND(COUNTIF(A2:E2, "<50")=1, E2<50), "Fail", "Pass"))

Let me know if this is what you were looking for.

Thanks,

Radu

I am doing an excel sheet with three options, Past Due, On-Time, and Empty. I used the IF formula, but it gives a binary answer as Pass Due or On Time. I would like to know if there is a formula that can let me have the three options that I need.

As you can see below I entered data and has different requirements, when is higher than 1 day is Past Due, anything that is less than 23hr is On Time, and when is not information in the box, I want that say Empty.

So my question is: is it a formula that can do this? I appreciate it for your help thanks

8/30/2021 8/30/2021

10:15:00 AM 11:15:00 AM

9/1/2021 8/30/2021

10:10:00 PM 6:00 PM

2 days11 hours 55 minutes 0 days6 hours 45 minutes 0 days0 hours 0 minutes

Pass Due On Time Empty

Thanks,

Hi,

You can add another IF statement to your current formula and check if the cell is empty. The formula would look like this (replace cell A1 with the cell you want to check):

=IF(ISBLANK(A1), "Blank", "Your existing formula")

Just delete the text "Your existing formula" and replace it with the current formula you are using.

Let me know if it works.

All the best,

Radu

I am struggling to get if function working for below condition.

If price is less than 1000, value A.

If price is equal to or above 1000 but less than or equal to 5000, value B.

If price is above 5000, value C

Pleas help

Hi Buushan,

Here is the formula:

=IF(A1<1000, "Value A", IF(AND(A1>=1000, A1<5000), "Value B", "Value C"))

Replace cell A1 with your own cell and adjust values A, B, and C.

Cheers,

Radu

Given formula below, my problem is that,Suppose student get Marks in three subject and formula required that if result is PASS then only Grade required other wise FAIL required.

N BELOW FORMAL IF SUPPOSE STUDENT FAIL IN ONE SUBJECT AND TOTAL IS GRATER THAN 240, GRADE DISPLYED "DIST" INSTED OF "FAIL". PLEASE SOLVE AND REPLY.

=IF(G4>240, "DIST", IF(G4>210, "FIRST", IF(G4>180, "Second", IF(G4>120, "Pass", "FAIL"))))

Hi Sunil,

The data you have provided lacks the info regarding how many subjects are failed by the student. You only reference cell G4 in your formula, but that is the total score. Where is the number of failed exams stored in your Excel sheet? Without it, there is no way to adjust the formula to take into account this info.

how to create a formula for this scenario "Jessie had a meeting with the ADM who offered a special $1,000 grant if the Actual came in up to $1,000 more than Estimated. Peter then adjusted the IF function to include a scenario where the Actual could be almost $1,000 higher than Estimated and still return a "GO". Create this formula. We could of course just add $1,000 to the budget (Estimated), but in future years you could get more or less as a grant, so it's good to keep this option as a separate GO, NOGO category".

Hi Clea,

I would make the formula dynamic so that I can insert the Grant value in one cell and get everything else calculated based on the Estimated and Actual values. For this, I have used the following cells:

A3: actual

A4: estimated

A5: grant

The formula looks like this:

=IF(actual-estimated<1000, "GO", "NOGO")

Just replace the cells to match your file, and you should be ok.

I hope this helps.

if you dont mind me asking

I am working on 6 if condition and kinda stuck

=IF(AND(G200>=$P$199; G200=$P$200; G200=$P$201; G200=$P$202; G200=$P$203; G200<P204); $Q$203))

what I got is an too many arguments for this function...

should I use other function?

or is there something wrong with my formula?

Hi Caesar,

If you are using the semicolon as the separator, then I think your formula would look like this:

=IF(AND(G200>=$P$199; G200=$P$200; G200=$P$201; G200=$P$202; G200=$P$203; G200

Hello, can we use IF function to put multiple different types of content in a cell like

Xyz Compnay

1234 A. Hollywood St.

City, State, Country, ZipCode

US

+1 111-111-111

+1 222-222-2222

info@xyz.com

http://www.xyz.com

into respective cell column like Company Name, Street Address, City, State, Country, ZipCode, Phone Number, Fax Number, Email, Website ?

Hi,

Unfortunately not. However, you can achieve this using Text to Columns option from the Data Tools. In the Delimiters field, make sure you select only Other. Select the box to insert the delimiter and then hold down ALT and write 0010. Then, select Next, check if your data is ok and click Finish.

If you are not familiar with splitting data using Text to Columns option, read the first part of this article.

Hi, Im trying to set of a covid outbreak Risk by determining 2 or more positive cases in a department that have dates within 14 days of each other. How would i set this up? i tried nesting a duplicate within an if statement and adding that there must be a positive as well but i cant figure out how to compare dates to be within 14 days of eachother AFTER meeting the first 2 requirements (2 in the same department that tested positive).

Hi Natalie,

The problem you are facing doesn't have an easy solution with just a formula. Since you need to check and compare more dates, match them also with the corresponding departments, and then see if those fall within 14 days of each other, it creates complexity. It's hard to say what the best approach would be without seeing your file, but I would suggest using some helper columns or a pivot table with an extra calculated column for the time between infections.

Maybe this post from Chandoo.org can help you. Just replace the clients with the departments and the order dates with infection dates. It should be a good starting point for your challenge.