To find the max value + [Solved/Closed]

Mukil - Jan 1, 2010 at 06:16 AM - 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 

14 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 1, 2010 at 09:11 PM
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.

Thank you, venkat1926 7

Something to say? Add comment

CCM has helped 1682 users 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..
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 6, 2010 at 05:44 AM
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
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 6, 2010 at 06:51 PM
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.
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 2, 2010 at 06:23 AM
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 ...
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 4, 2010 at 08:11 PM
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen > mukil - Jan 5, 2010 at 08:44 PM
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
mukil > venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 5, 2010 at 11:37 PM
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
Manish Bakde - Aug 11, 2010 at 06:31 AM
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)