Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP TO FIND MAXIMUM VALUE
One way, if I understand you correctly (array entered: CTRL-SHIFT-ENTER
or CMD-RETURN): =IF(S190,MAX(($B$8:$B$14000=B19)*($T$8:$T$14000)) ) In article , Eddy Stan wrote: I just put this formula to find maximum value =if(and(s190,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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP TO FIND MAXIMUM VALUE
Hi JE McGimpsey,
It's amazing.. the "command-return" worked. Nice week-end to you. thanx a million. Eddy Stan "JE McGimpsey" wrote: One way, if I understand you correctly (array entered: CTRL-SHIFT-ENTER or CMD-RETURN): =IF(S190,MAX(($B$8:$B$14000=B19)*($T$8:$T$14000)) ) In article , Eddy Stan wrote: I just put this formula to find maximum value =if(and(s190,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.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP TO FIND MAXIMUM VALUE
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$111440,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$111440,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. "JE McGimpsey" wrote: One way, if I understand you correctly (array entered: CTRL-SHIFT-ENTER or CMD-RETURN): =IF(S190,MAX(($B$8:$B$14000=B19)*($T$8:$T$14000)) ) In article , Eddy Stan wrote: I just put this formula to find maximum value =if(and(s190,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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
?How can I find the maximum column with condition | Excel Discussion (Misc queries) | |||
Find Maximum date | Excel Worksheet Functions | |||
find maximum | Excel Discussion (Misc queries) | |||
find which column has the maximum value | Excel Discussion (Misc queries) | |||
Find row number of maximum value | Excel Programming |