formula to only calculate the last 15 entries?

[April]

I have a spreadsheet where we are showing wins, losses, and total points
scored. We also show the same data but for only the last 15 matches. Is
there a way to have a cell only take the last 15 entries even though there
might be 100 entries.

Currently we have the cell such as this: Cell A1 has the wins inputted as
follows: "=5+5+5+5+5+5+5". As each week passes, we enter another value in
a1. I'd like to use cell a2 to calculate the numbers of the last 15 entries
of a1.

Any help is appreciated.

 

[Jim Thomlinson]

With the scores in indivuidual cells we would have a shot at extracting the
last 15 entries, but with everything in one cell the solution becomes very
complicated involving a bunch of VBA code.

 

[Henry]

April,


If you enter your scores as text (you put an apostrophe before the "=") and
your scores can never be more than 9 (1 digit long) then you could try:

Sub Last15()
Dim Score15 As String
Dim Total15 As Integer
Dim Counter as Integer

'Make sure the there are at least 15 scores entered
If Len(Range("A1").Value) =>30 Then
'Take the last 15 entries from A1
Score15 = Right(Range("A1").Value,29)
Total15 = 0
'Step through the string adding the scores but missing the "+"s
For Counter = 1 To 29 Step 2
Total15 =Total15 + Val(Mid(Score15,Counter,1))
Next Counter
Range("A2").Value = Total15
Else
Range("A2").Value = "Not enough results"
End If
End Sub

Henry