1. ## 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. ## 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. ## Re: Range (2002 (10.2616.2625))

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