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

 

[JackDanIce]

I don't understand why you're using SUM in your original formula. I have taken it out as I don't think it's needed. Check if this still works and then try saving:

=IF('[2011 2nd Shift Daily Tally Record.xlsx]4-09-11:4-03-11'!$N$62=0,0,'[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))

 

[L40]

If i do not use SUM i get a REF# error.

 

[JackDanIce]

Weird, I've just tried a variation of the adapted version of your formula that I gave and it works ok...

 

[shg]

How about instead just =MAX(0, $N$62)

 

[L40]

WHere should the =Max(0, $n$62) go in the formula. I have never used =Max so i'm unfamiliar with it.

 

[shg]

Replace the entire formula with that.

 

[L40]

When I replaced the formula it changed everything to 0. The work book that contains this formula is pulling information from 3 separate work books. Not sure if that’s why it’s not working.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[L40]

Ok i think figured a portion of it out. I used the =max(0, $n$62) like this but i gotr a 0. so i tried it like this =MAX(0, '[2011 2nd Shift Daily Tally Record.xlsx]2-26-11:2-20-11'!$N$62) and i did get a value in return. But the value was incorrecrt, it is not the correct average.

 

[shg]

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).