You might find this useful if you use Excel to log your fuel purchases and figure your fuel cost per mile. Or it might come in handy in some other way.

Excel allows users to create what it calls UDFs (user defined functions). This function takes a range of numbers and a count and returns the average.

All you have to do is open Excel. Press Alt-F11 to open up the Visual BASIC editor. Select Insert->Module from the overhead menu. And copy and paste this code into the module.

Function AverageLastX(RangeParam As Range, Count As Long)
Dim R As Range
Dim NumNumbers As Long


NumNumbers = WorksheetFunction.CountIf(RangeParam, ">-999999999")

If Count <= RangeParam.Rows.Count Then
Set R = Range(RangeParam(NumNumbers - Count + 1, 1), RangeParam(NumNumbers, 1))
Else
Set R = RangeParam
End If
AverageLastX = WorksheetFunction.Average(R)
Application.Calculate

Debug.Print "Num rows in range = " & RangeParam.Rows.Count
Debug.Print "Num numbers in range = " & NumNumbers
Debug.Print "Calced range = " & R.Address
End Function


Now go back to your spreadsheet and enter the formula into any cell like this (assuming the numbers you want to average are in column f) "=averagelastx(f1:f16000, 5)" (without the quotes)

What I do is I've got a sheet called fuel, where I enter each fuel purchase on it's own line along with odometer readings and dates and calculate cents/per mile. On another sheet I call truck costs, I add up all my costs for insurance, load boards, telephone, internet etc, etc and of course fuel. The actual formula on the trucks costs sheet that gets the average from the fuel sheet is "=averagelastx(Fuel!K3:K16000, 5)" (without the quotes). I've got another sheet called bids that uses the final truck costs/per mile number to help me see how badly I'm getting screwed.

I'm not claiming this is a fine piece of code or anything, it's just one way to get it done. Btw, the 3 statements at the end that start with debug.print help with debugging. From the code window, if you click View->Immediate Window from the overhead menu, you'll see what those three lines print. It's a good tool for debugging. I left those lines in there for the 1 in 100 people that might be interested in experimenting. You can delete them and make the code a fraction of a millisecond faster if you want.