+ Reply to Thread
Results 1 to 9 of 9

Formula Too Long

  1. #1
    Registered User
    Join Date
    11-29-2007
    Posts
    15

    Formula Too Long

    I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.

    I've tried the following but got an error stating it was too long:

    = IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))

    The repeating line needs attention in my mind:

    MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))

    Thanks
    Last edited by tsc35; 02-16-2010 at 08:45 PM. Reason: Solved
    Thanks,
    Travis

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Formula Too Long

    I don't understand the Vlookups... you only reference tables, but not the lookup values nor the column indexes to extract from etc....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-29-2007
    Posts
    15

    Re: Formula Too Long

    I'm just short handing the equation to quickly get my point across. I figured that obscure references would not help but here is the formula in is entirety:

    = IF(ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)) = MAX(ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))), VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE), IF(ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)) = MAX(ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))), VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE), IF(ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)) = MAX(ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))), VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE), VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))))

    I know I could split this into two cell formulas but I'm wondering if there is a way to do it in one step using some Boolean operation or something.

    Thanks
    Last edited by tsc35; 02-16-2010 at 05:18 PM. Reason: Additional clarification

  4. #4
    Registered User
    Join Date
    11-29-2007
    Posts
    15

    Re: Formula Too Long

    I've been able to shorten this up a bit using max/min comparisons but I still have the repetition issue. It would be nice if there was a function that determined the largest magnitude value in a list or database regardless of sign (-/+).

  5. #5
    Registered User
    Join Date
    11-29-2007
    Posts
    15

    Re: Formula Too Long

    I ended up not finding anything quick from EXCEL to shorten my formula or do what I wanted so I created a function:

    Please Login or Register  to view this content.
    Hope this helps others with the same problem.

  6. #6
    Registered User
    Join Date
    10-25-2008
    Location
    Mount Joy, PA
    MS-Off Ver
    2003
    Posts
    44

    Re: Formula Too Long

    I was trying to go threw your formula and ran into a couple things really quick.

    I am going to break down your formula so I can point out the problem.

    = IF(

    ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE))

    =

    MAX(ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), This line is missing a parenthesis close )

    ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)) After this line, you need another parenthesis close to end the if statement. If statement layout IF(logic text, Do if True, Do if False). You can't add a , and another statement

    Then I got lost. Maybe post up the actuall spreadsheet and the desired outcome and we can come up with something.

    ,ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))), VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE), IF(ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)) = MAX(ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))), VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE), IF(ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)) = MAX(ABS(VLOOKUP(C$6,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$7,Joint2_I_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE)), ABS(VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))), VLOOKUP(C$8,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE), VLOOKUP(C$9,Joint2_O_EB.rpt!$I$55:$O$99,5,FALSE))))

  7. #7
    Registered User
    Join Date
    11-29-2007
    Posts
    15

    Re: Formula Too Long

    Thanks... I realize that I may have miss-typed the formula however, the main problem is that in Excel there is only so many argument you can call in a single cell. Ignoring the mis-types you will get an error from Excel stating:

    Formula too long.

    I wanted a canned Excel function that would take a list of numerical arguments and return the largest magnitude, positive or negative. I couldn't find one so I created a function myself. See above.

  8. #8
    Registered User
    Join Date
    03-30-2010
    Location
    Salem, MA, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Cool Re: Formula Too Long

    Why not try this (paste it into a cell and modify it):

    =IF(MAX(A4:A11)<MIN(A4:A11),MAX(A4:A11),MIN(A4:A11))

    Where data is in cells A4 to A11.

  9. #9
    Registered User
    Join Date
    11-25-2010
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    1

    Smile Re: Formula Too Long

    I tried the Min / Max formula above, but it didn't return the results I wanted. However a simple modification, as below, works (where N4:N9 contains the range of numbers to check)

    =IF(ABS(MAX(N4:N9))>ABS(MIN(N4:N9)),MAX(N4:N9),MIN(N4:N9))

+ 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