Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range (2002 (10.2616.2625))

    Edited by HansV - data arranged in table for readability

    I am in need of help yet again.
    I have a small query and I need to calculate a range for each record which has 5 numbers in it
    ex:

    <table border=1><td>Record</td><td>Number1</td><td>Number 2</td><td>Number 3</td><td>Number 4</td><td>Number 5</td><td>Average</td><td>Range</td><td align=right>1</td><td align=right>5</td><td align=right>5</td><td align=right>6</td><td align=right>3</td><td align=right>1</td><td align=right>4</td><td>5 (Number 3-Number 5)</td></table>
    I can get the average in the query using = ([Entry 1]+[Entry 2]+[Entry 3]+[Entry 4]+[Entry 5])/5
    but I have not idea on how to get the range
    I was thinking of something along the line of max - min but I don't know how to get min or max across a record.

    ... thanks a lot

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range (2002 (10.2616.2625))

    Create a new module and copy the following functions into the module:

    Function MaxVal(ParamArray SomeValues()) As Variant
    Dim i As Integer
    MaxVal = Null
    For i = LBound(SomeValues) To UBound(SomeValues)
    If Not IsNull(SomeValues(i)) Then
    If IsNull(MaxVal) Then
    MaxVal = SomeValues(i)
    ElseIf SomeValues(i) > MaxVal Then
    MaxVal = SomeValues(i)
    End If
    End If
    Next i
    End Function

    Function MinVal(ParamArray SomeValues()) As Variant
    Dim i As Integer
    MinVal = Null
    For i = LBound(SomeValues) To UBound(SomeValues)
    If Not IsNull(SomeValues(i)) Then
    If IsNull(MinVal) Then
    MinVal = SomeValues(i)
    ElseIf SomeValues(i) < MinVal Then
    MinVal = SomeValues(i)
    End If
    End If
    Next i
    End Function

    To get the range, use the following expressions in the query:

    MaxNum: MaxVal([Number1],[Number 2],[Number 3],[Number 4],[Number 5])
    MinNum: MinVal([Number1],[Number 2],[Number 3],[Number 4],[Number 5])
    Range: MaxNum-MinNum

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range (2002 (10.2616.2625))

    You are a genius... that worked perfectly...

    Thanks for all your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •