Nested IF AND OR function Inexplicable error

Problem solved - BUT I DON'T KNOW WHY!!
I copied the formulae that you said worked for you into a fresh Excel
workbook and it indeed did work.
Then I copied that cell back into my original workbook replacing the
original formula and .... it worked.
Got me stumped! But now I can continue my work and thanks to all who
helped, Bernard, Sandy, Bob, Ron and Martin P.

"MichaelC" wrote:

> Thanks all who responded to this - my problem remains after removing the line
> breaks entirely, and checking the input cells (C44 is a number 1.2042).
> The error message occurs when I hit Enter after typing in the formula: "The
> formula you typed contains an error: For info about fixing.... To get
> assistance ... etc etc"
> The part of the formula highlighted by the error message is the second
> "GreenAbove" after J44.
>
> I am using Microsoft Excel 2000.
>
>
>
>
> "Martin P" wrote:
>
> > I copied your formula to a worksheet and it works for me.
> > My guess is that the error is in one of the cells J44, J43,J42 or more
> > likely C44.
> > What is the exact error message you are getting?
> >
> > "MichaelC" wrote:
> >
> > > I have the following formula which I believe does not breach any rules, yet
> > > it returns an error message. Can anyone see where my error is? I would much
> > > appreciate any help, and thank you in advance.
> > > I don't believe I am breaching the max 7 arguments rule.
> > >
> > > =if(OR
> > > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > > C44,"")

I used
=IF(OR(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),C44,"x")
and the cell correctly displayed an x. I am using XL 2003
What error are you getting? You do know the first 2 ANDs are the same?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Thanks all who responded to this - my problem remains after removing the line
breaks entirely, and checking the input cells (C44 is a number 1.2042).
The error message occurs when I hit Enter after typing in the formula: "The
formula you typed contains an error: For info about fixing.... To get
assistance ... etc etc"
The part of the formula highlighted by the error message is the second
"GreenAbove" after J44.

I am using Microsoft Excel 2000.




"Martin P" wrote:

> I copied your formula to a worksheet and it works for me.
> My guess is that the error is in one of the cells J44, J43,J42 or more
> likely C44.
> What is the exact error message you are getting?
>
> "MichaelC" wrote:
>
> > I have the following formula which I believe does not breach any rules, yet
> > it returns an error message. Can anyone see where my error is? I would much
> > appreciate any help, and thank you in advance.
> > I don't believe I am breaching the max 7 arguments rule.
> >
> > =if(OR
> > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > C44,"")

I copied your formula to a worksheet and it works for me.
My guess is that the error is in one of the cells J44, J43,J42 or more
likely C44.
What is the exact error message you are getting?

"MichaelC" wrote:

> I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Excel doesn't like line breaks before the parentheses -- try:
=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",
J41="RedBelow")),C44,"")
"MichaelC" <[email protected]> wrote in message
news:[email protected]...
> I have the following formula which I believe does not breach any rules,
yet
> it returns an error message. Can anyone see where my error is? I would
much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

On Mon, 22 Aug 2005 12:35:01 -0700, "MichaelC"
<[email protected]> wrote:

>I have the following formula which I believe does not breach any rules, yet
>it returns an error message. Can anyone see where my error is? I would much
>appreciate any help, and thank you in advance.
>I don't believe I am breaching the max 7 arguments rule.
>
>=if(OR
>(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
>AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
>AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
>C44,"")

After I removed all the line breaks, there was no error message.

=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
C44,"")

If you enter line breaks in the formula, I have found it necessary to put them
just AFTER a "(" rather than before it. See the difference in our first lines
above.


--ron

Your formula as written works for me in XL97

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

I used
=IF(OR(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),C44,"x")
and the cell correctly displayed an x. I am using XL 2003
What error are you getting? You do know the first 2 ANDs are the same?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Problem solved - BUT I DON'T KNOW WHY!!
I copied the formulae that you said worked for you into a fresh Excel
workbook and it indeed did work.
Then I copied that cell back into my original workbook replacing the
original formula and .... it worked.
Got me stumped! But now I can continue my work and thanks to all who
helped, Bernard, Sandy, Bob, Ron and Martin P.

"MichaelC" wrote:

> Thanks all who responded to this - my problem remains after removing the line
> breaks entirely, and checking the input cells (C44 is a number 1.2042).
> The error message occurs when I hit Enter after typing in the formula: "The
> formula you typed contains an error: For info about fixing.... To get
> assistance ... etc etc"
> The part of the formula highlighted by the error message is the second
> "GreenAbove" after J44.
>
> I am using Microsoft Excel 2000.
>
>
>
>
> "Martin P" wrote:
>
> > I copied your formula to a worksheet and it works for me.
> > My guess is that the error is in one of the cells J44, J43,J42 or more
> > likely C44.
> > What is the exact error message you are getting?
> >
> > "MichaelC" wrote:
> >
> > > I have the following formula which I believe does not breach any rules, yet
> > > it returns an error message. Can anyone see where my error is? I would much
> > > appreciate any help, and thank you in advance.
> > > I don't believe I am breaching the max 7 arguments rule.
> > >
> > > =if(OR
> > > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > > C44,"")

Thanks all who responded to this - my problem remains after removing the line
breaks entirely, and checking the input cells (C44 is a number 1.2042).
The error message occurs when I hit Enter after typing in the formula: "The
formula you typed contains an error: For info about fixing.... To get
assistance ... etc etc"
The part of the formula highlighted by the error message is the second
"GreenAbove" after J44.

I am using Microsoft Excel 2000.




"Martin P" wrote:

> I copied your formula to a worksheet and it works for me.
> My guess is that the error is in one of the cells J44, J43,J42 or more
> likely C44.
> What is the exact error message you are getting?
>
> "MichaelC" wrote:
>
> > I have the following formula which I believe does not breach any rules, yet
> > it returns an error message. Can anyone see where my error is? I would much
> > appreciate any help, and thank you in advance.
> > I don't believe I am breaching the max 7 arguments rule.
> >
> > =if(OR
> > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > C44,"")

I copied your formula to a worksheet and it works for me.
My guess is that the error is in one of the cells J44, J43,J42 or more
likely C44.
What is the exact error message you are getting?

"MichaelC" wrote:

> I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

On Mon, 22 Aug 2005 12:35:01 -0700, "MichaelC"
<[email protected]> wrote:

>I have the following formula which I believe does not breach any rules, yet
>it returns an error message. Can anyone see where my error is? I would much
>appreciate any help, and thank you in advance.
>I don't believe I am breaching the max 7 arguments rule.
>
>=if(OR
>(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
>AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
>AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
>C44,"")

After I removed all the line breaks, there was no error message.

=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
C44,"")

If you enter line breaks in the formula, I have found it necessary to put them
just AFTER a "(" rather than before it. See the difference in our first lines
above.


--ron

Your formula as written works for me in XL97

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Excel doesn't like line breaks before the parentheses -- try:
=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",
J41="RedBelow")),C44,"")
"MichaelC" <[email protected]> wrote in message
news:[email protected]...
> I have the following formula which I believe does not breach any rules,
yet
> it returns an error message. Can anyone see where my error is? I would
much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Problem solved - BUT I DON'T KNOW WHY!!
I copied the formulae that you said worked for you into a fresh Excel
workbook and it indeed did work.
Then I copied that cell back into my original workbook replacing the
original formula and .... it worked.
Got me stumped! But now I can continue my work and thanks to all who
helped, Bernard, Sandy, Bob, Ron and Martin P.

"MichaelC" wrote:

> Thanks all who responded to this - my problem remains after removing the line
> breaks entirely, and checking the input cells (C44 is a number 1.2042).
> The error message occurs when I hit Enter after typing in the formula: "The
> formula you typed contains an error: For info about fixing.... To get
> assistance ... etc etc"
> The part of the formula highlighted by the error message is the second
> "GreenAbove" after J44.
>
> I am using Microsoft Excel 2000.
>
>
>
>
> "Martin P" wrote:
>
> > I copied your formula to a worksheet and it works for me.
> > My guess is that the error is in one of the cells J44, J43,J42 or more
> > likely C44.
> > What is the exact error message you are getting?
> >
> > "MichaelC" wrote:
> >
> > > I have the following formula which I believe does not breach any rules, yet
> > > it returns an error message. Can anyone see where my error is? I would much
> > > appreciate any help, and thank you in advance.
> > > I don't believe I am breaching the max 7 arguments rule.
> > >
> > > =if(OR
> > > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > > C44,"")

Thanks all who responded to this - my problem remains after removing the line
breaks entirely, and checking the input cells (C44 is a number 1.2042).
The error message occurs when I hit Enter after typing in the formula: "The
formula you typed contains an error: For info about fixing.... To get
assistance ... etc etc"
The part of the formula highlighted by the error message is the second
"GreenAbove" after J44.

I am using Microsoft Excel 2000.




"Martin P" wrote:

> I copied your formula to a worksheet and it works for me.
> My guess is that the error is in one of the cells J44, J43,J42 or more
> likely C44.
> What is the exact error message you are getting?
>
> "MichaelC" wrote:
>
> > I have the following formula which I believe does not breach any rules, yet
> > it returns an error message. Can anyone see where my error is? I would much
> > appreciate any help, and thank you in advance.
> > I don't believe I am breaching the max 7 arguments rule.
> >
> > =if(OR
> > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > C44,"")

I copied your formula to a worksheet and it works for me.
My guess is that the error is in one of the cells J44, J43,J42 or more
likely C44.
What is the exact error message you are getting?

"MichaelC" wrote:

> I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

On Mon, 22 Aug 2005 12:35:01 -0700, "MichaelC"
<[email protected]> wrote:

>I have the following formula which I believe does not breach any rules, yet
>it returns an error message. Can anyone see where my error is? I would much
>appreciate any help, and thank you in advance.
>I don't believe I am breaching the max 7 arguments rule.
>
>=if(OR
>(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
>AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
>AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
>C44,"")

After I removed all the line breaks, there was no error message.

=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
C44,"")

If you enter line breaks in the formula, I have found it necessary to put them
just AFTER a "(" rather than before it. See the difference in our first lines
above.


--ron

Your formula as written works for me in XL97

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Excel doesn't like line breaks before the parentheses -- try:
=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",
J41="RedBelow")),C44,"")
"MichaelC" <[email protected]> wrote in message
news:[email protected]...
> I have the following formula which I believe does not breach any rules,
yet
> it returns an error message. Can anyone see where my error is? I would
much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

I used
=IF(OR(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),C44,"x")
and the cell correctly displayed an x. I am using XL 2003
What error are you getting? You do know the first 2 ANDs are the same?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

I used
=IF(OR(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),C44,"x")
and the cell correctly displayed an x. I am using XL 2003
What error are you getting? You do know the first 2 ANDs are the same?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Thanks all who responded to this - my problem remains after removing the line
breaks entirely, and checking the input cells (C44 is a number 1.2042).
The error message occurs when I hit Enter after typing in the formula: "The
formula you typed contains an error: For info about fixing.... To get
assistance ... etc etc"
The part of the formula highlighted by the error message is the second
"GreenAbove" after J44.

I am using Microsoft Excel 2000.




"Martin P" wrote:

> I copied your formula to a worksheet and it works for me.
> My guess is that the error is in one of the cells J44, J43,J42 or more
> likely C44.
> What is the exact error message you are getting?
>
> "MichaelC" wrote:
>
> > I have the following formula which I believe does not breach any rules, yet
> > it returns an error message. Can anyone see where my error is? I would much
> > appreciate any help, and thank you in advance.
> > I don't believe I am breaching the max 7 arguments rule.
> >
> > =if(OR
> > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > C44,"")

Problem solved - BUT I DON'T KNOW WHY!!
I copied the formulae that you said worked for you into a fresh Excel
workbook and it indeed did work.
Then I copied that cell back into my original workbook replacing the
original formula and .... it worked.
Got me stumped! But now I can continue my work and thanks to all who
helped, Bernard, Sandy, Bob, Ron and Martin P.

"MichaelC" wrote:

> Thanks all who responded to this - my problem remains after removing the line
> breaks entirely, and checking the input cells (C44 is a number 1.2042).
> The error message occurs when I hit Enter after typing in the formula: "The
> formula you typed contains an error: For info about fixing.... To get
> assistance ... etc etc"
> The part of the formula highlighted by the error message is the second
> "GreenAbove" after J44.
>
> I am using Microsoft Excel 2000.
>
>
>
>
> "Martin P" wrote:
>
> > I copied your formula to a worksheet and it works for me.
> > My guess is that the error is in one of the cells J44, J43,J42 or more
> > likely C44.
> > What is the exact error message you are getting?
> >
> > "MichaelC" wrote:
> >
> > > I have the following formula which I believe does not breach any rules, yet
> > > it returns an error message. Can anyone see where my error is? I would much
> > > appreciate any help, and thank you in advance.
> > > I don't believe I am breaching the max 7 arguments rule.
> > >
> > > =if(OR
> > > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > > C44,"")

I copied your formula to a worksheet and it works for me.
My guess is that the error is in one of the cells J44, J43,J42 or more
likely C44.
What is the exact error message you are getting?

"MichaelC" wrote:

> I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

On Mon, 22 Aug 2005 12:35:01 -0700, "MichaelC"
<[email protected]> wrote:

>I have the following formula which I believe does not breach any rules, yet
>it returns an error message. Can anyone see where my error is? I would much
>appreciate any help, and thank you in advance.
>I don't believe I am breaching the max 7 arguments rule.
>
>=if(OR
>(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
>AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
>AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
>C44,"")

After I removed all the line breaks, there was no error message.

=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
C44,"")

If you enter line breaks in the formula, I have found it necessary to put them
just AFTER a "(" rather than before it. See the difference in our first lines
above.


--ron

Your formula as written works for me in XL97

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Excel doesn't like line breaks before the parentheses -- try:
=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",
J41="RedBelow")),C44,"")
"MichaelC" <[email protected]> wrote in message
news:[email protected]...
> I have the following formula which I believe does not breach any rules,
yet
> it returns an error message. Can anyone see where my error is? I would
much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

I used
=IF(OR(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),C44,"x")
and the cell correctly displayed an x. I am using XL 2003
What error are you getting? You do know the first 2 ANDs are the same?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Problem solved - BUT I DON'T KNOW WHY!!
I copied the formulae that you said worked for you into a fresh Excel
workbook and it indeed did work.
Then I copied that cell back into my original workbook replacing the
original formula and .... it worked.
Got me stumped! But now I can continue my work and thanks to all who
helped, Bernard, Sandy, Bob, Ron and Martin P.

"MichaelC" wrote:

> Thanks all who responded to this - my problem remains after removing the line
> breaks entirely, and checking the input cells (C44 is a number 1.2042).
> The error message occurs when I hit Enter after typing in the formula: "The
> formula you typed contains an error: For info about fixing.... To get
> assistance ... etc etc"
> The part of the formula highlighted by the error message is the second
> "GreenAbove" after J44.
>
> I am using Microsoft Excel 2000.
>
>
>
>
> "Martin P" wrote:
>
> > I copied your formula to a worksheet and it works for me.
> > My guess is that the error is in one of the cells J44, J43,J42 or more
> > likely C44.
> > What is the exact error message you are getting?
> >
> > "MichaelC" wrote:
> >
> > > I have the following formula which I believe does not breach any rules, yet
> > > it returns an error message. Can anyone see where my error is? I would much
> > > appreciate any help, and thank you in advance.
> > > I don't believe I am breaching the max 7 arguments rule.
> > >
> > > =if(OR
> > > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > > C44,"")

Your formula as written works for me in XL97

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Thanks all who responded to this - my problem remains after removing the line
breaks entirely, and checking the input cells (C44 is a number 1.2042).
The error message occurs when I hit Enter after typing in the formula: "The
formula you typed contains an error: For info about fixing.... To get
assistance ... etc etc"
The part of the formula highlighted by the error message is the second
"GreenAbove" after J44.

I am using Microsoft Excel 2000.




"Martin P" wrote:

> I copied your formula to a worksheet and it works for me.
> My guess is that the error is in one of the cells J44, J43,J42 or more
> likely C44.
> What is the exact error message you are getting?
>
> "MichaelC" wrote:
>
> > I have the following formula which I believe does not breach any rules, yet
> > it returns an error message. Can anyone see where my error is? I would much
> > appreciate any help, and thank you in advance.
> > I don't believe I am breaching the max 7 arguments rule.
> >
> > =if(OR
> > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > C44,"")

I copied your formula to a worksheet and it works for me.
My guess is that the error is in one of the cells J44, J43,J42 or more
likely C44.
What is the exact error message you are getting?

"MichaelC" wrote:

> I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Excel doesn't like line breaks before the parentheses -- try:
=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",
J41="RedBelow")),C44,"")
"MichaelC" <[email protected]> wrote in message
news:[email protected]...
> I have the following formula which I believe does not breach any rules,
yet
> it returns an error message. Can anyone see where my error is? I would
much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

On Mon, 22 Aug 2005 12:35:01 -0700, "MichaelC"
<[email protected]> wrote:

>I have the following formula which I believe does not breach any rules, yet
>it returns an error message. Can anyone see where my error is? I would much
>appreciate any help, and thank you in advance.
>I don't believe I am breaching the max 7 arguments rule.
>
>=if(OR
>(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
>AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
>AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
>C44,"")

After I removed all the line breaks, there was no error message.

=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
C44,"")

If you enter line breaks in the formula, I have found it necessary to put them
just AFTER a "(" rather than before it. See the difference in our first lines
above.


--ron

I used
=IF(OR(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),C44,"x")
and the cell correctly displayed an x. I am using XL 2003
What error are you getting? You do know the first 2 ANDs are the same?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Thanks all who responded to this - my problem remains after removing the line
breaks entirely, and checking the input cells (C44 is a number 1.2042).
The error message occurs when I hit Enter after typing in the formula: "The
formula you typed contains an error: For info about fixing.... To get
assistance ... etc etc"
The part of the formula highlighted by the error message is the second
"GreenAbove" after J44.

I am using Microsoft Excel 2000.




"Martin P" wrote:

> I copied your formula to a worksheet and it works for me.
> My guess is that the error is in one of the cells J44, J43,J42 or more
> likely C44.
> What is the exact error message you are getting?
>
> "MichaelC" wrote:
>
> > I have the following formula which I believe does not breach any rules, yet
> > it returns an error message. Can anyone see where my error is? I would much
> > appreciate any help, and thank you in advance.
> > I don't believe I am breaching the max 7 arguments rule.
> >
> > =if(OR
> > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > C44,"")

I have the following formula which I believe does not breach any rules, yet
it returns an error message. Can anyone see where my error is? I would much
appreciate any help, and thank you in advance.
I don't believe I am breaching the max 7 arguments rule.

=if(OR
(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
C44,"")

Problem solved - BUT I DON'T KNOW WHY!!
I copied the formulae that you said worked for you into a fresh Excel
workbook and it indeed did work.
Then I copied that cell back into my original workbook replacing the
original formula and .... it worked.
Got me stumped! But now I can continue my work and thanks to all who
helped, Bernard, Sandy, Bob, Ron and Martin P.

"MichaelC" wrote:

> Thanks all who responded to this - my problem remains after removing the line
> breaks entirely, and checking the input cells (C44 is a number 1.2042).
> The error message occurs when I hit Enter after typing in the formula: "The
> formula you typed contains an error: For info about fixing.... To get
> assistance ... etc etc"
> The part of the formula highlighted by the error message is the second
> "GreenAbove" after J44.
>
> I am using Microsoft Excel 2000.
>
>
>
>
> "Martin P" wrote:
>
> > I copied your formula to a worksheet and it works for me.
> > My guess is that the error is in one of the cells J44, J43,J42 or more
> > likely C44.
> > What is the exact error message you are getting?
> >
> > "MichaelC" wrote:
> >
> > > I have the following formula which I believe does not breach any rules, yet
> > > it returns an error message. Can anyone see where my error is? I would much
> > > appreciate any help, and thank you in advance.
> > > I don't believe I am breaching the max 7 arguments rule.
> > >
> > > =if(OR
> > > (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> > > AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> > > AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> > > C44,"")

I copied your formula to a worksheet and it works for me.
My guess is that the error is in one of the cells J44, J43,J42 or more
likely C44.
What is the exact error message you are getting?

"MichaelC" wrote:

> I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

On Mon, 22 Aug 2005 12:35:01 -0700, "MichaelC"
<[email protected]> wrote:

>I have the following formula which I believe does not breach any rules, yet
>it returns an error message. Can anyone see where my error is? I would much
>appreciate any help, and thank you in advance.
>I don't believe I am breaching the max 7 arguments rule.
>
>=if(OR
>(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
>AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
>AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
>C44,"")

After I removed all the line breaks, there was no error message.

=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
C44,"")

If you enter line breaks in the formula, I have found it necessary to put them
just AFTER a "(" rather than before it. See the difference in our first lines
above.


--ron

Excel doesn't like line breaks before the parentheses -- try:
=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",
J41="RedBelow")),C44,"")
"MichaelC" <[email protected]> wrote in message
news:[email protected]...
> I have the following formula which I believe does not breach any rules,
yet
> it returns an error message. Can anyone see where my error is? I would
much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")

Your formula as written works for me in XL97

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula which I believe does not breach any rules, yet
> it returns an error message. Can anyone see where my error is? I would
> much
> appreciate any help, and thank you in advance.
> I don't believe I am breaching the max 7 arguments rule.
>
> =if(OR
> (AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelow"),
> AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelow"),
> AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBelow",J41="RedBelow")),
> C44,"")