Convert Excel formula for Access and run in text box? 3

[Turb]

I have been researching and testing and still cannot seem to figure this one out...
I have to convert this Excel formula to work in an Access form:

=(MAX(D16)-MIN(D16))/(MAX(D16)+MIN(D16))*100

My form's unbound text box controls:
D1, D2, D3, D4, D5, D6

Formula would be entered as control source of unbound text box control D8.

I'm trying to set it up so once the user has entered all data into text boxes D1 thru D6, D8 would automatically populate with the formula results.
Please help!



- Turb

 

[straybullet]

I had good luck using Allen Browne's MaxOfList/MinOfList functions. (

http://allenbrowne.com/func-09.html)

Create 2 additional textboxes on your form (you can hide them later). I labeled mine txtMax and txtMin and refer to them in the calculation textbox. For your formula, it would be =(([txtMax]-[txtMin])/([txtMax]+[txtMin]))*100

Let them hate - so long as they fear... Lucius Accius

 

[Turb]

straybullet, thank you for your help!
But for some reason, this is returning some strange numbers for me.
I created the module exactly as Allen Browne suggests (I made no changes):

Function MinOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMin As Variant   'Smallest value found so far.

    varMin = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMin <= varValues(i) Then
                'do nothing
            Else
                varMin = varValues(i)
            End If
        End If
    Next

    MinOfList = varMin
End Function

Function MaxOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.

    varMax = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMax >= varValues(i) Then
                'do nothing
            Else
                varMax = varValues(i)
            End If
        End If
    Next

    MaxOfList = varMax
End Function

and I saved & named the module 'MinMax'.
I then created two text boxes on my form and called them 'txtMin' and 'txtMax' (as you suggested).

Control source for txtMin is =MinOfList([D1],[D2],[D3],[D4],[D5],[D6])
Control source for txtMax is =MaxOfList([D1],[D2],[D3],[D4],[D5],[D6])
Control source for my D8 text box is =(([txtMax]-[txtMin])/([txtMax]+[txtMin]))*100

Values entered in D1 - D6:
D1 = 12.5
D2 = 15
D3 = 8.75
D4 = 11
D5 = 10.5
D6 = 9

Unfortunately, with the above numbers in D1 - D6, txtMin gives me 10.5; txtMax gives me 9 and D8 gives me -0.1647446458.
The Excel spread formula gives me for D8: 26.31579, with a MIN of 8.75 and a MAX of 15.

Where did I go wrong?


- Turb

 

[SkipVought]

In TEXT, "10.5" colates before "9" because "1" colates before "9".

You must CONVERT the TEXT to a NUMBER using something like CDbl. Your TextBox may also have a Numeric property function. I am not an Access user.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Turb]

SkipVought,
That was the key!
Once I set the format of the text box controls (all of those involved) to 'General Number' and saved the form, it all worked fine.

My thanks, to both of you, for all your help!


- Turb