Man in the middle

[Guest]

I am using Office 2003 on Windows XP.

In code, I need to work out which variable contains the middle value, in
this example, it would be 85. I would guess this needs to be done by
comparing <= and >= and sometimes there could be a two or three way tie. In a
tie, it doesn't matter which one is in the middle...but the code needs to
identify which variable has been selected. So in the example, I would need to
know that dVar1 is in the
middle.

Dim dVar1 as Double
Dim dVar2 as Double
Dim dVar3 as Double
dVar1 = Abs(-85)
dVar2 = Abs(95)
dVar3 = Abs(25)

If dVar1 >= dVar2 then ... (what next? I can't crack it!)

Do I have to try every combination to work this out? I can't seem to work
out the code to ensure that it always works. Your help is appreciated.

 

[Luke Alcatel]

I would advise you not to develop a specialized solution that only works for
the case of 3 items since you'll need to re-solve the problem when your boss
decides s/he wants to see the middle (wo)man of 5 items. The general
procedure is to sort your list and take the middle item from the list. A
simple explanation of bubble sort is:
1. Place your data in an array if N items
2. Create an outer loop that loops from N-1 to 1
3. Create in inner loop that loops from 0 to the value of the outer loop
variable (J).
4. In the inner loop compare item(J) to item (J+1) and swap if they are out
of order.
5. When the outer loop finishes the list is sorted. You can optimize it by
keeping track of whether there were any swaps in an iteration of the inner
loop and the first time there isn't one, your list is sorted.

There are quicker sorts but this is most suitable for a problem like yours.
I may have made some (hopefully) minor mistakes by regurgitating this from
memory but it's a good mental exercise to verify the logic.

Luke

 

[Guest]

Sub MiddleValue()
Dim dVar1 As Double
Dim dVar2 As Double
Dim dVar3 As Double
Dim midval As Double
Dim s As String
dVar1 = Abs(-85)
dVar2 = Abs(95)
dVar3 = Abs(25)
midval = Application.Median(dVar1, dVar2, dVar3)
If dVar1 = midval Then s = "dVar1"
If dVar2 = midval Then s = "dVar2"
If dVar3 = midval Then s = "dVar3"
Debug.Print s, midval
End Sub

 

[Guest]

If the there are an odd number of variables, the Median function eliminates
all that for you. If it isn't an odd number, what would be the definition of
the middle value? <g>

 

[Luke Alcatel]

Well I never knew of the Median function, probably because it ...
- is not in my Excel 2000 help.
- is not a choice with automatic completion in the VBA editor.

However I would still shy away from it because its syntax is poorly thought
out. It appears to only accept an explicit list of variables, not an array.
If the OP's boss is delighted with his skill in solving the problem, he's
got some woe when the boss wants the median of 1001 items.

BTW, for closure, I'll painfully complete the OP's question:
If dVar1 > dVar2 Then
temp = dVar2
dVar2 = dVar1
dVar1 = temp
End If
If dVar2 > dVar3 Then
temp = dVar3
dVar3 = dVar2
dVar2 = temp
End If
If dVar1 > dVar2 Then
temp = dVar2
dVar2 = dVar1
dVar1 = temp
End If

Note that this is nothing more than an ugly implementation of the bubble
sort algorithm I explained.

Luke

 

[Guest]

Sub ABC()
Dim v(1 To 1001)
For i = 1 To 1001
v(i) = Int(Rnd() * 100000 + 1)
Next
MsgBox Application.Median(v)

End Sub


worked fine for me.

Probably shouldn't advise something based on knowing nothing about it.

 

[Luke Alcatel]

Tom,
You're right but I did try it. Unfortunately I had an error elsewhere and
my misinterpretation of the error made me think it didn't work with an array
argument. However I'll defend myself against not knowing anything about it.
As I said earlier both normal Help lookups and line completion in the editor
came up empty on the Median function. With significant perserverance I
eventually found some discussion in Help under the topic of Excel functions
that can be used in VBA. The Help topic there starts by saying the
calculation is limited to 30 numbers. It then goes on to say that the
arguments can be array names as well as an explicit list. It's not too
obvious from this documentation (if one can find it) that the function would
work with more than 30 numbers in an array.

In the end one has to make decisions about the "good" features to use and
the problematic ones. I would still make my choice because I prefer to have
a "bag of tricks" that is portable and does not rely on a specific
spreadsheet capability (if the OP ever needs to find a name that is the
"lexigraphical median" the sort method has already solved the problem). On
the other hand using the built-in function is more efficient and presumably
it is better tested.

Luke