Max function on header row to roll up max date of contiguous range immediately following the header row

excelpinto

Board Regular
Joined
Feb 14, 2008
Messages
53
Hey all,

I'm trying to come up with a max function that will be used on a header row that will roll up the max date of contiguous range immediately following the header row

Here is some sample data

Col A Col B
1 Max Date
1 5/1/2010
1 5/10/2010
1 6/1/2010
2 Max Date
2 5/5/2010
2 5/15/2010
2 6/1/2010


this is in cell b1
=max(B2:??)
where ?? = the last row where col a = 1 so the formula would return max(b2:b4) but I'm having trouble returning b4. I'm not quite sure how to integrate a match or if function into the second half of the max (after b2).
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

excelpinto

Board Regular
Joined
Feb 14, 2008
Messages
53
One way:

=MAX(IF(A$2:A$8=A2,B$2:B$8))

confirmed with Ctrl+Shift+Enter.
Awesome. That works. THANKS! On to another complication:

The keys in column A are text values, in this format:
<table x:str="" style="border-collapse: collapse; width: 129pt;" border="0" cellpadding="0" cellspacing="0" width="172"><tbody><tr style="height: 12.75pt;" height="17"></tr><tr style="height: 20.25pt;" height="27"><td class="xl25" style="height: 20.25pt; width: 48pt;" x:num="" width="64" height="27">1</td><td class="xl26" style="width: 81pt;" x:err="#VALUE!" align="center" width="108">#VALUE!
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">1.1</td><td class="xl24" x:err="#VALUE!" align="center">#VALUE!</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.1.1</td><td class="xl23" x:num="40360" align="right">7/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.1.2</td><td class="xl23" x:num="40391" align="right">8/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">1.2</td><td class="xl24" x:err="#VALUE!" align="center">#VALUE!</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.2.1</td><td class="xl23" x:num="40452" align="right">10/1/2010</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">1.2.2</td><td class="xl23" x:num="40483" align="right">11/1/2010</td></tr></tbody></table>

The idea is that the '1' header row rolls up all the 1.*.* rows. The '1.1' header row only rolls up the 1.1.* rows. The '1' row is easy and i have that working with =MAX(IF(LEFT(A2:A1000,1)=LEFT(A1,1),B2:B1000)) But I'm struggling with coming up with a working formula for the '1.1' roll up of '1.1.*' I need to use some string functions that will break apart the keys in column a as they can be >2 characters sometimes (like 1.10.10) so I cant just use a straight left,3 function to return it like like I do for the '1' roll up.
This is what I've got so far for the '1.1' rollup:

=MAX(IF(LEFT(A3:A1000,FIND(".",A3:A1000,3)-1)=A2,B3:B1000)) and I'm getting a VALUE error.

Basically i'm looking to return the max of rows below the 1.1 row where their key is 1.1.*. My string function =LEFT(A3,FIND(".",A3,3)-1) appears to work by itself and correctly return only the 1.1 value but when i plug it into the array it returns VALUE Any ideas?

<table x:str="" style="border-collapse: collapse; width: 129pt;" border="0" cellpadding="0" cellspacing="0" width="172"><col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 20.25pt;" height="27"> <td class="xl25" style="height: 20.25pt; width: 48pt;" x:num="" width="64" height="27">
</td> <td class="xl26" style="width: 81pt;" x:err="#VALUE!" align="center" width="108">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl24" x:err="#VALUE!" align="center">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl23" x:num="40360" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40391" align="right">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl27" style="height: 12.75pt;" height="17">
</td><td class="xl24" x:err="#VALUE!" align="center">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40452" align="right">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" style="height: 12.75pt;" height="17">
</td><td class="xl23" x:num="40483" align="right">
</td></tr></tbody></table>
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,943
Messages
5,508,264
Members
408,673
Latest member
CELER_

This Week's Hot Topics

Top