To find the max value + Solved/Closed

Question

Hello,

   Hi need to get the maximum value for certain range of cells.
     and the value of the cell next to that max cell.
 can any one help

from 
mukil

venkat1926 · Accepted Answer

suppose the data is from A1 to A9

in any cell paste this formula 

=INDIRECT("B"&MATCH(MAX(A1:A9),A1:A9,0))

modify the formula to suit your data 

fill some day(numbers) in  a sample sheet A1 to A9 and B1 to B9 and test the formula and then only modify it to suit you.

venkat1926 · Answer

You have not explained properly You must give the logic behind your needs. 
maximum of four cells in which column and also which four cells.
you have two columns A and B.
coumn A has 8 cells, B has 8 cells. which is the maximums value, is it 10 in A1 or 4 in B1.  

I suppose your logic is something like this.
you  take the first four rows.  In this subset in column A. the maximum is 20. the next cell value for 20 is 6. so 6 is to be added in C1 (that is the first row of the subset of column A.
similarly the second subset is 5th to 8 rows. here the maximum of column A in this subset is 52 which has next cell is 3. this 3 is be in column c in the fifth row (the first subset) in column C

next subset is rows 9 to 12 and goes on 

Kindly confirm

venkat1926 · Answer

Of course i can have a formula which is becoming cumbersome and complex. so I have given a macro.  

Sub test()
Dim j As Integer, k As Integer, r As Range, x As Double, rmax As Range
Worksheets("sheet1").Activate
Columns("C:C").Delete
j = Range("A1").End(xlDown).Row
For k = 1 To j Step 4
Set r = Range(Cells(k, 1), Cells(k + 3, 1))
x = WorksheetFunction.Max(r)
Set rmax = Cells(WorksheetFunction.Match(x, r, 0), "b")
Cells(k, "C").Value = rmax
Next k
End Sub


suppose there are two maximums in a subset e.g. a1 to a4 then it will take the corresponding B value only for the first maximum .

venkat1926 · Answer

Match(x, r, 0)
Set r = Range(Cells(k, 1), Cells(k + 3, 1))


in the fisrt loop  r is A1 to A4
x is the maximum of r
match function finds out the row no. in r of the value x
(o the third argument in match function is getting exact value)
then if find out the value of B in that row.

In the subsequent loops there is a small problem when you come to A5 to A8 it will only find the row no. within that range namely A5 to A8 .  but what we want the the row number of the overall A range.  so that addition of k-1 in the 
set rmax=.....................

I hope I made it clear. see help under match in the worksheet.  then do some experiments. you will get the hand of it. I am happy you do not want to blindly copy the macro and use it.  You want to find out the logic. That will help you to write macros. greetings for happy new year.   If you have doubts post back.

venkat1926 · Answer

I shall tell you how to modify the macro 
copy the original macro safely somewhere so that it is not messed up 

in the worksheet 
large(a1:a6,2) 
will give you the second maximum n A1 to A6
but in vba youi have to use "worksheetfunction "

dim y as double 
y=worksheetfunction.large(r,2)

something like this. can you now try to modify the macro. 

If there is problem you are free to repost

Manish Bakde · Answer

give the formula =large(Range,no)
for example from a range of a1 to f20 first largest value the formula is
=large(a1:f20,1)
for second largest value
=large(a1:f20,2)
for third largest value
=large(a1:f20,3)

To find the max value +

6 responses

Subscribe To Our Newsletter!