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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.