ExcelBanter - how many inbedded if statements can i have?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - how many inbedded if statements can i have? (https://www.excelbanter.com/excel-worksheet-functions/188276-how-many-inbedded-if-statements-can-i-have.html)

how many inbedded if statements can i have?

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

how many inbedded if statements can i have?

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

how many inbedded if statements can i have?

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

how many inbedded if statements can i have?

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

how many inbedded if statements can i have?

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

Quote:

Originally Posted by Brooke (Post 669166)

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