Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MichaelC
 
Posts: n/a
Default Nested IF AND OR function Inexplicable error

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="RedBel ow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",J41="RedBelow")),
C44,"")
  #2   Report Post  
Bernard Liengme
 
Posts: n/a
Default

I used
=IF(OR(AND(J44="GreenAbove",J43="GreenAbove",J42=" RedBelow"),AND(J44="GreenAbove",J43="GreenBelow",J 42="RedBelow"),AND(J44="GreenBelow",J43="GreenBelo w",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" wrote in message
...
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="RedBel ow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",J41="RedBelow")),
C44,"")



  #4   Report Post  
Bob Umlas
 
Posts: n/a
Default

Excel doesn't like line breaks before the parentheses -- try:
=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelo w"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",
J41="RedBelow")),C44,"")
"MichaelC" wrote in message
...
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="RedBel ow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",J41="RedBelow")),
C44,"")



  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Mon, 22 Aug 2005 12:35:01 -0700, "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="RedBe low"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBel ow"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenB elow",J41="RedBelow")),
C44,"")


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

=IF(OR(
AND(J44="GreenAbove",J43="GreenAbove",J42="RedBelo w"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",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


  #6   Report Post  
Martin P
 
Posts: n/a
Default

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="RedBel ow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",J41="RedBelow")),
C44,"")

  #7   Report Post  
MichaelC
 
Posts: n/a
Default

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="RedBel ow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",J41="RedBelow")),
C44,"")

  #8   Report Post  
MichaelC
 
Posts: n/a
Default

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="RedBel ow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",J41="RedBelow")),
C44,"")

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error Handling #N/A with AVERAGE Function - Average of values in Row Sam via OfficeKB.com Excel Worksheet Functions 13 July 31st 05 03:59 PM
undefined function error when creating xls pivot from mdb qry andrew Excel Worksheet Functions 0 July 29th 05 07:26 PM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
Lookup Function Error Jacinthe Excel Worksheet Functions 2 March 10th 05 08:37 AM


All times are GMT +1. The time now is 03:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"