Formula too long

[JeffJ]

I get an error saying "Formula too long."

My formula as follows works fine without any error:

=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="38X12"),35,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="40X12"),36,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="42X12"),37,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="44X12"),38,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="46X12"),39,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="48X12"),40,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="50X12"),41,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="52X12"),42,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="54X12"),43,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="56X12"),44,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="58X12"),45,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="60X12"),46,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="62X12"),47,"")&IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch",E6="64X12"),48,"")

However, if I attach just one more argument to the end of it (i.e.
attach the following argument to the tail end of the above arguments)
I get the error "Formula too long":

IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="6 inch",E6="66X12"),49,"")

I know that, using the regular IF function, you are limited to
arguments.
I found out that if you use the CONCATENATE function, you can go up t
30 arguments, which is nice if you need more than 7 IF functions in
formula.
And in my internet research, I found out that if you use th
concatenation operator ("&"), you are not limited to 30 argument
(which was like a godsend when I found this out).

My guess is, the reason I'm getting the error may have something to d
with having too many AND or OR nested functions (in TOTAL combinatio
for the entire formula)...even though you're supposed to be able t
have up to 30 conditions for the AND function and 30 for the O
function.

I know I can split my formula between 2 or more hidden cells and the
Reference them from my main cell (as a workaround solution), but i
there's an easier way I'd like to know; in any case, I would reall
like to know WHY I'm getting this error. Does anyone know?

Thank you for any reply.
-Jeff
(e-mail address removed)

 

[Frank Kabel]

Hi
try the following:
put the allowed values for E6 and the values to be
returned in a spearate sheet as lookup table. e.g. call
this sheet 'lookup' with col. a + B as follows:
38X12 - 35
40X12 - 36
......


Now use the following formula
=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="6
inch"),VLOOKUP(E6,'lookup'!$A$1:$B$100,2,0))

or if you want to include some error checking:
=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="6
inch"),IF(ISNA(VLOOKUP(E6,'lookup'!
$A$1:$B$100,2,0)),"",VLOOKUP(E6,'lookup'!$A$1:$B$100,2,0))

 

[JeffJ]

Wow, what a fast reply! Thanks, Frank! I was actually just now toyin
with the possible idea of some way to apply VLOOKUP, and you not onl
cemented/confirmed the idea for me, but you were kind enough to lay ou
the exact formula in black and white! I really appreciate your promp
and comprehensive reply! That looks like it will do the trick, and
will begin applying that right now! Thanks again

 

[Frank Kabel]

Hi
thanks for your feedback

 

[JeffJ]

You're welcome. One thing I don't really understand, though (since I'
only familiar with VLOOKUP at a very basic level), is why you use "0
as the Range_lookup in "VLOOKUP(E6,'lookup'!$A$1:$B$100,2,0))."
Microsoft Excel Help only uses TRUE or FALSE as examples fo
Range_lookup. What exactly does the "0" mean/imply, and how does i
relate to being either true or false in that formula (i.e., why a "0
instead of a "1" or instead of using "TRUE" or "FALSE")

 

[Frank Kabel]

Hi
0=FALSE / 1 = TRUE
just shorter to write as most people are lazy

 

[JeffJ]

Ah, I see. Thank you for that explanation.

I used the formula as you suggested, and it works, except that, until
fill in E5, it reads "FALSE." Even if I fill in E2, E3 and E6, i
still reads "FALSE" until I fill in E5. I'm going to have severa
columns of this, which will not always be filled in; so how do I mak
it stay blank instead of reading "FALSE"?

One way, I know, is to use Conditional Formatting, and, if it say
"FALSE" to tell it to make the text the same color as the background.
But do you know of a better way

 

[Frank Kabel]

Hi
try
=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="6
inch"),IF(ISNA(VLOOKUP(E6,'lookup'!
$A$1:$B$100,2,0)),"",VLOOKUP(E6,'lookup'!$A$1:$B$100,2,0),"")

 

[JeffJ]

Frank,

I tried that. Actually, in my case now (because of where I placed th
cells in the second Sheet) it would be:

=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch"),IF(ISNA(VLOOKUP(E6,lookup!$C$3:$D$19,2,0)),"",VLOOKUP(E6,lookup!$C$3:$D$19,2,0)))

It stills gives me FALSE until I choose 1 or 2 from the drop-down lis
in E5 on the first Sheet

 

[JeffJ]

Actually, as I mentioned, I can use Conditional Formatting to make an
FALSE reading invisible, so that's really not a problem.

But now, when I put in the complete formula I need, I'm experiencin
something else. (BTW, thank you for all the help you've already give
me, and for all your patience.)

I actually have 2 lists on Sheet2. As you suggested, I renamed Sheet
to now be called "lookup."

The first list is the one I mentioned before.
(Representing Zones 1 and 2, in 2 columns):
First row: first column: 38X12; next column: 35
Second row: 40X12; 36
Third row: 42X12; 37
etc.
As I mentioned previously, this covers C319.

The second list is similar.
(Representing Zone 3):
38X12....40
40X12....42
42X12....44
etc.
This covers E3:F19.

As you know, E5 (on Sheet1) is a drop-down list with 1, 2 or 3 a
choices, which represent the Zones. 1 and 2 return the same things
only 3 is different.

So now, if I combine the options for the two lists, it becomes:

=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="6
inch"),IF(ISNA(VLOOKUP(E6,'lookup'!
$A$1:$B$100,2,0)),"",VLOOKUP(E6,'lookup'!$A$1:$B$100,2,0),IF(AND(E5=3,E2="...3/12...",E3="
inch"),IF(ISNA(VLOOKUP(E6,'lookup'!
$E$3:$F$19,2,0)),"",VLOOKUP(E6,lookup!$E$3:$F$19,2,0)))))

But now it gives me an error: "You've entered too many arguments fo
this function."

So I take out the ISNA part, and then it stops giving me that error.
Then the formula becomes:

=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="6
inch"),IF(VLOOKUP(E6,lookup!$A$1:$B$100,2,0),"",IF(AND(E5=3,E2="...3/12...",E3="
inch"),IF(VLOOKUP(E6,lookup!$E$3:$F$19,2,0),""))))

But now, no matter what options I choose (whether I leave any or al
cells blank or fill them in) for E2:E6, it gives a FALSE reading fo
everything; i.e., it now won't put in any of the amounts anymore

 

[JeffJ]

Ok, I took out the space between "6" and "inch", and I changed th
"$A$1:$B$100" to the correct reference cells (dumb me!).

But now, if E6 is blank, it results in #N/A.
If E5 is 1 or 2, it results in a blank cell.
Everything else results in FALSE.
I still can't get any amounts

 

[JeffJ]

I found the solution:

=IF(AND(OR(E5=1,E5=2),E2="...3/12...",E3="
inch"),VLOOKUP(E6,lookup!$C$3:$D$19,2,0),IF(AND(E5=3,E2="...3/12...",E3="
inch"),VLOOKUP(E6,lookup!$E$3:$F$19,2,0),""))

It now returns the correct amounts when the E2:E6 cells are filled in
and if any cell in E2:E6 is blank, then it returns blank (not FALS
anymore), which is what I want, because I want either all or none o
E2:E6 to be filled in by the user. If only E6 is blank it return
#N/A, which is fine, because that will merely prompt the user to fil
in E6.

I believe that when I was getting FALSE if E2:E6 was blank, that wa
because ISNA checks the type of value and returns TRUE or FALS
depending on the outcome. And since I was not getting #N/A as a
error, ISNA=FALSE.

Thank you again, Frank, for helping me along with that