formula to get max from range selected by criteria

[r1998]

Dear Friends and Respected Seniors,
i have this data in sheet2 starting from cells a1:b9
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]8[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]j[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]u[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]i[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]

I want to get the value from column B when value in column A is character b...... so the value will be 5
then I want to get the value from column B when value in column A is character j...... so the value will be 3
then i want to get the maximum value of this range starting from 5 and ending at 3....
the range will be
5
2
4
3
and its Maximum value will be 5


I am able to do this with VBA code,
but I want to do this with a formula n i am not able to figure out how to do it :|

Sub e()
Dim i As Integer, a As Integer, b As Integer


For i = 1 To Sheet2.Range("b1").End(xlDown).Row
If (Cells(i, 1) = "b") Then
a = i
End If


If (Cells(i, 1) = "j") Then
b = i
End If


Next


Sheet2.Range(Cells(a, 2), Cells(b, 2)).Select
Sheet2.Range("c1") = Application.Max(Sheet2.Range(Cells(a, 2), Cells(b, 2)))


End Sub

Please can anyone kindly help and guide me
Awaiting your replies.
Thank you.

 

[Aladin Akyurek]

With F1 = b, etc., try in G1:

=MAXIFS($B$1:$B$9,$A$1:$A$9,$F1)


If you don't have the MAXIFS function, in G1 control+shift+enter, not just enter:

=MAX(IF($A$1:$A$9=$F1,$B$1:$B$9))

 

[r1998]

Thank you Aladin Akyurek Sir
I am using excel 2010 so maxif function is not available and i m using excel after many months, so finding it difficult to use formulas
but after thinking for almost an hour, i was able to do it like this

=MAX(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,MATCH(A5,A:A,0)))

Thank you.

 

[Aladin Akyurek]

Thank you Aladin Akyurek Sir
I am using excel 2010 so maxif function is not available and i m using excel after many months, so finding it difficult to use formulas
but after thinking for almost an hour, i was able to do it like this

=MAX(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,MATCH(A5,A:A,0)))

Thank you.

Just use the second option...

=MAX(IF($A$1:$A$9=$F1,$B$1:$B$9))

 

[Yongle]

another formula to try (not as succinct, but seems to work)

=MAX(OFFSET(B1,MATCH("b",A:A,0)-1,0,MATCH("j",A:A,0)-MATCH("b",A:A,0)+1))

 

[r1998]

Thank you Aladin Akyurek Sir and Yongle Sir
Both formulas working fine

Aladin Akyurek Sir, i am using your formula like this

=MAX(IF($A$1:$A$9=A2,$B$1:$B$9),IF($A$1:$A$9=A5,$B$1:$B$9))

this is giving me max value of 2 numbers which are in cells B2 and B5,
but i m searching for max value between range b2:b5
I replaced , with : in formula, but its not working

=MAX(IF($A$1:$A$9=A2,$B$1:$B$9):IF($A$1:$A$9=A5,$B$1:$B$9))

any idea how to make this work ?
Thank you.

 

[Aladin Akyurek]

Thank you Aladin Akyurek Sir and Yongle Sir
Both formulas working fine

Aladin Akyurek Sir, i am using your formula like this

=MAX(IF($A$1:$A$9=A2,$B$1:$B$9),IF($A$1:$A$9=A5,$B$1:$B$9))

this is giving me max value of 2 numbers which are in cells B2 and B5,
but i m searching for max value between range b2:b5
I replaced , with : in formula, but its not working

=MAX(IF($A$1:$A$9=A2,$B$1:$B$9):IF($A$1:$A$9=A5,$B$1:$B$9))

any idea how to make this work ?
Thank you.

Using A2 and A5 is not generic for these values are fixed parts of the data data range A1:B9.

By the way, is it possible for you to post A1:B9?

 

[r1998]

Hello Aladin Akyurek Sir,
data in a1 to b9 is
[TABLE="class: cms_table, width: 128"]
<tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]8[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]j[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]u[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]i[/TD]
[TD="align: right"]11

[/TD]
[/TR]
</tbody>[/TABLE]
In earlier formula, we had used A2 and A5, that is why there was some confusion, but
if we use cells A2 and A8 in formula, i want the maximum value of range from b2 to b8....
Your formula is getting the maximum of the 2 numbers present in B2 and B8, so answer is 47,
but i want the maximum value of range from b2 to b8.... so answer is 85...
Your formula works, but its comparing only 2 numbers present in b2 and b8 and giving its maximum value

=MAX(IF($A$1:$A$9=A2,$B$1:$B$9),IF($A$1:$A$9=A8,$B$1:$B$9))

but i want the maximum value of range from b2 to b8, I tried to use ur formula like this, but getting error

=MAX(IF($A$1:$A$9=A2,$B$1:$B$9):IF($A$1:$A$9=A8,$B$1:$B$9))

i dont know how to make this work, thank you for helping.

 

[Aladin Akyurek]

You must have your specifications outside of the range of interest.
Let's say that we have the following conditions:

D1 = b, E1 = u.

Book1
	A	B	C	D	E	F
1	a	8		b	u	85
2	b	5
3	c	2
4	h	4
5	j	3
6	k	85
7	y	65
8	u	47
9	i	11
Sheet1

In F1 enter:

=MAX(INDEX($B$1:$B$9,MATCH(D1,$A$1:$A$9,0)):INDEX($B$1:$B$9,MATCH(E1,$A$1:$A$9,0)))

 

[r1998]

Hello Aladin Akyurek Sir,
your formula working perfectly now

In other columns there is data, so i am using your formula like this

=MAX(INDEX($B$1:$B$9,MATCH(A2,$A$1:$A$9,0)):INDEX($B$1:$B$9,MATCH(A8,$A$1:$A$9,0)))

Thank you.