formula to only calculate the last 15 entries?
 

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.

formula to only calculate the last 15 entries?
 

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.
--
HTH...

Jim Thomlinson


"April" wrote:

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.

formula to only calculate the last 15 entries?
 

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

"April" wrote in message
...
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.