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).
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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>
 

Watch MrExcel Video

Forum statistics

Threads
1,133,320
Messages
5,658,133
Members
418,429
Latest member
Regila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top