Hello Jami,
It may be a better option (but its up to you) to use two separate tables side by side. One table is for the monthly odometer readings and the other can be used to do the calculations without the negative values. We can then use VBA to insert formulae for the calculations.Have a look at the following sample I knocked up for you:-
http://ge.tt/2r7NeWm2
To start with, type a value into Column H (1st August), larger than the previous value, then click away (or press enter or down arrow) and you'll see the second table update, including the yearly total and average to date. Do the same for the remaining months then start a second row if you wish. Totals and averages should update.
As you can see, I haven't actually set the sample up as tables but you can do this yourself just to see that it all still works as it should. Remember to keep at least one column between the tables as per the sample.
Following is the code for this:-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("A2:M" & lr)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Range("O2:Z" & lr).Formula = "=IF(B2-A2=0,"""",B2-A2)"
Range("AA2:AA" & lr).Formula = "=SUM(O2:Z2)"
Range("AB2:AB" & lr).Formula = "=AVERAGE(O2:Z2)"
For Each c In Range("O2:Z" & lr)
If Left(c.Value, 1) = "-" Then
c.ClearContents
End If
Next c
Sheet1.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
It is a Worksheet_Change event and needs to be placed into the sheet1 (or your master sheet) module.
Let us know your thoughts.
Cheerio,
vcoolio.