PDA

View Full Version : Max Formula



lukes123
12-17-2008, 01:26 PM
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
12-17-2008, 02:47 PM
Try

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

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

lukes123
12-17-2008, 03:00 PM
How can I customize this for my workbook? Could you explain each part of the formula?

Thanks
Luke

Aussiebear
12-17-2008, 03:04 PM
Look up both INDEX & MATCH in the Help section.

Mahahaava
12-17-2008, 08:12 PM
Hi,

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

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
HTH

Petri

11182