Can someone give me a hand with this formula please?

[Howie]

I have got something like:
=IF(OR(AND(J3>B3,J3<C3),(AND(J3>E3,J3<F3)),(AND(J3>H3,J3<I3)),TRUE,FALSE))

In words, I am trying to say:

IF the value in X is greater than a, AND less than b, OR greater
than c AND less than d, OR greater than e AND less than f, then
FALSE, otherwise TRUE.

I really would appreciate any help here.

TIA.

H.

 

[Max]

One interp .. Maybe in say, K3:

=IF(OR(AND(J3>B3,J3<C3),AND(J3>E3,J3<F3),AND(J3>H3,J3<I3)),FALSE,TRUE)

 

[Niek Otten]

=IF(OR(AND(J3>B3,J3<C3),AND(J3>E3,J3<F3),AND(J3>H3,J3<I3)),TRUE,FALSE)

 

[Bob Phillips]

You should check your post on OzGrid I explained it to you there!

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Howie]

On Tue, 8 Nov 2005 10:28:03 +0100, "Niek Otten"

|=IF(OR(AND(J3>B3,J3<C3),AND(J3>E3,J3<F3),AND(J3>H3,J3<I3)),TRUE,FALSE)

Brilliant! Thank you. That worked really well.

At the risk of being a pain, do you know if there's a way to
compare if there is an overlap of a time period in two different
cells?
What I mean is, I am making a timesheet and I want to flag up an
error if the times in;
A1(start time), and A2 (end time)
conflict with similar cells elsewhere. Such as:
B1(start time) and B2(end time)

Of course, it's the time BETWEEN the two cells in each set of
times which must not conflict.

??

 

[Howie]

On Tue, 8 Nov 2005 11:21:25 -0000, "Bob Phillips"

|You should check your post on OzGrid I explained it to you there!

Thanks Bob, I did. And I've posted a thank you. I didn't get it
to work at first, (my own stupidity), and I didn't realise you
would be on both forums :-\
It works now of course. But I admit to hedging my bets in my
frustration! Sorry if it seemed like overkill!

I don't suppose you can help with my Q on the time conflict
formula can you?

Regards,

H.

 

[Max]

IF the value in X is greater than a, AND less than b, OR greater

than c AND less than d, OR greater than e AND less than f, then
FALSE, otherwise TRUE.

Howie,
From the above lines in your original post, really thought you wanted the
"FALSE" indicated as the Value_IF_True, and "TRUE" as the value otherwise.
That's why I swapped it around. I'm confused. Could you re-clarify your
original intent?

 

[Max]

Something tells me my request for clarification from the OP isn't going to
receive any response ..

 

[Howie]

|Something tells me my request for clarification from the OP isn't going to
|receive any response ..
|--
|Rgds
|Max
|xl 97
|---
|Singapore, GMT+8
|xdemechanik
|http://savefile.com/projects/236895

Don't be like that!
Actually, it was true/false order. But it was the rest of the
formula which was confusing me!

All sorted now; with everyone's help. Thanks very much again.

H.

 

[Max]

Actually, it was true/false order. But it was the rest of the
formula which was confusing me!

Thanks for clarifying. Think I crossed the finishing post a little ahead,
but I was misled into running backwards. It's indeed a fine line between
brilliance and obscurity <g>

Here's some thoughts on your question:

if there's a way to compare if there is an overlap of a time
period in two different cells? What I mean is, I am
making a timesheet and I want to flag up an error if the times in;
A1(start time), and A2 (end time)
conflict with similar cells elsewhere. Such as:
B1(start time) and B2(end time)

Of course, it's the time BETWEEN the two cells in each set of
times which must not conflict.

Assuming times in A1:A2 are to be checked against those in B1:B2

Try in say, A3:
=IF(OR(AND(A1>B1,A1<=B2),AND(A2>B1,A2<=B2)),"Conflict","")

 

[Howie]

<snip>
|
|Assuming times in A1:A2 are to be checked against those in B1:B2
|
|Try in say, A3:
|=IF(OR(AND(A1>B1,A1<=B2),AND(A2>B1,A2<=B2)),"Conflict","")

Max. that does it. Of course!

thanks again for your help.

Regards,

H.

 

[Max]

You're welcome, Howie !
Thanks for the feedback