Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Satisfy two if conditions

1. Member
Join Date
11th March 2005
Location
Columbia, Maryland
Posts
20

## Satisfy two if conditions

I don't know the correct terminology to use to research how to fix this problem. Please bear with me.

I want to satisfy two If statements, then substitute one of two answers in a cell.

Let's say that cell D11 says "Yes" and D12 Says "No"

How do I create a formula that puts N/A in a cell if D11 is Yes and D12 is No or puts "Correct" in the cell if both conditions are not met?

I know how to do it with one if statement

*=IF(D11 = "Yes", "N/A")* but I can't figure out how to say

If D11 = "Yes" and D12 = "No", "N/A" and, if not, "Correct"

Excel Video Tutorials / Excel Dashboards Reports

2. Member
Join Date
21st May 2005
Location
Posts
22

## Re: Satisfy two if conditions

Hi,

Use
=if(and(d11="Yes",d12="No"),"N/A","Correct")

Excel Video Tutorials / Excel Dashboards Reports

3. Member
Join Date
11th March 2005
Location
Columbia, Maryland
Posts
20

## Re: Satisfy two if conditions

Thank you so much. I just couldn't figure out how to combine the two statements.

I took the logic from your response and added it to my real formula and it worked great but I still want to tweak it a bit and need your help.

The statement below says if D11 = Yes and D12 = No and the month from the date in cell I5 is less than 7, write 1/1/2006 and if the date is more than 7, write 7/1/2006.

*=IF(AND(D11="Yes",D12="No")*AND(MONTH(I5)<7),DATE(YEAR(I5)+1,1,1),DATE(YEAR(I5)+1,7,1))*

The formula works great. How can I tweak it a bit to satisfy my real aim which is to have the contents of the cell to change to 1/1/2006 or 7/1/2006 or N/A depending on the contents of both cells, D11 and D12. If either D11 or D12 is "Yes" then I want the date to changed as indicated above, but if D11 and D12 are both "No" then I want the cell to state "N/A".

My eternal gratitude. Thank you very much.

Excel Video Tutorials / Excel Dashboards Reports

4. Have VBA, will travel
Join Date
24th December 2004
Location
Nanaimo, Vancouver Island, British Columbia, Canada
Posts
2,464

## Re: Satisfy two if conditions

Hi,

You seem to be have the basic Idea of 'IF...THEN which is:

'If this is true, DO this, else DO this'

Now we move on to truth tables:

AND means both must meet the criteria for the test to pass, otherwise the test fails.

OR means one must match criteria for the test to pass, if neither matches then fail.

So we have 1st test:

If (D11="no" _AND_ D12="no") is True (both are NO), Do (echo) "N/A".

1st test failed? Do 2nd test...

If (D11="yes" _OR_ D12 ='yes") check date. Greater than 7?, Do this, else Do this.

And here 'tis with the correct syntax:

VB:
```=If(And(D11="No",D12="No"),"N/A",If(Or(D11="Yes",D12="yes")*AND(MONTH(I5)<7),DATE(YEAR(I5)+1,1,1),DATE(YEAR(I5)+1,7,1)))

```
Cheers,

dr

Excel Video Tutorials / Excel Dashboards Reports

5. Member
Join Date
11th March 2005
Location
Columbia, Maryland
Posts
20

## Re: Satisfy two if conditions

It worked. Thank you so much.

I understand the logic but how do you learn how to set the formula up?

I know how to do the basics in Excel and there is so much that Excel can do that I will never need to learn.

Is there are simple course that someone can take through this Forum or a book that can help me learn simply how to manipulate dates.

Excel Video Tutorials / Excel Dashboards Reports

6. Have VBA, will travel
Join Date
24th December 2004
Location
Nanaimo, Vancouver Island, British Columbia, Canada
Posts
2,464

## Re: Satisfy two if conditions

Hi,

A really grea tplace to start is C.Peasons pages about dates and times at

http://www.cpearson.com/excel/datetime.htm

Try Excel dates and times in a web search too.

Cheers,

dr

Excel Video Tutorials / Excel Dashboards Reports

7. Member
Join Date
11th March 2005
Location
Columbia, Maryland
Posts
20

## Re: Satisfy two if conditions

You are not kidding. It is a great site. Now I have two great Excel sites.

Any ideas about similar sites for Word or Crystal Reports?

I'm getting greedy now. My apologies.

Excel Video Tutorials / Excel Dashboards Reports

8. Have VBA, will travel
Join Date
24th December 2004
Location
Nanaimo, Vancouver Island, British Columbia, Canada
Posts
2,464

## Re: Satisfy two if conditions

Hi, Here's a link to experts-exchange, another board which has ALL apps including Excel, Word, VBA, etc etc!

http://www.experts-exchange.com/Data...ystal_Reports/

Cheers,

dr

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno