How to calculate these data? Cannot work with nested IF.

[areriff]

I have these data which all contain boolean value.
All cells from AR3 to BC3 is boolean.
Every line works independently. But I cannot seems to get it to work if nested together.
It gives error of too many argument.

IF(AND(AR3,AS3,AT3,AU3,AV3,AW3,AX3,AY3,AZ3,BA3,BB3,BC3),"Construct 12","Construct 11")
IF(AND(AR3,AS3,AT3,AU3,AV3,AW3,AX3,AY3,AZ3,BA3,BB3),"Construct 11","Construct 10")
IF(AND(AR3,AS3,AT3,AU3,AV3,AW3,AX3,AY3,AZ3,BA3),"Construct 10","Construct 9")
IF(AND(AR3,AS3,AT3,AU3,AV3,AW3,AX3,AY3,AZ3),"Construct 9","Construct 8")
IF(AND(AR3,AS3,AT3,AU3,AV3,AW3,AX3,AY3),"Construct 8","Construct 7")
IF(AND(AR3,AS3,AT3,AU3,AV3,AW3,AX3),"Construct 7","Construct 6")
IF(AND(AR3,AS3,AT3,AU3,AV3,AW3),"Construct 6","Construct 5")
IF(AND(AR3,AS3,AT3,AU3,AV3),"Construct 5","Construct 4")
IF(AND(AR3,AS3,AT3,AU3),"Construct 4","Construct 3")
IF(AND(AR3,AS3,AT3),"Construct 3","Construct 2")
IF(AND(AR3,AS3),"Construct 2","Construct 1")
IF(AND(AR3),"Construct 1","Construct 0")

How to go around and make it work?

 

[Rick Rothstein]

Does this formula return what you are looking for?

="Construct "&MATCH(FALSE,AR3:BC3,0)-1

 

[areriff]

Turns out i've not been careful of my syntaxes, it was missing a comma and parentheses, my nested if works.

However, thanks Rick, now I've learn something new about MATCH which i've never see before.

 

[Rick Rothstein]

Turns out i've not been careful of my syntaxes, it was missing a comma and parentheses, my nested if works.

However, thanks Rick, now I've learn something new about MATCH which i've never see before.

Just so I am clear on this... did my formula actually do what you wanted or not?

 

[areriff]

Just so I am clear on this... did my formula actually do what you wanted or not?

No, as it is not the purpose and my intention.

each of those cells have boolean value from the actual data and I want it to evaluate and return a specific string.
In the formula above, the string that I want to display is "Construct 1-12".
This is simplified version of that
=IF(AND(A1,A2,A3,A4),"Construct 12",IF(AND(A1,A2,A3),"Construct 11",IF(AND(A1,A2),"Construct 10",IF(A1=TRUE(),"Construct 9","Construct 8")

I've read somewhere that nested IF is limited to only 7 level. But it works here. Maybe the limitation has been lifted somehow.

 

[Rick Rothstein]

No, as it is not the purpose and my intention.

That is probably because you did not tell us what your purpose or intention was in your original message.

each of those cells have boolean value from the actual data and I want it to evaluate and return a specific string.
In the formula above, the string that I want to display is "Construct 1-12".
This is simplified version of that
=IF(AND(A1,A2,A3,A4),"Construct 12",IF(AND(A1,A2,A3),"Construct 11",IF(AND(A1,A2),"Construct 10",IF(A1=TRUE(),"Construct 9","Construct 8")

See, even here your intention is not clear. You say you wanted "Construct 1-12", but then your example formula does not show the "1-" part leaving us to wonder exactly what you do want. I also note that in your example, you specify cells A1, A2, A3, etc., which is a range of cells down a single column, but in your original post you said "All cells from AR3 to BC3 is Boolean" which is a range of cells across a single row. So, again, it is unclear what you are looking for. Assuming your range in Message #1 is correct, is the only thing wrong with my formula's output that the "1-" is missing (when the number of consecutive TRUEs is more than 1)? If so, and assuming AR3:BC3 always have either TRUE or FALSE in them (that is, none are blank), then try this formula...

="Construct "&IF(AT3=TRUE,"-1 ","")&MATCH(FALSE,AR3:BC3,0)-1