Finding max array value of variable cell range

[The Fisherman]

Hi All,

I'm currently stuck in trying to find out to get a max array value
from a variable cell range,
the data is divided in 5 collums, and the variable cell range should
be dependent upon the first column. the maximum value should be
available from column c to e. the first colums has blanks in between
of variable spaces. I need to get the maximal amount of the array
based upon the id code in first column (including the row of the id
code, excluding the row of the second id code)...


example:


A B C D E
12341 data data data etc...


12341


42343


23432

etc....

does anyone had a clue how to fix my problem (offset, dmax or
anything) ?? I really don't know how to fix this problem,


thanks in advance!!


best regards

 

[Domenic]

If I understand you correctly, you'd like to search Column A for the
first occurrence of the specified ID Code, and return the maximum value
from the corresponding values in Columns C through Column E. If this is
correct, assuming that A2:E10 contains the data, try...

=MAX(INDEX($C$2:$E$10,MATCH(G2,$A$2:$A$10,0),0))

....where G2 contains the ID Code of interest. Adjust the ranges
accordingly.

Hope this helps!

 

[The Fisherman]

If I understand you correctly, you'd like to search Column A for the
first occurrence of the specified ID Code, and return the maximum value
from the corresponding values in Columns C through Column E. If this is
correct, assuming that A2:E10 contains the data, try...

=MAX(INDEX($C$2:$E$10,MATCH(G2,$A$2:$A$10,0),0))

...where G2 contains the ID Code of interest. Adjust the ranges
accordingly.

Hope this helps!






- Tekst uit oorspronkelijk bericht weergeven -

Thanks for your effort, the formula works but only for a single array,
and it won't adjust to the different amounts of blanks between the
column A "ID codes" Do you know how to fix this problem? the distance
is variable, so the array from which the max value should be given is
variable to...

i know it is difficult, can you follow my problem?

best regards,

W

 

[Domenic]

Still unclear. Does this return the desired result?

=MAX(IF($A$2:$A$10=G2,$C$2:$E$10))

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER. If this doesn't return the desired result, try posting
a small sample of your data, along with the expected result.

 

[Bernd]

Hello,

I am afraid I did not understand the "different amount of blanks" but:
Try array-entered
=MAX($C$1:$E$10*($A1:$A10=$H$1)-1E+307*($A1:$A10<>$H$1))
where H1 holds your ID.

If this does not solve your problem explain in more detail, please.

HTH,
Bernd

 

[The Fisherman]

Hello,

I am afraid I did not understand the "different amount of blanks" but:
Try array-entered
=MAX($C$1:$E$10*($A1:$A10=$H$1)-1E+307*($A1:$A10<>$H$1))
where H1 holds your ID.

If this does not solve your problem explain in more detail, please.

HTH,
Bernd

Hi guys, thanks for thinking with me!!
the problem is not solved yet, i'll try to make things clearer:

example:


A B C D E
12341 data data data etc...





12344

42343





23432




43453
etc.... (distances between the ID's are variable, and blank. the area
behind the ID's is full with data over the whole area)

The whole area behind the ID number (downwards over multiple rows)
should provide a MAX value, but the MAX area should stop at the next
ID value where a new MAX value is given. I have to repeat this
calculation over about 16000 rows, so a non-manual solution would be
preferable...
i hope i was able to make things clearer....

thanks in advance for your efforts!!

best regards!

Wouter

 

[Domenic]

Assuming that A2:E20 contains the data, let G2:G6 contain 12341, 12344,
42343, 23432, and 43453. Then try the following...

H2, copied down:

=MATCH(G2,$A$2:$A$20,0)

I2, copied down:

=MIN(IF(ROW($A$2:$A$20)-ROW($A$2)+1>H2,IF($A$2:$A$20<>"",ROW($A$2:$A$20)-
ROW($A$2)+1)))

....confirmed with CONTROL+SHIFT+ENTER.

J1:

=MAX(IF($C$2:$E$20<>"",ROW($C$2:$E$20)-ROW($C$2)+1))

....confirmed with CONTROL+SHIFT+ENTER

J2, copied down:

=MAX(INDEX($C$2:$C$20,H2):INDEX($E$2:$E$20,IF(N(I2),I2-1,$J$1)))

....confirmed with just ENTER

Adjust the ranges accordingly.

Hope this helps!

 

[Bernd]

Hello,

1. Create a helper column Z where you make cure that each row shows
the corresponding ID (and not blanks), kind of Z2:
=IF(ISBLANK(A2),Z1,A2)
2. Take my formula but use column Z instead of A

HTH,
Bernd

 

[The Fisherman]

Hello,

1. Create a helper column Z where you make cure that each row shows
the corresponding ID (and not blanks), kind of Z2:
=IF(ISBLANK(A2),Z1,A2)
2. Take my formula but use column Z instead of A

HTH,
Bernd

Hi All,

Thanks for the feedback! i found a solution using 2 helper colums:

first: =IF(F240<>"",E239+1,E239)
sec: =IF(E240=E239,D239+1,0)
result: =IF(D241=0,MAX(OFFSET(H240,0,0,-D240,72)),0)

thanks for your feedback!!

best regards,

W