Returning Values base on a series and the highest in the series

[liverebel]

In the attached here is what im trying to do. Column 1 [Series#] contains number series that are based on year eg 17 - and then a reference number. After the reference number is another series number preceded by a period. The number that comes after the period is a series reference number with in the main series number. So 17-0012 contains 17-0012.1 17-0012.2 17-0012.3 17-0012.4 etc. I would like column C [Top Series] to return the some indication that number ending the series is the highest number in that series. So in the example of 17-0012 which contains 4 sub series I would like column 3 to put an "x" or the number "17-0012.4" ONLY for the number that is the highest in that series leave 17-0012.1 17-0012.2 17-0012.3 blank. Just some way to indicate in column C that .4 is the highest within the 17-0012 series. Thanks!

[Special-K]

Considerations:
17-0012.1

Is that 17-0012.10 or 17-0012.1 ?
If the series after the decimal point is going to go beyond .9 then you MUST rename the digits from 1-9 as .01 .02 .03 etc or you wont be able to distinguish 17-0012.1 from being point one or point ten.

[liverebel]

the series runs as .1 .2 .3 .4 .5 .6 .7 .8 .9 .10 .11 etc

[Special-K]

Then you may need to change them to

.01 .02 .03 .04 etc

or .1 may get picked up as .10

[liverebel]

copy that. Do you have a way to accomplish what I'm referencing if I do?

[Special-K]

Nope, it was just an observation.

[liverebel]

What about if I split the reference number into 3 distinct series like this?

[Special-K]

That helps immensely.
Just make sure for column D you enter ones as 1 and tens as 10

in F2
=IF(MAX(IF((B$2:B$17=B2)*(C$2:C$17=C2),D$2:D$17))=D2,"x","")
Array formula, use Ctrl-Shift-Enter
and copy down the column

So for each group of B and C return the highest number for column D. If this number matches the value currently in D of this row then this is the maximum number within that group (of B and C) so output an "x" otherwise output null.

[menem]

I try this formula on E2 (outside table area)

Please Login or Register  to view this content.

Press ctrl-shift-enter for array formula.

But can not put it in table area T_T.

Regards.