Maximum Number of Characters in an Excel (2010) Formula = 8,192

[NavyGator]

So, while working on a spreadsheet for handling COOP's today I came across something that was interesting and scary all at the same time.

The maximum number of characters that can be in an Excel (2010) formula are 8,192.

The interesting part is that I now know the limit. The scary part is I had to break my formula up into three parts. Hehehe.

Let the humor flow forth....

[Mimir]

That is one complicated formula!

Sent from my Motorola Atrix2

[kgustafson]

Have you looked to shrink your reference points to single or double letters?  That may help shrink it a bit.

[chrisatpsu]

3 parts?  heh   simple arithmetic...  lol

[NavyGator]

Have you looked to shrink your reference points to single or double letters?  That may help shrink it a bit.

I thought of a different way yesterday but I figured I was so far down this path that I might as well finish it off to see the feasibility of the whole spreadsheet.

[NavyGator]

3 parts?  heh   simple arithmetic...  lol

Right....again...

[rrowan]

So its two Pixelnet Universes

Is the formula in binary?

Rick R.

[knguyen916]

i'm curious to see this formula 

[mr2hogs]

Might I recommend you modify the formula from

=A1+A2+A3+A4+A...

to

= sum(A1:A5000)

 

[NavyGator]

So its two Pixelnet Universes

Is the formula in binary?

Rick R.

It is kind of ironic that it is 2 PixelNet universes.

[NavyGator]

i'm curious to see this formula 

I probably shouldn't do this...but....here it is. Well 1/3 of it anyways.

It is basically nested if statements. In hind sight there was a couple of other options to do this.

[chrisatpsu]

So its two Pixelnet Universes

Is the formula in binary?

Rick R.

It is kind of ironic that it is 2 PixelNet universes.

etherdongle, and you've got room to grow!

[rm357]

Not sure what you are trying to do, but I think that type of coding would be more appropriate as a virtual basic macro that you call rather than a formula embedded in a cell. If it doesn't work the first time, editing an debugging will be a nightmare.

The industry average is around 40 errors per thousand lines of code (before reviews). At 8000 characters, you probably have 4 errors... In each third... (I figured using 80 characters per line, which is really a long line)

RM

[NavyGator]

Not sure what you are trying to do, but I think that type of coding would be more appropriate as a virtual basic macro that you call rather than a formula embedded in a cell. If it doesn't work the first time, editing an debugging will be a nightmare.

The industry average is around 40 errors per thousand lines of code (before reviews). At 8000 characters, you probably have 4 errors... In each third... (I figured using 80 characters per line, which is really a long line)

RM

I was able to build it in sections, and there is some repetition in the code so it wasn't too bad to write. It just looks horrendous.

I have never used macros yet. I am looking at some to see how they work to try and figure them out.

If I am not mistaken, don't macros also cause security concerns?

Rich

[Mike Hill]

So, while working on a spreadsheet for handling COOP's today I came across something that was interesting and scary all at the same time.

The maximum number of characters that can be in an Excel (2010) formula are 8,192.

The interesting part is that I now know the limit. The scary part is I had to break my formula up into three parts. Hehehe.

You need to get the updated version of Excel, it far exceeds 8,192