Excel Error Message

D

Danny

I have been receiving this error message with the following formula. How do I
fix it? If I substitute the letter "A" for all of the text in parentheses,
the formula works.

=IF(E12>=120,"Insulin Resistance / Diabetes, Lipidproteinemia, Hepatitis,
Liver Congestion, Acute Pulmonary Infarction, Extensive Pneumonia, Advanced
Cancer, Anemia, Lymphocytosis, Skeletal Muscle Dysfunction, Systemic
Conditions, Collagen Vascular Diseases, Omega-3 Fatty Acid Deficiency,
Hypertriglyceridemia, Alcoholism, Renal Dysfunction, Acute Pancreatitis,
Gout, Oral Contraceptives, Excess Fructose Consumption",IF(E12<120,""))

Thanks
 
J

Jacob Skaria

Hi Danny

In 2003 you are limited to 1024 characters in a formula,
In 2007 the limit is 8192

So you can have your text in another cell and refer that in your formula

'With the text in cell F1
=IF(E12>=120,F1,IF(E12<120,""))


If this post helps click Yes
 
A

AB

Danny,

There surely must be a reason but still looks interesting the 2nd IF in the
formula:
IF(E12<120,"")

I thought that the 1st IF would take care of it as surely if E12 is not
=120, then it's <120 and just "" should be returned.
Click to expand...
Wouldn't this work:
=IF(E12>=120,F1,"")
 
R

Rick Rothstein

Yes, =IF(E12>=120,F1,""). I think Jacob just went along with the OP's
original structure as he addressed the area causing the problem (too much
text) and didn't actually pay attention to the formula itself... this is
quite a common thing which I'm sure all responders have done at one time or
another.
 
A

AB

I belive so.
I would not dare to suggest that Jacob wouldn't have spotted that - there is
so much i can learn from Jacobs posts here.
 
T

T. Valko

Hmmm...

That's strange. The formula length as posted, LEN() = 435, is nowhere near
the formula length limit yet you get the warning.

If you put all that text in a cell and refer to that cell it works fine.
There seems to be a limit on the length of a string as a hardcoded argument.
255 characters works ok, >255 characters then Excel complains

A1 = long string of text

=IF(E12>=120,A1,"")
 
D

Dave Peterson

Try breaking your string into smaller strings.

=IF(E12>=120,"long string1 "&"longstring2 "&"longstring3",if(e12<120,""))

I could break your long string into 2 pieces.

It broke here:
Diseases, "&"Omega-3 Fatty

(but I couldn't make the post look nice because of the way it would wrap.)
 
D

Danny

Thanks, Jacob,
I still don't understand why I get the message, since there are only about
600 characters in the formula including the spaces. I'll try the F1 deal.
Danny
 
Top