+ Reply to Thread
Results 1 to 7 of 7

Dealing with long formulas in Excel 2007

  1. #1
    Registered User
    Join Date
    09-27-2011
    Location
    Tucson, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    10

    Dealing with long formulas in Excel 2007

    Need some advice on working with long formulas. The particulars of my formula aren't important, but here it is for reference:
    =IFERROR(IF(AND('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$17,'.OPS01-MXL'!$F$2=CONCATENATE('ACES UPLOAD'!X2,'ACES UPLOAD'!Y2)),'.OPS01-MXL'!$F$53,IF('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$18,0,IF('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$19,0,INDEX('CLIN ALLOCATIONS'!$E$20:$BB$23,MATCH('.OPS01-MXL'!$F$1,'CLIN ALLOCATIONS'!B20:B23,0),MATCH(CONCATENATE('ACES UPLOAD'!X2,'ACES UPLOAD'!Y2),'CLIN ALLOCATIONS'!$E$12:$BB$12,0)*'.OPS01-MXL'!$G$75)))),0)
    The formula is a bit of a convoluted test against values in the [unnamed] range of 'CLIN ALLOCATIONS'!B17 through B23. Based on which value is found, do something, i.e., either set the result to zero, set it to a value found in the cell '.OPS01-MXL'!$F$53, or finally using an INDEX plus horizontal and vertical MATCH functions, multiple the value in '.OPS01-MXL'!$F$53 by a percentage found in the INDEX table.

    The issue is I would like to replace all of the absolute tab references to '.OPS01-MXL'!$F$1 with a dynamic reference using values from a nearby column: MATCH(INDIRECT("'" & "." & AJ2 & "'!$F$1" where AJ2 contains ".OPS01-MXL". Sounded simple until I got the error message that I've hit the 255 character limit.

    Did I mention I need to populate 68,000 rows with this formula? The tab reference for each row is already there but changes every 350 rows or so, so I'd really like a draggable formula rather than having to manually update it for each new tab reference. Renaming tabs with shorter names is an option, I suppose, but would make the finished workbook less user-friendly than leaving the more descriptive names in place.

    Any ideas on how to either shorten the existing formula to a point that will handle the additional text count, or circumvent the limitation altogether?

    Thanks,

    Tom

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Dealing with long formulas in Excel 2007

    Hi Tom

    to shorten your formula a bit you can
    1.instead of concatenate(a1,a2,a3) use =a1&a2&a3
    2. Use defined names for parts of your formula and reassemble the names to create your formula

    For instance INDIRECT("'" & "." & AJ2 & "'!$F$1") could be replaced by "MyRef"

    I did not check the validity of your formula - Good luck

  3. #3
    Registered User
    Join Date
    09-27-2011
    Location
    Tucson, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Dealing with long formulas in Excel 2007

    Thanks, Pepe, for the reply. After letting it simmer for a couple days I realized I was making it too complicated with three exceptions before using a lookup table. I got rid of two of the nexted IF statements by adding two additional rows to my INDEX/MATCH/MATCH lookup table populated with all zeroes. Same result, much shorter formula. Also got rid of the CONCATENATE as you suggested, leaving plenty of room for INDIRECTs to generate dynamic tab name references so I can drag the formula down now. Very happy.

    WAS:
    =IFERROR(IF(AND('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$17,'.OPS01-MXL'!$F$2=CONCATENATE('ACES UPLOAD'!X2,'ACES UPLOAD'!Y2)),'.OPS01-MXL'!$F$53,IF('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$18,0,IF('.OPS01-MXL'!$F$1='CLIN ALLOCATIONS'!$B$19,0,INDEX('CLIN ALLOCATIONS'!$E$20:$BB$23,MATCH('.OPS01-MXL'!$F$1,'CLIN ALLOCATIONS'!B20:B23,0),MATCH(CONCATENATE('ACES UPLOAD'!X2,'ACES UPLOAD'!Y2),'CLIN ALLOCATIONS'!$E$12:$BB$12,0)*'.OPS01-MXL'!$G$75)))),0)

    IS:
    =IFERROR(IF(AND(INDIRECT("'" & "." & AJ2 & "'!$F$1")=ALLOCATIONS!$B$17,INDIRECT("'" & "." & AJ2 & "'!$F$2")=(X2&Y2)),INDIRECT("'" & "." & AJ2 & "'!" & AN2),(INDEX(ALLOCATIONS!$E$18:$BB$23,MATCH(INDIRECT("'" & "." & AJ2 & "'!$F$1"),ALLOCATIONS!$B$18:$B$23,0),MATCH((X2&Y2),ALLOCATIONS!$E$12:$BB$12,0)))*INDIRECT("'" & "." & AJ2 & "'!" & AN2)),0)

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Dealing with long formulas in Excel 2007

    Glad it helped I hope you don't have too many INDIRECT functions all over the place. Being volatile they will eventually slow your calculations down

  5. #5
    Registered User
    Join Date
    09-27-2011
    Location
    Tucson, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Dealing with long formulas in Excel 2007

    A question, and a comment.

    The question: If I understand correctly (always in doubt...) volatile functions can be neutralized by turning off AutoCalc. True statement? I ask because in previous versions of this file we've had to disable AutoCalc because the 68K rows that contain something similar to my original iteration of the lookup formula posted above slowed things way, way down to the point of user frustration.

    The comment: The offending worksheet is essentially a pre-formatted upload sheet required for dumping data into another system into which we have been unable to get "hooks" for direct data transfer. Where I'd like to take it eventually is to simply replace the upload sheet full of 68,000 rows of lookup results with a VBA routine that hides out inactive until the user wants to generate the upload sheet. It's probably a bit beyond my VBA skills at the moment to replace the slimmed down formula above with VBA, but if I can't figure it out I can turn to other resources within the company to complete it for me.

    Thanks,

    Tom

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Dealing with long formulas in Excel 2007

    Using a named range (eg for an absoluted reference) also makes the formula easier to read when you have to edit it (or figure out later what the heck its doing)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    09-27-2011
    Location
    Tucson, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Dealing with long formulas in Excel 2007

    Quote Originally Posted by FDibbins View Post
    Using a named range (eg for an absoluted reference) also makes the formula easier to read when you have to edit it (or figure out later what the heck its doing)
    There is, in fact, a common set of cells being referenced on each of 72 different source tabs which drive the final summary report being fed into the other system. Each of the 72 tabs are identical except for 19 unique cells which must be evaluated across 50 potential recipient cells using the lookup arrangement above. Hence the 68,000+ rows: 19 inputs * 72 tabs * 50 potential recipient lines per tab. After the inputs have been captured and the summary report calculates, the user simply filters the summary for non-zero amounts and uploads the summary. Most (perhaps as much as 95% of the rows) are zero-value because there's no match between tab, input element, and recipient assignment.

    All that said, what's the performance penalty for have the same ranges set up as named ranges on each of the 72 tabs, if any?

    Thanks as always,

    Tom

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1