Here's my functional formula
=IF(OR(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1),"Need to answer all
statements",IF(AND(O13=2,O14=2,O15=2,O16=2,O17=2,O 18=2),2,IF(OR(O13=3,O14=3,O15=3),"3",IF(AND(O13=2, O14=2,O15=2,(SUM(O16:O18))=7),2,IF(AND(O13=2,O14=2 ,O15=2,(SUM(O16:O18))=8),"Btw
Red & Grn",IF(AND(O13=2,O14=2,O15=2,(SUM(O16:O18))=9),"B tw Red & Grn","not an
ans"))))))

and it return the results I want, but I needed one more condition and it
gives me the "formula contains errors" message. I just want to add the
following that works on it's own....

IF((AND(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1)),1,

so is there a limit to how many true statements you can have?

Thanks

Hi

You can have up to 7 levels of IF's in one formula - without branching them
it leaves you with 8 different options.

The condition you want to add will never occur, because the 1st condition in
your current formula will be fired before.

Generally, possible formulas will be (LE# - logical expression):

=IF(LE1,Resp1,IF(LE2,resp2,IF(LE3,resp3,IF(LE4,res p4,IF(LE5,Resp5,IF(LE6,Resp6,IF(LE7,Resp7,Resp8))) ))))

With branching (BC - branching condition):
=IF(BC,IF(LE1,Resp1,IF(LE2,resp2,IF(LE3,resp3,IF(L E4,resp4,IF(LE5,Resp5,IF(LE6,Resp6,Resp7)))))),IF( LE8,Resp8,IF(LE9,resp9,IF(LE10,resp10,IF(LE11,resp 11,IF(LE12,Resp12,IF(LE13,Resp13,Resp14)))))))

With numeric responses you can avoid 7-level limit using formula like:
=(LE1)*Resp1+(LE2)*Resp2+...



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Brooke" wrote in message
...
Here's my functional formula
=IF(OR(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1),"Need to answer all
statements",IF(AND(O13=2,O14=2,O15=2,O16=2,O17=2,O 18=2),2,IF(OR(O13=3,O14=3,O15=3),"3",IF(AND(O13=2, O14=2,O15=2,(SUM(O16:O18))=7),2,IF(AND(O13=2,O14=2 ,O15=2,(SUM(O16:O18))=8),"Btw
Red & Grn",IF(AND(O13=2,O14=2,O15=2,(SUM(O16:O18))=9),"B tw Red & Grn","not
an
ans"))))))

and it return the results I want, but I needed one more condition and it
gives me the "formula contains errors" message. I just want to add the
following that works on it's own....

IF((AND(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1)),1,

so is there a limit to how many true statements you can have?

Thanks

Will any of the cells in the range O16:O18 ever contain TEXT or will they
*always* contain a number or be *EMPTY* ?

--
Biff
Microsoft Excel MVP


"Brooke" wrote in message
...
Here's my functional formula
=IF(OR(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1),"Need to answer all
statements",IF(AND(O13=2,O14=2,O15=2,O16=2,O17=2,O 18=2),2,IF(OR(O13=3,O14=3,O15=3),"3",IF(AND(O13=2, O14=2,O15=2,(SUM(O16:O18))=7),2,IF(AND(O13=2,O14=2 ,O15=2,(SUM(O16:O18))=8),"Btw
Red & Grn",IF(AND(O13=2,O14=2,O15=2,(SUM(O16:O18))=9),"B tw Red & Grn","not
an
ans"))))))

and it return the results I want, but I needed one more condition and it
gives me the "formula contains errors" message. I just want to add the
following that works on it's own....

IF((AND(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1)),1,

so is there a limit to how many true statements you can have?

Thanks

The condition you want to add will never occur, because the 1st condition
in your current formula will be fired before.

It will occur *if* you make it the first condition. You would want to test
*all* cells for 1 first, then test *any* cells for 1.

=IF(COUNTIF(O13:O18,1)=6,1,IF(COUNTIF(O13:O18,1)," Need to answer all
statements"........


--
Biff
Microsoft Excel MVP


"Arvi Laanemets" wrote in message
...
Hi

You can have up to 7 levels of IF's in one formula - without branching
them it leaves you with 8 different options.

The condition you want to add will never occur, because the 1st condition
in your current formula will be fired before.

Generally, possible formulas will be (LE# - logical expression):

=IF(LE1,Resp1,IF(LE2,resp2,IF(LE3,resp3,IF(LE4,res p4,IF(LE5,Resp5,IF(LE6,Resp6,IF(LE7,Resp7,Resp8))) ))))

With branching (BC - branching condition):
=IF(BC,IF(LE1,Resp1,IF(LE2,resp2,IF(LE3,resp3,IF(L E4,resp4,IF(LE5,Resp5,IF(LE6,Resp6,Resp7)))))),IF( LE8,Resp8,IF(LE9,resp9,IF(LE10,resp10,IF(LE11,resp 11,IF(LE12,Resp12,IF(LE13,Resp13,Resp14)))))))

With numeric responses you can avoid 7-level limit using formula like:
=(LE1)*Resp1+(LE2)*Resp2+...



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Brooke" wrote in message
...
Here's my functional formula
=IF(OR(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1),"Need to answer all
statements",IF(AND(O13=2,O14=2,O15=2,O16=2,O17=2,O 18=2),2,IF(OR(O13=3,O14=3,O15=3),"3",IF(AND(O13=2, O14=2,O15=2,(SUM(O16:O18))=7),2,IF(AND(O13=2,O14=2 ,O15=2,(SUM(O16:O18))=8),"Btw
Red & Grn",IF(AND(O13=2,O14=2,O15=2,(SUM(O16:O18))=9),"B tw Red &
Grn","not an
ans"))))))

and it return the results I want, but I needed one more condition and it
gives me the "formula contains errors" message. I just want to add the
following that works on it's own....

IF((AND(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1)),1,

so is there a limit to how many true statements you can have?

Thanks

Try this *assuming O16:O18 will never contain TEXT* :

=IF(COUNTIF(O13:O18,1)=6,1,IF(COUNTIF(O13:O18,1)," Need to answer all
statements",IF(COUNTIF(O13:O18,2)=6,2,IF(COUNTIF(O 13:O15,3),3,IF(AND(O13=2,O14=2,O15=2,O16+O17+O18=7 ),2,IF(AND(O13=2,O14=2,O15=2,OR(O16+O17+O18={8,9}) ),"Btw
Red & Grn","not an ans"))))))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Will any of the cells in the range O16:O18 ever contain TEXT or will they
*always* contain a number or be *EMPTY* ?

--
Biff
Microsoft Excel MVP


"Brooke" wrote in message
...
Here's my functional formula
=IF(OR(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1),"Need to answer all
statements",IF(AND(O13=2,O14=2,O15=2,O16=2,O17=2,O 18=2),2,IF(OR(O13=3,O14=3,O15=3),"3",IF(AND(O13=2, O14=2,O15=2,(SUM(O16:O18))=7),2,IF(AND(O13=2,O14=2 ,O15=2,(SUM(O16:O18))=8),"Btw
Red & Grn",IF(AND(O13=2,O14=2,O15=2,(SUM(O16:O18))=9),"B tw Red &
Grn","not an
ans"))))))

and it return the results I want, but I needed one more condition and it
gives me the "formula contains errors" message. I just want to add the
following that works on it's own....

IF((AND(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1)),1,

so is there a limit to how many true statements you can have?

Thanks

Hi, As I understand it you can have up to 7 nested If functions I got this to work
IF(AND(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1),1,IF(O R(O13=1,O14=1,O15=1,O16=1,O17=1,O18=1),"Need to answer all statements",IF(AND(O13=2,O14=2,O15=2,O16=2,O17=2,O 18=2),2,IF(OR(O13=3,O14=3,O15=3),"3",IF(AND(O13=2, O14=2,O15=2,O16+O17+O18=7),2,IF(AND(O13=2,O14=2,O1 5=2,O16+O17+O18=8),"Btw
Red & Grn",IF(AND(O13=2,O14=2,O15=2,O16+O17+O18=9),"B tw Red & Grn","not an
ans")))))))
which incorporated your extra "if".
regards Howard