Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
  •