5

I have the following data in Excel:

spreadsheet 1

The real data will go on for over 100 columns.

What I want to get is a sum of the number if it were to repeat horizontally by the number of repeats. NB: Technically I'm repeating one less time, but I'm not wanted to split hairs.

Here's what the expansion would look like if I were to create multiple rows:

spreadsheet 2

I then can just sum those rows:

spreadsheet 3

What I'd like is a dynamic array function that lets me sum the values without the intermediate rows. It's doing my head in.

Here's what I'd like it to look like:

spreadsheet 4

I've tried to start with =BYCOL(R12C23:R12C,lambda(x, but am totally stuck at how to compute the result.

Can anyone suggest how to complete the formula or offer an alternative?

Improve this question
1
  • 4
    What a nice question! It shouldn't be too bad to create the 2D array using reduce/hstack for each row then reduce/vstack to stack them and finally get the column totals. The challenge for me is that you shouldn't have to actually create the 2D array...there should be a more direct way of creating the column totals just in a 1D array. Commented Mar 9, 2024 at 8:49

3 Answers 3

Reset to default
6

Here is one option:

enter image description here

Formula in B3:

=LET(x,B2:E2,y,COLUMN(x),z,TOCOL(y),TOROW(BYROW((z<=y+x-1)*(z>=y)*B1:E1,SUM)))
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Yes that is exactly the type of solution I was working towards.
@TomSharpe, let me know what you come up with! I feel this is still very verbose.
@JvdV - Thank you for this. I'm getting a #NAME? error. =LET(x,R13C23:R13C26,y,COLUMN(x),z,TOCOL(y),TOROW(BYROW((z<=y+x-1)*(z>=y)*R12C23:R12C26,SUM)))
@Enigmativity, I've used the ETA LAMBDA syntax where we could skip over the LAMBDA(r,SUM(r)) part and sum directly. You might want to fiddle that back in to see if that resolves the error. Furthermore, please note that I deduct 1 in my formula due to the fact there is one column to the left of the start of the array. Adjust your formula accordingly.
It works when I use LAMBDA(r,SUM(r)). Thank you.
|
2

Try

=MMULT(B1:E1,LET(n,COLUMNS(B1:E1),x,SEQUENCE(n,,0,-1)+SEQUENCE(,n),(x>=0 )*(x<=TRANSPOSE(B2:E2))))

or:

=MAP(COLUMN(B1:E1)-1,LAMBDA(x,SUM(TAKE($B1:E1*(SEQUENCE(,x,x,-1)<=B2:E2),,x))))

enter image description here

Improve this answer

1 Comment

Can I trouble you to see if you can convert this to a single cell result that doesn't spill? I need it to work from the current cell to the left.
2

I haven't had chance to sit down and work through this properly but my idea (very similar to @JvdV and @Dang D. Khanh) was just to work across a single row including all the values that were within range for each column:

=LET(values,B1:E1,repeats,B2:E2,seq,SEQUENCE(1,COLUMNS(values)),
MAP(seq,LAMBDA(c,SUM(values*(c>=seq)*(c<seq+repeats)))))

enter image description here

The equivalent formula for displaying one cell at a time would be:

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,SEQUENCE(1,COLUMNS(values)),col,COLUMN()-1,
SUM(values*(col>=seq)*(col<seq+repeats)))

or to show result for the column to the left of the current cell:

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,SEQUENCE(1,COLUMNS(values)),col,COLUMN()-2,
SUM(values*(col>=seq)*(col<seq+repeats)))

However I don't like the mix of column numbers and sequence running from 1 to 4 because it would only work if starting in column B.

Maybe better to use column numbers only like this:

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,COLUMN(values),col,COLUMN(),
SUM(values*(col>=seq)*(col<seq+repeats)))

and

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,COLUMN(values),col,COLUMN()-1,
SUM(values*(col>=seq)*(col<seq+repeats)))

which are not vulnerable to insertion/deletion of columns to the left of the data.

Improve this answer

1 Comment

Can I trouble you to see if you can convert this to a single cell result that doesn't spill? I need it to work from the current cell to the left.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.