| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find values in multiple cells and paste row values | Excel Discussion (Misc queries) | |||
| find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
| Add to Summed Figures | Excel Worksheet Functions | |||
| Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
| SUMIF where values to be summed are formula | Excel Worksheet Functions | |||
Linear Mode
