Sum last X entries in a row

[Marycrawford]

[FONT=&quot]Dear All,[/FONT]
[FONT=&quot]I’m pretty new to Excel and appreciate any guidance regarding the following issue that I encounter;

In the following, I have a row of numbers; What I want to be in the second row is the sum of the last “X” number of elements; Meaning that for X=4, as long as the number of values in the first row is less than X=4, the second row will show the sum of the non empty cells in the first row and once the X+1 cell (5 for this example) gets filled with a value, the very first entry in the first row is subtracted from the sum; for the next coming value in cell X+2 of the first row, the second entry in the first row is subtracted from the sum and so on.[/FONT]
[FONT=&quot]Hence, we have a moving average of the sum of last X number of elements shown in the second row and in case the number of elements in the first row is less than X it doesn’t give an error but instead sum of the available entries.

[/FONT]
[TABLE="width: 979"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1=1[/TD]
[TD]3=1+2[/TD]
[TD]6=3+3[/TD]
[TD]10=6+4[/TD]
[TD]14=10+5-1[/TD]
[TD]18=14+6-2[/TD]
[TD]22=18+7-3[/TD]
[TD]26=22+8-4[/TD]
[TD]30=26+9-5[/TD]
[TD]34=30+10-6[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for any guidance.

 

[FormR]

Hi, welcome to the board!

Something like this maybe, where the 4 is your X.

=SUM(A1:INDEX($A1:$Z1,MAX(1,COLUMN(A1)-COLUMN($A$1)+1-(4-1))))

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	10
2	1	3	6	10	14	18	22	26	30	34
Sheet1

Cell Formulas
Range	Formula
A2	=SUM(A1:INDEX($A1:$Z1,MAX(1,COLUMN(A1)-COLUMN($A$1)+1-(4-1))))

 

[Marycrawford]

Hi and many thanks for your answer; however, as it's clear from below when I fill in just first and second entries in the first row, besides the correct entries in the second row (A2 and B2), the irrelevant entries are also filled with some redundant data: C2=3, D2=3, E2=2.

[TABLE="width: 1427"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Could you please help me how to get rid of this effect?
Many thanks beforehand for your help.

 

[FormR]

Could you please help me how to get rid of this effect?

Hi, how about?

=IF(ISNUMBER(A1),SUM(A1:INDEX($A1:$Z1,MAX(1,COLUMN(A1)-COLUMN($A$1)+1-(4-1)))),"")

 

[Marycrawford]

GREAAAAT! many many thanks for your quick awesome response.

Now, can I kindly ask another question that I guess is rather easy to an expert like you
What I want to do is to have a function is a cell that will get input in the same cell; What I want exactly, is to add X% to the input value that a user inserts into a cell; So, I want a function saying that, once a value inserted here, add X% to it and put the new value in the same cell.
I wonder would that be possible?
Many thanks again.

 

[FormR]

So, I want a function saying that, once a value inserted here, add X% to it and put the new value in the same cell.

You would need to use VBA for that, probably a "change event" assuming that the value would be entered manually.

 

[Marycrawford]

Yes, the value would be entered manually by the user; May I ask what is a VBA?

Many thanks.

 

[FormR]

May I ask what is a VBA?

Hi, it's a programming language for MS office applications that enables all sorts of automation.

Yes, the value would be entered manually by the user

Where is the value entered:
- A single cell (if so which cell)?
- Or a range of cells? (if so, which column, row or range of cells)?

Where does the x% come from:
- is it fixed (if so what is it)?
- or stored somewhere (if so where)?
- or logically derived (if so how)?

 

[Marycrawford]

Hi,

- It's a range of cells; assume A10-Z10.
- The x% is stored within the cell A1 and is fixed.

Thanks.

 

[FormR]

OK - you can try this event code.

To use:

1. Right click the sheet tab where you want this to happen and choose "View Code".
2. Copy and paste the code below to the top right hand side window.
3. Press ALT+Q to close the code editor
4. Save your file as a macro enabled workbook (*.XLSM)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Range("A10:Z10"))
If Not r Is Nothing Then
  Application.EnableEvents = False
  For Each c In r
    If IsNumeric(c.Value) And Len(c.Value) Then
      c.Value = c.Value + (c.Value * Range("A1").Value)
    End If
  Next c
  Application.EnableEvents = True
End If
End Sub