Solution for How To Append Vector Of Row Sums Using Let Function In Excel
is Given Below:
My question is two parts:
- Is there a way to return a vector of the row sums of a data frame using the
LET
function? - Can one return this column appended to the original data frame in the same function?
For example, given the below data, I want to return the full data frame along with the column c(6, 15, 24).
I guess the first order of business is getting each individual row, but I have struggled so far. I’ve tried a couple things like =LET(x, $A$1:$C$3, INDEX(x, ROW($A$1:$A$3), 0))
or =LET(x, $A$1:$C$3, y, ROW($A$1:$A$3), INDEX(x, y,y))
but no dice. Any tricks? Maybe the CHOOSE
function could come in handy?
=LET( data, A1:C3,TRANSPOSE(MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0))))
By multiplying all the values of one row with 1 per row using MMULT you can avoid SUM and INDEX. The result is a spilled sum result already that can be transposed.
Without LET: =TRANSPOSE(MMULT(--A1:C3,SEQUENCE(COLUMNS(A1:C3),1,1,0)))
Not sure if you’re u wanted to include the data set in the result; in that case you could use: =LET( data, A1:C3,drows,ROWS(data),sum,TRANSPOSE(MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0))),IF(SEQUENCE(drows+1)<=drows,data,sum))
I just figured you would want it to be added as a column rather than a row (makes more sense too): =LET( data, A1:C3,dcolumns,COLUMNS(data),sum,MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0)),IF(SEQUENCE(1,dcolumns+1)<=dcolumns,data,sum))
Aesthetic improvement showing blanks as blanks and zeroes as zeroes in the spill of the original range:
=LET( data, A1:C3,dcolumns,COLUMNS(data),sum,MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0)),IF(SEQUENCE(1,dcolumns+1)<=dcolumns,IF(LEN(data)<>0,data,""),sum)