Im trying to use the 'Average' formula but I'm having some problems. The problem is that some of the cells dont have a value in at present. The values are 3,1, 0 or blank. Basically its a football points list, 3 for a win, 1 for a draw and 0 for a loss. Because some of the games havent been played yet, I need the formula to only count the cells with a value. How should I do that? Thanks.

I forget the exact syntax, but there are a few postings that might be of help with regards to searching all objects of a particular type on a given form.

In this case, you'd want to check each control in turn to see if it is a text box. If so, and if the value is null, ignore it. If so, and the value isn't null, include it in your calculations.

I'd try something along the lines of:

Sub ResetFormFields(strFormName As String)

Dim ctl As Control
Dim textboxcount as integer
Dim overallscore as integer

textboxcount = 0
overallscore = 0

For Each ctl In Forms(strFormName)
Select Case ctl.ControlType
Case Is = acTextBox
If Not isnull(ctl) then
textboxcount = textboxcount + 1
overallscore = overallscore + {score in ctl}
end if
ctl = ""
End Select
Next ctl

End Sub

Hope that's of some use.

Im just after the formula in excel, thanks anyway!

The cells with 0 are your problem. The blank ones do not affect the average or count functions. If the 0 entries must be maintained, I would suggest a hidden column. In the hidden column use a formula, IF(a1=0,"",a1). Fill this down for the number or rows in the display column. Average the hidden column.

Im trying to use the 'Average' formula but I'm having some problems.Try going back to first principals - Average = Sum / Count. Therefore, instead of using =Average(CellRange), try =Sum(CellRange)/CountA(CellRange). If you didn't know CountA is Count Absolute and ignores empty cells.