Say I have the following basic spreadsheet:
A B C D
1 -2 4 2 12
2 -1 1 0
3 0 0 0 22
4 1 1 2 12
5 2 4 6
6 3 9 12
The A column has integers from -2 to 3.
The B column has the a column value squared.
The C column is the row sum of A and B so C1 is =SUM(A1:B1).
D1 has =MAX(C1:C6) and this max is the result I need to get with a single formula.
D3 is =MAX(SUM(A1:B6)) entered with Ctrl+Shift+Enter, but it just results in a regular sum. D4 is =MAX(A1:A6+B1:B6) with ctrl+shift+enter, and this works and gives the correct result of 12.
However the problem with D4 is that I need to be able to handle large dynamic ranges without entering endless sums. Say SUM(A1:Z1000) would be A1:A1000+B1:B1000+....+Z1:Z1000 which is not a reasonable formula.
So how can I do something like =MAX(SUM(A1:Z1000)) such that it would sum the rows A1:Z1 to A1000:Z1000 and give me the final row-wize max.
I can only use base Excel, so no helper columns and no VBA function.
UPDATE Since there have not been any successful answers, I have to assume it is not possible with current Excel versions.
So I am trying to build this function in VBA and this is what I have so far.
Function MAXROWSUM(Ref As Range) As Double
Dim Result, tempSum As Double
Dim Started As Boolean
Started = False
Result = 0
For Each Row In Ref.Rows
tempSum = Application.WorksheetFunction.Sum(Row)
If (Started = False) Then
Result = tempSum
Started = True
ElseIf tempSum > Result Then
Result = tempSum
End If
Next Row
MAXROWSUM = Result
End Function
This function works and is quite fast with less than 100k rows, but if the row count in the range approaches the possible 1 million, the function becomes very slow taking several seconds, even if most of the range is empty. Is there a way to significantly optimize the current code, by possibly filtering out any empty rows? In my example if I enter MAXROWSUM(A1:B1000000) it will work, but will be slow, can I make this very fast?
Your solution is Matrix Multiplication, via the MMULT function
How this works is as follows: currently, you have an X*N array/matrix, which you want to change into an X*1 matrix (where each new row is the sum of the rows in the old matrix), and then take the maximum value. To do this, you need to multiply it by an N*1 matrix: the two Ns "cancel out".
The first step is simple: every value in your second matrix is 1
Example: [6*2] ∙ [2*1] = [6*1]
[[-2][ 4] [[ 2]
[-1][ 1] [[ 1] [ 0]
[ 0][ 0] ∙ [ 1]] = [ 0]
[ 1][ 1] [ 2]
[ 2][ 4] [ 6]
[ 3][ 9]] [12]]
We then MAX this:
=MAX(MMULT(A1:B6,{1;1}))
To generate our second array dynamically (i.e. for any size), we can use the first Row of our table, convert it entirely to 1 (for example, "Column number > 0"), and then TRANSPOSE this to be a column instead of a row. This can be written as TRANSPOSE(--(COLUMN(A1:B1)>0))
Put it all together, and we get:
=MAX(MMULT(A1:B6, TRANSPOSE(--(COLUMN(A1:B1)>0))))
Since MMULT works with arrays - like SUMPRODUCT - we don't actually need to define this as an Array Formula with Ctrl+Shift+Enter either!
If you wanted to do this column-wise instead, then you would need to swap the arrays around - Matrix Multiplication is not commutative:
=MAX(MMULT(TRANSPOSE(--(ROW(A1:A6)>0)), A1:B6))
[1*6] ∙ [6*2] = [2*1]
[[-2][ 4]
[-1][ 1]
[[ 1][ 1][ 1][ 1][ 1][ 1]] ∙ [ 0][ 0] = [[ 3][19]]
[ 1][ 1]
[ 2][ 4]
[ 3][ 9]]
{UPDATE} In July 2021, Microsoft introduced the new BYROW and BYCOL functions, which can be used to do this and more: it allows you to feed each Row/Column into a function one at a time, and receive an array of the results. Our MMULT is then the equivalent of:
=BYROW(A1:B6, LAMBDA(rw, SUM(rw)))
Which can then be MAXed in the same way:
=MAX(BYROW(A1:B6, LAMBDA(rw, SUM(rw))))