Max Formula

[lukes123]

Hi
I Know this is probably the wrong place to post this, but I have a quick question about formulae.

I have a table which has two columns, an item name column and a quantity bought column. What I want to do is return the item name to the cell which has the maximum number on the quantity bought column. Is there any way to do this?

Thanks
Luke

[Bob Phillips]

Try

=INDEX(A:A,MATCH(MAX(B:B),B1:B100,0))

as an array formula, commit with Ctrl-Shift-Enter, not just Enter

[lukes123]

How can I customize this for my workbook? Could you explain each part of the formula?

Thanks
Luke

[Aussiebear]

Look up both INDEX & MATCH in the Help section.

[Mahahaava]

Hi,

try looking at the attached xls -file. The following VBA -code is in Module FindMax in it(Run with Crtr+m):

[vba]Option Explicit
'This is a funtion to determine how many rows of data there is:
Function LastUsedRow(WB As String, S As Variant) As Long
'Find the last used Row on a ActiveSheet:
Workbooks(WB).Worksheets(S).Activate
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastUsedRow = LastRow
Else
LastUsedRow = 0
End If
End Function

Sub FindMaxValue()
Dim MaxProd As String' The most sold productname
Dim MaxQuantity As Long'The Max Quantity
Dim Row As Integer'The row where the MaxQuantity lies
Dim i As Integer'Running counter
For i = 1 To LastUsedRow("MaxQuantity.xls", 1)
If Worksheets("Sheet2").Cells(i, 2).Value > MaxQuantity Then
'If the value for row "i" is larger than any before, then
MaxQuantity = Worksheets("Sheet2").Cells(i, 2).Value
'set the value of the Max to this new value and
Row = i
'remember the rownumber
End If
Next i
MaxProd = Worksheets("Sheet2").Cells(Row, 1).Value
'The most sold product is on row Row, column 1
MsgBox "The most sold product is: " & MaxProd & ", " & _
MaxQuantity & " sold."
End Sub[/vba]
HTH

Petri

Attachment 11182