To find the max value + [Solved/Closed]

- - Latest reply:  Manish Bakde - Aug 11, 2010 at 06:31 AM
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
See more 

6 replies

Best answer
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
930
7
Thank you
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.

Say "Thank you" 7

A few words of thanks would be greatly appreciated. Add comment

CCM 3194 users have said thank you to us this month

Thanks a Lot venkat.
i need some more help form u.
actually i have a set of values in a column and i have 2 compare only by four,four cells and find the max & the value next to that cell.

i.e
@ my excel i have
A B C
10 4 6
20 6
15 7
05 9
45 1 3
35 5
52 3
20 4 ..... like that goes on..
I need the values in column "C" as i specified..
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
930
1
Thank you
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.
Thanks a lot. Happy New Year - Have a Honey Year
Now its very clear for me. thanks..
Sure i do try to build some macros.
I need some more help for u.
same thing continues..
i need to find the second max value + value adjacent to it - within the same range.
do the needful

Mukil
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
930
1
Thank you
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
Thank u very much.
Ya i build the macro to find second max value & so on..
thanks..
U used a command to clear column "C" .
Columns("C:C").Delete
i wanna to clear Column D,E,F while staring macro
i used the same as
Columns("D:D").Delete
Columns("E:E").Delete

but its not clearing the value instead it shift the values to previous column.
do the needs

Mukil..
Hi
Venkat i got the function to delete
thanks ..take care.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
930
0
Thank you
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
Hi Venkat

u r correct.. i need the same

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 help me in this ...
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
930
0
Thank you
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 .
Hi Venkat
Thanks for ur macro
but its not working properly.
for first set its fine - gives correct value
from the second set(ranges(5:8) ) onwards - its give the value of the fourth row.
the max is working fine .the probs. with the match function.

the result of my excel

A B C
10 1 2
60 2
30 3
40 B
50 5 B
60 6
70 7
80 8
90 9 B
100 10
110 11
120 12
130 13 B
140 14
150 15
160 16
170 17 B
180 18
190 19
200 20
-- look into it....
venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
930 > mukil -
yes there is a small error in the macro . sorry. try this revised macro
I have indicated where the modification is done in the macro as a comment.\\
please confirm whether it works ok.

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))
'msgbox r.Address
x = WorksheetFunction.Max(r)
'msgbox x
Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b")
'the modification is in the above line 
'msgbox rmax.Address
Cells(k, "C").Value = rmax
Next k
End Sub
> venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hi Venkat
Thanks a lot.
Its working fine.
can u plz explain the match function

Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b")

i can able to get other functions.

from
mukil
0
Thank you
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)