If statement with 4 conditions.

[sdoppke]

Hi everyone, I am hoping to get help on what seems to be a complicated IF statement (to ME )

In words it looks like this:
If A1(6:00AM) is <than C1(11:00AM) then = "O" OR if A2(9:00PM) is >than C2(11:00PM) then = "C" OR If A1 and A2 are in between C1 and C2 then "M" or is none of these then "" (blank).

thank everyone in advance, hope that was clear as mud

sd

 

[kpark91]

That was as clear as mud mixed with oil.

this threw me off
If A1(6:00AM) is <THAN is A2(9:00PM) if OR then="O" C1(11:00AM)>than C2(11:00PM) then

is it greater than or less than? Lol.

and what's with the OR?

 

[sdoppke]

That was as clear as mud mixed with oil.

this threw me off
If A1(6:00AM) is <THAN is A2(9:00PM) if OR then="O" C1(11:00AM)>than C2(11:00PM) then

is it greater than or less than? Lol.

and what's with the OR?

WOW looks like I did something to delete half of my post

Let me try this again:

If A1(6:00AM) is <<THAN is A2(9:00PM) if OR then="O" C1(11:00AM)>than C1(9:00AM) then = "O" OR IF A2>than C2(11:00PM) then = "C" OR If A1 and A2 are in between C1 and C2 then "M" or IF is none of these then "" (blank).


Thanks for catching that.

sd

 

[Joe4]

WOW looks like I did something to delete half of my post

Your greater than/less than signs are probably being treated as HTML code. This comes up quite a bit (see here: http://www.mrexcel.com/forum/showthread.php?t=570141).

 

[Joe4]

If A1(6:00AM) is <<THAN is A2(9:00PM) if OR then="O" C1(11:00AM)>than C1(9:00AM) then = "O" OR IF A2>than C2(11:00PM) then = "C" OR If A1 and A2 are in between C1 and C2 then "M" or IF is none of these then "" (blank).

Let's break this up to talk about this a bit more:

1. If A1(6:00AM) is < <THAN is A2(9:00PM) if OR then="O" C1(11:00AM)>than C1(9:00AM) then = "O"
Sounds like this component would be:

=IF(A1<C1,"0",...)[ p CODE]<> < C1,"O",...)

2. IF A2 > than C2(11:00PM) then = "C"
Sounds like this component would be:

=IF(A2 > C2,"C",...)

3. If A1 and A2 are in between C1 and C2 then "M"
This could be interpretted a few different ways.
Do you mean A1 between C1 and C2
AND A2 between C1 and C2, i.e.

=IF(AND(A1 > C1,A1 < <C2,A2>C2,A2 > C1,A2 < C2),"M",...)

<C2),"M",...)[ p CODE]<>
What if more than one of these conditions are met? If this some sort of hierarchy (i.e. if condition 1 is met, stop there and don't bother looking at the other conditions)?

 

[sdoppke]

Let's break this up to talk about this a bit more:

1. If A1(6:00AM) is < <THAN is A2(9:00PM) if OR then="O" C1(11:00AM)>than C1(9:00AM) then = "O"
Sounds like this component would be:

=IF(A1<C1,"0",...)[ p CODE]<> < C1,"O",...)

2. IF A2 > than C2(11:00PM) then = "C"
Sounds like this component would be:

=IF(A2 > C2,"C",...)

3. If A1 and A2 are in between C1 and C2 then "M"
This could be interpretted a few different ways.
Do you mean A1 between C1 and C2
AND A2 between C1 and C2, i.e.

=IF(AND(A1 > C1,A1 < <C2,A2>C2,A2 > C1,A2 < C2),"M",...)

<C2),"M",...)[ p CODE]<>
What if more than one of these conditions are met? If this some sort of hierarchy (i.e. if condition 1 is met, stop there and don't bother looking at the other conditions)?

Thanks a ton for the direction. I used your info to come up with this, unfortunaltely give me a "value error"

=OR(IF(D13<$C$10,"O"),(IF(F13>$F$10,"C")),(IF(AND(D13>C10,F13<F10),"M")),(IF(AND(D13="",F13=""),"")))

I will lay out each value
Currently:
D13=blank
F13=blank
C10=11:00AM
F10=6:00PM

I was hoping for a return of blank
I have tried with different calues in D13 and F13 still same error

Does it make a difference that all four cell have formulas in them?

sd

 

[Joe4]

I think what you need is a NESTED IF statement.
Can you try posting your formula again, as it is getting chopped off?
If you place a blank space on both sides of every greater than/less than sign, it should work.

 

[sdoppke]

Can you try posting your formula again, as it is getting chopped off?
If you place a blank space on both sides of every greater than/less than sign, it should work.

Ya i noticed that and tried to eddit it but still chopped lets try this:

=OR(IF(D13 < $C$10,"O"),(IF(F13 > $F$10,"C")),(IF(AND(D13 > C10,F13< F10),"M")),(IF(AND(D13="",F13=""),"")))

 

[Joe4]

You are still not surrounding each greater than/less sign with blank spaces. Look at how I did mine a few posts back.

 

[sdoppke]

You are still not surrounding each greater than/less sign with blank spaces. Look at how I did mine a few posts back.

knew it was user error. i edited the last post.

sd