PDA

View Full Version : VBA programming question on cell selection and optimization



thexside
06-16-2010, 06:56 PM
Guys, I want to maximize my probability of choosing the highest number out of individual rows of 10 randomly generated numbers, using a specific strategy and I can't figure out how to write the code.

Let's say I have the following list of 10 random generated numbers on one row in excel:
5, 1, 3, 7, 2, 6, 4, 9, 8, 10

I want to apply a (x,y) selection strategy by changing parameters x and y and optimizing the best values of x and y.

The selection is illustrated as follows(with different x and y values) and using the random generated list of 10 numbers:

random list:
5, 1, 3, 7, 2, 6, 4, 9, 8, 10


(1,1) strategy:
You let the first number go by and then choose the first one bigger than this. The first one is 5. The first one bigger than this is 7. You choose this one(7).

(2,1) strategy:
You let the first two go by and then choose the first one bigger than the larger of these two. The larger of the
first two is 5. The first one bigger than this is 7. You choose this one.

(1,2) strategy:
You let the first one go by and then choose the one that increases the maximum the second time. The first one
is 5. The first one bigger than this is 7. You do not choose 7; you wait until the maximum has been increased
once more. The first one bigger than 7 is 9. This is the one you choose.

(4,2) strategy:
You let the first four one go by and note the maximum of the four. Then choose the one that increases the
maximum the second time. The maximum of the first four is 7. The first one bigger than this is 9. You do not
choose 9; you wait until the maximum has been increased once more. The first one bigger than 9 is 10. This is
the one you choose.


I want to run a simulation on the strategy, let's say I have 20 rows of "10 random regenerated numbers"; I want to test which strategy is the most optimal at selecting the highest numbers in each row.

Hope is clear, any help would be greatly appreciated (see it as a intelectual challenge;) ):banghead: :thumb

shrivallabha
06-16-2010, 11:24 PM
Please see the attached file and User Defined function.

The code is as here below

Function OptimumValue(Num As Double, Max As Double) As Double

Dim FirstMax As Double
Dim Counter As Double

'Determining the biggest value in the first set
If Num < 1 Then
MsgBox "Please provide valid input!"
Exit Function
Else
FirstMax = Cells(ActiveCell.Row, ActiveCell.Offset(, -10).Column).Value
For i = 1 To Num
If FirstMax <= Cells(ActiveCell.Row, i + 1).Value Then
FirstMax = Cells(ActiveCell.Row, i + 1).Value
Else
FirstMax = FirstMax
End If
Next i
End If

'Finding Max as required
If Max < 1 Then
MsgBox "Please provide valid input!"
Exit Function
Else
Counter = 1
For j = Num + 1 To 10
If Cells(ActiveCell.Row, j) > FirstMax Then
Counter = Counter + 1
FirstMax = Cells(ActiveCell.Row, j)
If Counter > Max Then
Exit For
End If
Else
FirstMax = FirstMax
End If
Next j
End If

OptimumValue = FirstMax

End Function


On second thoughts, did you give it a try in VBA before asking on VBAX?

Hope this helps,

Bob Phillips
06-17-2010, 01:00 AM
Here is another



Public Function OptimumHighest(Numbers As Variant, NumPass As Long, MaxPass As Long)
Dim aryNumbers As Variant
Dim TestMax As Double
Dim MaxPassCount As Long
Dim i As Long, j As Long

If Not (NumPass > 0 And MaxPass > 0) Then

OptimumHighest = CVErr(xlValue)
Exit Function
End If

aryNumbers = Application.Transpose(Application.Transpose(Numbers))
For i = LBound(aryNumbers) To NumPass

If aryNumbers(i) > TestMax Then TestMax = aryNumbers(i)
aryNumbers(i) = 0
Next i

For i = NumPass + 1 To UBound(aryNumbers)

If aryNumbers(i) > TestMax Then

MaxPassCount = MaxPassCount + 1
If MaxPassCount = MaxPass Then

OptimumHighest = aryNumbers(i)
Exit Function
Else

TestMax = aryNumbers(i)
End If
End If
Next i

End Function


You would have the 10 n umbers in A2:J2, and the two parameters in L2, M2, and call as =OptimumHighest($A$2:$J$2,L2,M2), or even as =OptimumHighest({5,1,3,7,2,6,4,9,8,10},L2,M2)

thexside
06-17-2010, 04:01 AM
Thanks guys, I tried in VBA but I really suck at it:|, I forgot to say there is a tricky part, let's say the list of numbers starts with 10:

10, 5, 1, 3, 7, 2, 6, 4, 9, 8

(1,1) strategy: you let the first one go and choose the first maximum, as there is no number bigger than 10 you choose the last one 8.
......etc

Similarly:
5, 1, 3, 7, 10, 2, 6, 4, 9, 8

(1,3) strategy: you let the first one go and as there is no bigger than the third one you choose the last, 8.

Bob Phillips
06-17-2010, 05:02 AM
Public Function OptimumHighest(Numbers As Variant, NumPass As Long, MaxPass As Long)
Dim aryNumbers As Variant
Dim TestMax As Double
Dim MaxPassCount As Long
Dim i As Long, j As Long

If Not (NumPass > 0 And MaxPass > 0) Then

OptimumHighest = CVErr(xlValue)
Exit Function
End If

aryNumbers = Application.Transpose(Application.Transpose(Numbers))
For i = LBound(aryNumbers) To NumPass

If aryNumbers(i) > TestMax Then TestMax = aryNumbers(i)
aryNumbers(i) = 0
Next i

For i = NumPass + 1 To UBound(aryNumbers)

If aryNumbers(i) > TestMax Then

MaxPassCount = MaxPassCount + 1
If MaxPassCount = MaxPass Then

OptimumHighest = aryNumbers(i)
Exit Function
Else

TestMax = aryNumbers(i)
End If
End If
Next i

If IsEmpty(OptimumHighest) Then OptimumHighest = aryNumbers(UBound(aryNumbers))
End Function

thexside
06-17-2010, 02:12 PM
Thank you guys

Simon Lloyd
06-23-2010, 12:21 AM
Apparently guys this is a university assignment, i'm locking the thread as it has been requested for deletion, this is my only course of action until i can verify it's origin.

@thexside, as per our forum rules you must declare when the question relates to homework/assignments, please bear this in mind for the future!

Bob Phillips
06-23-2010, 12:45 AM
I would think it would be pointless to delete it now. If it is homework, the perpetrator would have gotten what he/she wanted and be long gone, so you will just be removing a couple of good solutions from the archives.

There is no point slapping a shadow.

Simon Lloyd
06-23-2010, 09:25 AM
Sorry Bob it was in response to this
http://www.vbaexpress.com/forum/showthread.php?goto=newpost&t=32715

Simon Lloyd
06-23-2010, 11:06 PM
Crossposted:
http://www.ozgrid.com/forum/showthread.php?t=143747&page=2
http://www.mrexcel.com/forum/showthread.php?t=475822