How to use ARGMAX() statistical funtion

[Anuator]

Hello everyone!!!

I want to ask that how to use the formula of ARGMAX(lables,values,testcells,criteria).
I have installed the simtools add-in but i am not sure how will this formula work. Can anyone give me some example or any reference from where i can find some example about the syntax of this formula.

Thanks alot.

 

[Scott Huish]

Hi, welcome to the board! If you look at the site where you downloaded that add-in, they have a link to the book you can buy that explains it.

 

[Anuator]

Actually I also got the book but so far i only found the same formula with very little explanation and no example...So if anyone can help me it would be great and in the mean time I will keep searching .... Anyway thanks a lot n TC.

 

[Andrew Poulsom]

Here's an explanation:

ARGMAX(labels, values, testCells, criterion) returns a label from a cell in the labels-range that corresponds to a maximal value cell, among all the cells in th e values-range that correspond to test cells (if any) that match the criterion. The labels-range and the values-range must have the same number of rows and the same number of columns. The testCells and criterion are optional. If they are included, then the testCells must be a range that has the same number of rows a nd columns as the labels-range and values-range. Sophisticated criteria with "* " as a wild card may be used, as in Excel's COUNTIF function.

And here's the VBA code for the function:

Function ARGMAX(labels As Object, values As Object, Optional testCells, Optional criterion)
    Dim i As Integer, j As Integer, k As Integer, r As Integer, c As Integer, y As Double, x As Variant, crit As Variant
    On Error GoTo 5
    r = labels.Rows.Count
    c = labels.Columns.Count
    If (values.Rows.Count <> r Or values.Columns.Count <> c) Then GoTo 5
    If IsMissing(testCells) Then
        For i = 1 To r
            For j = 1 To c
                y = values.Cells(i, j).Value
                If IsEmpty(x) Or y > x Then
                    x = y
                    ARGMAX = labels.Cells(i, j).Value
                End If
            Next j
        Next i
        Exit Function
    End If
    If (testCells.Rows.Count <> r Or testCells.Columns.Count <> c Or IsMissing(criterion)) Then GoTo 5
    crit = criterion
    For i = 1 To r
        For j = 1 To c
            If Application.CountIf(testCells.Cells(i, j), crit) = 1 Then
                y = values.Cells(i, j).Value
                If IsEmpty(x) Or y > x Then
                    x = y
                    ARGMAX = labels.Cells(i, j).Value
                End If
            End If
        Next j
    Next i
    If IsEmpty(x) Then ARGMAX = CVErr(xlErrNull)
    Exit Function
5     ARGMAX = CVErr(xlErrValue)
End Function

 

[Anuator]

Thank you sir... I am trying to solve this now.

I will tell you about the results. Actually I am trying to find the equilibrium

point which is represented as the difference two values given as

Equilibrium = Argmax(Agreement - Disagreement)

Hope to solve this problem soon !!!

 

[Anuator]

To be more precise, it is given as

Equilibrium = Argmax ((a1-d1)(a2-d2)(a3-d3))

Thanks!!!

 

[Andrew Poulsom]

I don't think that function is going to help you.

 

[Anuator]

Yes you are right!!! It always gives error... Do you have a some other suggestion????

 

[Andrew Poulsom]

What do you want the formula to return? I don't know what you mean by equilibrium.

 

[Anuator]

Here equilibrium means a point where all the values are satisfied. It is the point where every player considers itself to have achieved enough for itself. This is basically the concept of Nash Equilibrium from Game Theory.

So we require a point where most of the players have their maximum share. The argmax() term appears in many research papers and is expected to reach the desired solution point i.e. maximum of all the three players each for the above example. This will result in three values, one for each player giving optimal points for each player.