Can someone give me a hand with this formula please?

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.


--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

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)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Howie" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> --
> Howard Coakley
> e-mail... howard<dot}coakleyatcoakley<dot].codotuk
> Skype ID: howie10 (get skype from www.skype.com)

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

--
Kind regards,

Niek Otten

"Howie" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>
> --
> Howard Coakley
> e-mail... howard<dot}coakleyatcoakley<dot].codotuk
> Skype ID: howie10 (get skype from www.skype.com)

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> --
> Howard Coakley
> e-mail... howard<dot}coakleyatcoakley<dot].codotuk
> Skype ID: howie10 (get skype from www.skype.com)

On Tue, 8 Nov 2005 10:28:03 +0100, "Niek Otten"
<[email protected]> wrote:

|=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.

??
--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

On Tue, 8 Nov 2005 11:21:25 -0000, "Bob Phillips"
<[email protected]> wrote:

|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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

> 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?
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
--

On Wed, 9 Nov 2005 17:29:24 +0800, "Max" <[email protected]>
wrote:

|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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

"Howie" wrote:
> 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","")

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

<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.

You're welcome, Howie !
Thanks for the feedback
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--