1. ## Formula Suggestion (03)

I have encountered the 256 character limitation for a formula within a cell and I am looking for an alternative way to state the formula.

As an example -

I have three sections [actual, budget, last year] each containing 12 months. Therefore the formula is based on 36 cells in the row: tests each cell value to determine if it contains a zero. If all 36 cells contain a zero then the returned value is *; if not then " ".

I do have a total column for each one of the three sections but the total of the 12 months may net to zero. Stated another way, March has a value of 100 and Sept has a value of -100 thus the net amount is zero.

Any words of wisdom would truly be appreciated.

John

2. ## Re: Formula Suggestion (03)

Say that the values are in A2:AJ2. Assuming that all values are numeric or blank, you can use this array formula (confirm with Ctrl+Shift+Enter):
<code>
=IF(SUM(ABS(A2:AJ2))=0,"*","")
</code>
The ABS function removes the - from negative numbers, so the result is always greater than or equal to 0. The sum of the absolute values can only be 0 if all of the values are 0 (or blank, which counts as 0 too).

3. ## Re: Formula Suggestion (03)

Have you thought of naming some of the ranges to reduce length?

4. ## Re: Formula Suggestion (03)

By the way, the maximum length of a formula is 1024 characters in Excel 2003. In Excel 2007, it is 8092 characters.

5. ## Re: Formula Suggestion (03)

Hans,

Great suggestion.

Regards,
John

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•