Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default formula cant be entered

I am tying to enter in some "IF" formulas and it is not allowing me to enter
in more then 44. I checked the specifications and limits and I dont believe
I am over the limit. When I try to enter the 45th one I get this message,
"The specified formula cannot be entered because it contains more values,
references, and/or names than are allowed in the current file format." Cant
anyone give me some info on this, or is there a way to change the "current
file format?"
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default formula cant be entered

Are you attempting to have one formula with more than 44 if statements in
it??? Also what version of XL?
--
HTH...

Jim Thomlinson


"matt2123" wrote:

I am tying to enter in some "IF" formulas and it is not allowing me to enter
in more then 44. I checked the specifications and limits and I dont believe
I am over the limit. When I try to enter the 45th one I get this message,
"The specified formula cannot be entered because it contains more values,
references, and/or names than are allowed in the current file format." Cant
anyone give me some info on this, or is there a way to change the "current
file format?"

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default formula cant be entered

=IF(CAB1!$E$2,CAB1!$A$2,CAB1!F:F)&IF(CAB1!$E$3,CAB 1!$A$3,CAB1!F:F) ---- That
is basically what I am foing 44 times. It is 2007


"Jim Thomlinson" wrote:

Are you attempting to have one formula with more than 44 if statements in
it??? Also what version of XL?
--
HTH...

Jim Thomlinson


"matt2123" wrote:

I am tying to enter in some "IF" formulas and it is not allowing me to enter
in more then 44. I checked the specifications and limits and I dont believe
I am over the limit. When I try to enter the 45th one I get this message,
"The specified formula cannot be entered because it contains more values,
references, and/or names than are allowed in the current file format." Cant
anyone give me some info on this, or is there a way to change the "current
file format?"

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default formula cant be entered

Hi Matt,

What do you mean by more than 44? 44 what's? In one cell? Why don't you
show us a little of your formula.

In 2003 and earlier, you are limited to 1,024 characters in a cell formula.
And you are limited to 7 levels deep of nesting. You are also limited to a
maximum of 30 arguments, for example if your formula looked like this
=SUM(IF(...),IF(...)....)

In 2007 these limits are 16,384 bits for formulas, 64 levels of nesting and
255 arguments.

In most cases long IF formulas could be done with other more efficient
techniques.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"matt2123" wrote:

I am tying to enter in some "IF" formulas and it is not allowing me to enter
in more then 44. I checked the specifications and limits and I dont believe
I am over the limit. When I try to enter the 45th one I get this message,
"The specified formula cannot be entered because it contains more values,
references, and/or names than are allowed in the current file format." Cant
anyone give me some info on this, or is there a way to change the "current
file format?"

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default formula cant be entered

Shane,

=IF(CAB1!$E$20,CAB1!$A$2,CAB1!F:F)&IF(CAB1!$E$30 ,CAB1!$A$3,CAB1!F:F)&IF(CAB1!$E$40,CAB1!$A$4,CAB1 !F:F).....


I am trying to pull the description of an item from another tab, if column E
has an amount greater then 0. It stops at the 44th entry, but I didnt think I
was over the limits.

Thanks Shane

"ShaneDevenshire" wrote:

Hi Matt,

What do you mean by more than 44? 44 what's? In one cell? Why don't you
show us a little of your formula.

In 2003 and earlier, you are limited to 1,024 characters in a cell formula.
And you are limited to 7 levels deep of nesting. You are also limited to a
maximum of 30 arguments, for example if your formula looked like this
=SUM(IF(...),IF(...)....)

In 2007 these limits are 16,384 bits for formulas, 64 levels of nesting and
255 arguments.

In most cases long IF formulas could be done with other more efficient
techniques.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"matt2123" wrote:

I am tying to enter in some "IF" formulas and it is not allowing me to enter
in more then 44. I checked the specifications and limits and I dont believe
I am over the limit. When I try to enter the 45th one I get this message,
"The specified formula cannot be entered because it contains more values,
references, and/or names than are allowed in the current file format." Cant
anyone give me some info on this, or is there a way to change the "current
file format?"

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
manual numbers entered in formula aprilm Excel Discussion (Misc queries) 0 July 19th 07 05:36 PM
Formula to see if a cell has an entered value bellz Excel Worksheet Functions 3 January 13th 06 07:23 AM
hourglass won't go away after formula was entered Dawn New Users to Excel 9 November 1st 05 11:36 PM
Formula does not show as I entered it. ladytar Excel Worksheet Functions 2 October 4th 05 12:29 AM
Formula entered not working Thrava Excel Discussion (Misc queries) 5 March 6th 05 10:18 PM


All times are GMT +1. The time now is 05:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"