Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Average when some fields are null

Discussion in 'Information Technology' started by NB, Jul 28, 2006.

  1. NB

    NB
    Expand Collapse
    Guest

    I need to average scores from a test. Usually it is across 3 sections for
    each record, but sometimes a field is null. In Excel, the Avg command
    automatically adjusts when a field is null. Can Access?

    I want to see all records on one report, so I don't want to do it in
    separate queries.

    Example:

    RECORD 1
    Score A= 5
    Score B= 7
    Score C= 5
    Average = (5+7+5)/3 = 5.67

    RECORD 2
    Score A= 5
    Score B= null
    Score C= 7
    Average = (5+7)/2=6

    This group is absolutely great for information, so I have no doubt that you
    know the answer to this! Thank you for your help!

    NB
     
  2. Loading...

    Similar Threads Forum Date
    Sikhi About The Average Sikh Sikh Sikhi Sikhism Feb 18, 2016
    India Corruption rate in India is double of global average Breaking News Jul 9, 2013
    Average Jatt Sikh Male Height 5' 7" Health & Nutrition Aug 7, 2010
    Tech turns average Joes into mini-Spielbergs (USATODAY.com) Interfaith Dialogues Apr 1, 2005
    US Defense Department Hiring Resident Aliens in Medical and Language Fields Business, Lifestyle & Leisure Dec 6, 2008

  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Mybe there is a better way, but that can do the trick

    =Sum(Nz([Field Name]),0) / Sum(IIf([FieldName] Is Null,0,1))


    --
    Good Luck
    BS"D


    "NB" wrote:

    > I need to average scores from a test. Usually it is across 3 sections for
    > each record, but sometimes a field is null. In Excel, the Avg command
    > automatically adjusts when a field is null. Can Access?
    >
    > I want to see all records on one report, so I don't want to do it in
    > separate queries.
    >
    > Example:
    >
    > RECORD 1
    > Score A= 5
    > Score B= 7
    > Score C= 5
    > Average = (5+7+5)/3 = 5.67
    >
    > RECORD 2
    > Score A= 5
    > Score B= null
    > Score C= 7
    > Average = (5+7)/2=6
    >
    > This group is absolutely great for information, so I have no doubt that you
    > know the answer to this! Thank you for your help!
    >
    > NB
    >
    >
     
  4. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Ofer Cohen wrote:
    > Mybe there is a better way, but that can do the trick
    >
    > =Sum(Nz([Field Name]),0) / Sum(IIf([FieldName] Is Null,0,1))
    >
    >


    I have good news, bad news and good news :). The Avg function in
    Access doesn't count Nulls as part of the average. That's good news.
    In a query, the Avg function works down columns rather than in rows.
    That's bad news. Ofer's solution can work within a row as a calculated
    expression with minimal changes. That's good news. Post back if you
    need more help.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  5. Ron2006

    Ron2006
    Expand Collapse
    Guest

    On your table design, you may want to have the default value for
    numeric fields be 0, and if you need to deferrentiate between a
    non-entry and a actual 0 then use some other field/criteria. But it all
    depends on your data and your organization and whether you want to
    count 0s some of the times or all of the times or none of the times.

    Ron.
     
  6. Michael H

    Michael H
    Expand Collapse
    Guest

    Hi NB.

    Here are two methods. For each, I assume that the name of your Table is
    "Scores", and the names of your fields are "ScoreA", "ScoreB", and "ScoreC".
    Please make changes where appropriate using your own Table and Field names.

    Method 1 uses only SQL. It is a bit clumsy, and will give an error for the
    Average field if all three Score fields are Null, but otherwise it works (the
    error would not affect the query results, but you would probably want to hide
    the Average field in a report):
    SELECT Scores.ScoreA, Scores.ScoreB, Scores.ScoreC,
    (nz([ScoreA])+nz([ScoreB])+nz([ScoreC]))/(3+IsNull([ScoreA])+IsNull([ScoreB])+IsNull([ScoreC])) AS Average
    FROM Scores;


    Method 2 uses SQL and a custom function, fAverage. Paste the following code
    into a module (not a class module or one attached to a form):

    Public Function fAverage(ParamArray aMembers() As Variant)
    Dim i As Integer
    Dim lngCount As Long
    Dim lngSum As Long
    For i = 0 To UBound(aMembers)
    If aMembers(i) <> 0 Then
    lngCount = lngCount + 1
    End If
    lngSum = lngSum + Nz(aMembers(i))
    Next i
    If lngCount <> 0 Then
    fAverage = lngSum / lngCount
    End If
    End Function

    Then, use the following SQL:
    SELECT Scores.ScoreA, Scores.ScoreB, Scores.ScoreC,
    fAverage([ScoreA],[ScoreB],[ScoreC]) AS Average
    FROM Scores;

    Using Method 2, no error will be generated in the query if all three Score
    fields are Null. Additionally, an arbitrary number of parameters can be
    passed to fAverage. So, if for example a ScoreD field was added in the
    future, the only change necessary to Method 2 would be to add [ScoreD] to the
    list of parameters passed to fAverage.


    Hope this helps.

    -Michael

    "NB" wrote:

    > I need to average scores from a test. Usually it is across 3 sections for
    > each record, but sometimes a field is null. In Excel, the Avg command
    > automatically adjusts when a field is null. Can Access?
    >
    > I want to see all records on one report, so I don't want to do it in
    > separate queries.
    >
    > Example:
    >
    > RECORD 1
    > Score A= 5
    > Score B= 7
    > Score C= 5
    > Average = (5+7+5)/3 = 5.67
    >
    > RECORD 2
    > Score A= 5
    > Score B= null
    > Score C= 7
    > Average = (5+7)/2=6
    >
    > This group is absolutely great for information, so I have no doubt that you
    > know the answer to this! Thank you for your help!
    >
    > NB
    >
    >
     

Share This Page