Find Max of Summed Values
 

Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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


"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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

"RAD" wrote:

Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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)" wrote in
message ...
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


"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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.

"Mike H" wrote:

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

"RAD" wrote:

Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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)


"Rick Rothstein (MVP - VB)" wrote in
message ...
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)" wrote in
message ...
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


"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

Another great tip.
Thanks, again,
RAD

"Rick Rothstein (MVP - VB)" wrote:

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)" wrote in
message ...
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


"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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


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

"Rick Rothstein (MVP - VB)" wrote:

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


"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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

Find Max of Summed Values
 

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

Find Max of Summed Values
 

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

"Ron Coderre" wrote:

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)


"Rick Rothstein (MVP - VB)" wrote in
message ...
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)" wrote in
message ...
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


"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.

Find Max of Summed Values
 

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)



"Rick Rothstein (MVP - VB)" wrote in
message ...
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

Find Max of Summed Values
 

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

--
Greetings from New Zealand
"RAD" wrote in message
...
Is there a more streamlined way to calculate this?
=MAX(SUM(I2,J2),SUM(I3,J3),SUM(I4,J4),SUM(I5,J5),S UM(I6,J6),SUM(I7,J7),SUM(I8,J18),SUM(I9,J9),SUM(I1 0,J10),SUM(I11,J11),SUM(I12,J12),SUM(I13,J13),SUM( I14,J14),SUM(I15,J15),SUM(I16,J16),SUM(I17,J17),SU M(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.