Average (1 Viewer)

[ddrew]

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.

 

[Matt Greatorex]

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 + {[I]score in ctl[/I]}
                end if
                ctl = ""
        End Select
    Next ctl

End Sub

Hope that's of some use.

 

[ddrew]

Im just after the formula in excel, thanks anyway!

 

[billyr]

Averaging without counting the 0s.

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.

 

[DreamGenius]

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.