# Formula Too Long

1. ## 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

2. ## 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....

3. ## 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

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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))