# Thread: Find max based on condition (Excel 2003)

1. ## Find max based on condition (Excel 2003)

How can I determine what is the maximum year from a list if only some of the years are to be considered? The # of years is fixed at 7, but whether or not any given year is included is up to data on other sheets. The data looks like this:
<pre>EndYear Include?
2006 1
2008 1
2009 0
2011 1
2012 0</pre>

So here I'd have two of the years (rows) not included in the MAX calculation (the 1/0 is a flag that is calculated based on other conditions in the workbook). MAX() by itself does't work as it includes all the years (the 0-years). What's the trick here I'm missing?

Thnx, Deb <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

2. ## Re: Find max based on condition (Excel 2003)

Perfect! I knew it was simple, but couldn't see it. I always forget about array formulas too.

Thnx, Deb

3. ## Re: Find max based on condition (Excel 2003)

try

=MAX(A1:A7*B1:B7)

entered as an array formula (Ctrl-Shift-Enter). It works for me, as attached

Edited to change attached workbook - the original version contained some macros from a project I was working on. Nothing dangererous, but distracting...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•