Number of characters in a formula

Posted by Pankaja Narayanan on February 01, 2001 8:31 AM

Can someone tell me how I can make a formula that is more than 1024 characters long work?

Posted by Kaiowas on February 01, 2001 8:36 AM

Presumably the formula is that long because it either contains lots of functions or very long strings, either way the only way to make it work would be to shorten the formula by calculating parts of it separately in other cells then referencing the results in your main formula.

Kaiowas

Posted by Mark W. on February 01, 2001 8:56 AM

I'd like to see this formula!

Posted by PankajaNarayanan on February 01, 2001 9:29 AM

Posted by PankajaNarayanan on February 01, 2001 9:30 AM

My formula is like this:

=IF(ISNA(VLOOKUP(0001, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)), 0, VLOOKUP(0001, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)) + IF(ISNA(VLOOKUP(0002, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)), 0, VLOOKUP(0002, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)) + IF(ISNA(VLOOKUP(0003, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)), 0, VLOOKUP(0003, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)) + IF(ISNA(VLOOKUP(0003, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)), 0, VLOOKUP(0004, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)) + IF(ISNA(VLOOKUP(0003, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)), 0, VLOOKUP(0004, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE))

Posted by Pankaja Narayanan on February 01, 2001 9:34 AM

Thanks a lot for your helpful suggestions. My formula is like this:

+ IF(ISNA(VLOOKUP(0003, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE)), 0, VLOOKUP(0004, '[w.xls]W'!$A$5:$X$699, LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2), FALSE))

Posted by Mark W. on February 01, 2001 3:21 PM

Why don't you define the following names:

Table_Array = '[w.xls]W'!$A$5:$X$699
Col_Index_Num = LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2)

...and then change your formula as follows:

IF(ISNA(VLOOKUP(0001, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0001, Table_Array, Col_Index_Num, 0))
+ IF(ISNA(VLOOKUP(0002, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0002, Table_Array, Col_Index_Num, 0))
+ IF(ISNA(VLOOKUP(0003, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0003, Table_Array, Col_Index_Num, 0))
+ IF(ISNA(VLOOKUP(0003, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0004, Table_Array, Col_Index_Num, 0))
+ IF(ISNA(VLOOKUP(0003, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0004, Table_Array, Col_Index_Num, 0))

You may want to take a close look at the 4th
and 5th terms. I believe there are some redundancies.
Also, I'm a bit confused by the 1st argument to your
VLOOKUP() functions. If these are text values they
should be entered with quotes (e.g., "0001").

Posted by Pankaja Narayanan on February 02, 2001 5:31 AM

Mark, Thank you very much for your very useful and excellent suggestions to rewrite the VLOOKUP() functions. Actually, 0001, 0002 etc., refer to "test sites" and are numeric. They should read 0001, 0002, 0003, 0004 and 0005 (combining the data from five sites). Thaks once again, Mark.

Posted by Mark W. on February 02, 2001 6:17 AM

If they are truly numeric Excel will strip off the
leading zeroes when you enter the value into a cell
or include it in a formula.

Posted by Pankaja Narayanan on February 02, 2001 7:16 AM

Mark,
Excel does strip off the leading zeros and that is fine. I defined the names Table_Array and Col_Index_Row as suggested by you. But it does not seem to be working. The end result is 0 when I should be getting 170. Please help. I would like to use your formula in my work. Thanks.

Posted by Mark W. on February 02, 2001 10:04 AM

Let me see your revised formula.

Posted by Pankaja Narayanan on February 02, 2001 12:15 PM

The original data is like this:

Center Volume

0001 24
0003 56
0006 78
0008 95
...
...

9999
I am trying to sum say,(0001, 0006,0516,0818) in another file using Vlookup (ISNA puts a zero if there is no data in the first file).

As suggested, I entered '[w.xls]W'!$A$5:$X$699 in a cell and named (using name-define) it Table_Array
I entered LOOKUP('[w.xls]W'!$G$1, '[w.xls]W'!$1:$1, '[w.xls]W'!$2:$2) in another cell and named it Col_Index_Num.

Then I entered in my receiving file in the column volume for center 0001 I entered the formula
IF(ISNA(VLOOKUP(0001, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0001, Table_Array, Col_Index_Num, 0))
+IF(ISNA(VLOOKUP(0006, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0006, Table_Array, Col_Index_Num, 0))
+IF(ISNA(VLOOKUP(0516, Table_Array, Col_Index_Num, 0)),0,VLOOKUP(0516, Table_Array, Col_Index_Num, 0))
+IF(ISNA(VLOOKUP(0818, Table_Array, Col_Index_Num, 0)), 0, VLOOKUP(0818, Table_Array, Col_Index_Num, 0))

The result is 0 volume which is not correct. It should be a higher number.

Does this help?

Posted by Mark W. on February 02, 2001 2:58 PM

Pankaja, I'm missing one or more pieces of this puzzle.

1. Excel will not store a formula as you've shown it!
How is this formula displayed in the formula bar?

IF(ISNA(VLOOKUP("0001", Table_Array, Col_Index_Num, 0))...

IF(ISNA(VLOOKUP(1, Table_Array, Col_Index_Num, 0))...

2. When you select an empty cell, type an equal sign (=),
click on the cell containing the Center value, 0001,
and press Ctrl+= what do you see? 1 or "0001"

Posted by Pankaja Narayanan on February 03, 2001 6:08 AM

Mark, The center value is formatted as a number and I see 1 in the formula and not "0001". The problem is I see "N/A" as values for Table_Array and Col_Index_Num whereas wnen I use the original formula I see the correct values. Your suggested formula is very convenient to use and I want to make use of that especially when I want to sum about 10 centers. Thanks Mark, I appreciate your help.

Posted by Mark W. on February 03, 2001 10:49 AM

Pankaja, now I see part of your problem! I read your last reply very
quickly because it was late in the day. You entered the cell reference
for the Table_Array into a cell and then named that cell "Table_Array".
That wasn't what I was suggesting. Using the Insert Name Define...
menu command you should have created the name "Table_Array" and then
in the "Refers to" field entered the cell reference for your entire
table. And then do the same for "Col_Index_Num".

Posted by Pankaja Narayanan on February 03, 2001 11:43 AM

Thanks a lot Mark, for your prompt and helpful suggestions. I will try again and hopefully this time it will work and also simplify my work with very complicated data files.

By the way, can a MACRO be written to do the VLOOKUP functions I am using in my analysis? Thanks.

Posted by Mark W. on February 03, 2001 12:29 PM

I suppose so, but I don't believe that's necessary.

Posted by Pankjaja Narayanan on February 04, 2001 7:44 AM

Re: MARK, Your FORMULA worked very well.! THANKS A LOT!