Geek asks and answers

Excel formula with a minimum value

EDIT: Probably a simple question, basically I have three values, say 9, 4 and 7 in different cells ( not in the table) . I want to find the minimum in order, then return its relative position, for example: 9,4,7 will return 2, since 4 is the minimum and will be the second.

What is the correct excel formula for this? Thank you

Solution: Thanks for your help, I did not think that Excel could do this if it is not continuous. I ended up using a series of nested IF statements, AND operator and <signs to do the job :)

0
source share
5 answers

Here is an array formula that should work. He assumes the values ​​are in A1, C3 and B4.

=MAX(IF(CHOOSE(TRANSPOSE({1,2,3}),$A$1,$C$3,$B$4)=MIN($A$1,$C$3,$B$4),TRANSPOSE({1,2,3}),0))

+2

:

=ROUND(MIN(A1,B1,C1), 0)

Ah ;

, VBA (, + )

=OrdinalMin(A2,B2,C2)

//in a module
Public Function OrdinalMin(ParamArray cells() As Variant) As Long
Dim i As Long, min As Double
For i = 0 To UBound(cells)
    If cells(i) <= cells(min) Then min = i
Next
OrdinalMin = min + 1
End Function
+1

min: =MIN(A1,B2,C3)

: =MATCH(2,1/FREQUENCY(min,(A1,B2,C3)))

+1

, Excel .

- :

Public Function Match2(ByVal What As Variant, ByVal Where As Range) As Long
  Dim a As Range
  Dim c As Range
  Dim g As Long

  For Each a In Where.Areas
    For Each c In a.Cells
      g = g + 1

      If c.Value = What Then
        Match2 = g
        Exit Function
      End If
    Next
  Next
End Function

:

=Match2(MIN(B6,F9,I16),(B6,F9,I16))
0

:

{=SUM((B$16:M$16)*(B23:M23=MIN(B23:M23)))}

B$16:M$16 (.. 1, 2, 3, 4 ..), B23:M23 .

0

More articles:


All Articles