Find Max of Summed Values

[RAD]

Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),SUM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I10,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM(I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SUM(I18,J18),SUM(I19,J19),SUM(I20,J20),SUM(I21,J21),SUM(I22,J22),SUM(I23,J23),SUM(I24,J24),SUM(I25,J25),SUM(I26,J26),SUM(I27,J27))
This =MAX(SUM(I2,J2),SUM(I27,27)) only finds the max of I2+J2 and I27+J27
This =MAX(SUM(I2:J2):SUM(I27:J27)) returns a formula error.

 

[Rick Rothstein \(MVP - VB\)]

This array-entered** formula should replace it...

=MAX(I2:I27+J2:J27)

** Commit the formula by pressing Ctrl+Shift+Enter instead of just pressing
Enter. Doing this will place curly brackets {} around the formula... do not
try to shortcut the process by typing them in yourself... that will not
work. If you ever edit the formula, you must remember to commit it using
Ctrl+Shift+Enter.

Rick

 

[Mike H]

Why not sum i2 and j2 in k2 and drag down to k27 and use the formula

=max(k2:k27)

If column k is in use you can use an out-of-the-way column and hide it if
you want.

Mike

 

[Rick Rothstein \(MVP - VB\)]

By the way, if you think you might be editing the formula in the future and
that you might not remember to use the array-entered keystroke to commit it,
you can use this less efficient normally-entered formula to get your result
instead...

=SUMPRODUCT(MAX(I2:I27+J2:J27))

Rick

 

[RAD]

That works great. Why do some formulas have to be commited with
Ctrl+Shift+Enter?
Thanks for the help.

 

[RAD]

Yes, yours is an efficient way to calculate this, but I wanted to know if and
how to avoid using another column.
Thanks for the help.

 

[Ron Coderre]

Couple alternatives....

Regular formula:
=MAX(INDEX(I2:I27+J2:J27,0))

or
Array Formula (with built in reminder)
=MAX(I2:I27+J2:J27)+N("Ctrl+Shift+Enter")

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[RAD]

Another great tip.
Thanks, again,
RAD

By the way, if you think you might be editing the formula in the future and
that you might not remember to use the array-entered keystroke to commit it,
you can use this less efficient normally-entered formula to get your result
instead...

=SUMPRODUCT(MAX(I2:I27+J2:J27))

Rick

 

[Rick Rothstein \(MVP - VB\)]

An array-entered formula takes a multi-cell range and iterates through that
range cell by cell. My guess is this is not how Excel was originally
constructed to work, so to allow for this added functionality, the
developers constructed an alternate input method so that the internal parser
would know whether to try calculating the functions inside a formula
directly (and failing for syntax when it hit the multi-cell range) or to use
its iterating code to perform the calculation. This allows existing
functions to be used in both modes (although not all functions can be forced
to do so).

Rick

 

[Rick Rothstein \(MVP - VB\)]

Couple alternatives....

Regular formula:
=MAX(INDEX(I2:I27+J2:J27,0))

Good point! Don't know why, but I seem to instinctively reach for the
catch-all SUMPRODUCT function without spending more time thinking about the
already array equipped ones.

or
Array Formula (with built in reminder)
=MAX(I2:I27+J2:J27)+N("Ctrl+Shift+Enter")

Hey! That's a good idea (although if the user is not familiar with the N
function, it might confuse him/her as well).

Rick

 

[Rick Rothstein \(MVP - VB\)]

An array-entered formula takes a multi-cell range and iterates

through that range cell by cell.

I probably should clarify that a little bit. If there are multiple
multi-cell ranges, each range must cover the same number of cells and the
iteration is like-cell to like cell. So, if the two ranges are A1:A4 and
C5:C9 (both ranges containing 5 cells each), then on the first iteration, A1
and C5 will be used, on the second iteration A2 and C6 will be used, etc.

You can find more information on array formulas here...

http://www.cpearson.com/excel/ArrayFormulas.aspx

Rick

 

[RAD]

Thanks, Ron. Adding the reminder is a good suggestion.
RAD

 

[Ron Coderre]

Well, here're my thoughts on that....

To use array formulas in other peoples workbooks without informing them
about what they are and how to use them practically guarantees I'll get
phone calls that could be avoided.

I make sure all of my users know what an array formula is, yet,
they NEVER remember to C+S+E when done editing the formula. So, I
strategically place +N("Ctrl+Shift+Enter") in some of the
formulas as a reminder. That dramatically reduced the number
of "I broke the formula" calls.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Bill Kuunders]

You could have a hidden column (K) where the sum is calculated on each line.
Then find the max =Max(K2:K27)