Limit 8192 Characters

[L40]

Im using Excel 2007 and I'm trying to save my work and i'm getting this message.
One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid this limitation, save the workbook in the Excal Binary Workbook format.

I have verified all of my formulas and i do not see any errors. I have also tried saving a a binary woork book and i get the same error. This is the formula i'm using.

=IF(SUM('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62)=0,0,SUM('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62)/INDEX(FREQUENCY('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62,0),2))

I have been using this for some time now and for some reason now it is doing this. does anyone know what is going on?

Thanks

 

[T. Valko]

I don't see how your original formula averages anything. Removing the sheet references, it looks like this:

=IF(SUM($N$62)=0, 0, SUM($N$62)/INDEX(FREQUENCY($N$62, 0), 2))

As Jack pointed out, summing a single cell doesn't do anything, so that's equivalent to

=IF($N$62=0, 0, $N$62/INDEX(FREQUENCY($N$62,0),2))

But the Frequency function returns either zero (if N62 is <= 0) or 1 (if N62 > 0).

The OPs formula is doing an "average if cell ref >0" across multiple sheets.

 

[T. Valko]

Im using Excel 2007 and I'm trying to save my work and i'm getting this message.
One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid this limitation, save the workbook in the Excal Binary Workbook format.

I have verified all of my formulas and i do not see any errors. I have also tried saving a a binary woork book and i get the same error. This is the formula i'm using.

=IF(SUM('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62)=0,0,SUM('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62)/INDEX(FREQUENCY('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62,0),2))

I have been using this for some time now and for some reason now it is doing this. does anyone know what is going on?

Thanks

I don't have any suggestions wrt the 8192 character limit. If that formula is your longest formula then you're well within the formula length limit.

Since you're using Excel 2007 you can replace the "IF SUM = 0" portion with IFERROR.

=IFERROR(SUM('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62)/INDEX(FREQUENCY('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62,0),2),0)

Unless of course, you use the "IF SUM = 0" to be compatible with earlier versions of Excel.

If you need version compatibility with an efficient error trap then this version can be used:

=LOOKUP(1E100,CHOOSE({1,2},0,SUM('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62)/INDEX(FREQUENCY('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62,0),2)))

 

[ZVI]

At saving of workbook the full path to another (external in this case) workbook is added to the formula 3 times.
Full path includes drive name, root folder, all subfolders, workbook name, sheet(s) name(s) and cell(s) address(es).
Therefore the common length of formula can exceed the limit.
Close workbook [2011 2nd Shift Daily Tally Record.xlsx] to see the extending of the formula’s length.

For solving try to add name and set its RefersTo property to external workbook ranges.
Then replace in formula the links to another workbook by that name.
Also you can save workbooks in the folder which is closer to the root one and/or use shorter names of workbooks & sheets.