Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Suggestion (03)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •