HELP TO FIND MAXIMUM VALUE

[Guest]

I just put this formula to find maximum value

=if(and(s19>0,b8..b14000=b19),max(t8..t14000),0)

I want to check if value in s19 > 0 then
find the range equal to value in b19 in b8..b14000 then
find parallel range in t8..t14000 then
find maximum value in that range

That is value in b19 = 2, which there in b19 to b27 (b19 = 2, b20 =2, ....)
Now there is value in t19 is 25, t20 is 45, t21 is 5, t22 is 100... t27 is 75
maximum value between t19 to t27 is at t22 100, which is the value I require.

I want to copy the formula you give thro u8 ... u 14000
so that I get max value in "U" range.

my advance thanks to you..

 

[JE McGimpsey]

One way, if I understand you correctly (array entered: CTRL-SHIFT-ENTER
or CMD-RETURN):

=IF(S19>0,MAX(($B$8:$B$14000=B19)*($T$8:$T$14000)))

 

[Guest]

Hi JE McGimpsey,
It's amazing.. the "command-return" worked.
Nice week-end to you.
thanx a million.
Eddy Stan

 

[Guest]

Hi,
Sorry.. some how the data I had max values in the 1st row, so I thought that
the function is giving correct result. But after 500 rows the max value is in
between the range and our function giving the value at 1st row of the range.
I tried like MAX( ) as:
=IF($S$8:$S$11144>0,MAX((($B$8:$B$11144)=B117)*($T$8:$T$11144)),0)
result 1st row value that is value at T117 is shown.
I tried like LARGE( ) as:
=IF($S$8:$S$11144>0,LARGE((($B$8:$B$11144)=B142)*($T$8:$T$11144),1),0)
result 1st row value 176 is returned, that is value at T142 is showing.
But I need to see max value 223 between the range T142:T156 at T144 (max 223
is there in T144.T145, T146 & T147)

So can you try with Large( ) function, where we can say 1st large

I am trying to send a portion (200 rows) of my mega file, kindly study &
give me the function.

thank you in advance.