1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excel 2013 "conditional range"

Discussion in 'Business Applications' started by andrew1968, Jul 3, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. andrew1968

    andrew1968 Thread Starter

    Joined:
    Jul 3, 2014
    Messages:
    11
    I have a series of sequential dates in column A
    In column B I have numerical data
    In column C I would like to look at the previous year as determined by column A and the result would be the highest number in that range from column B.

    The period in column A I'm looking at constantly changes as I move down the spreadsheet because the dates change. I think I may use the LOOKUP OR VLOOKUP function to get my result after defining the range.

    Can you help me define the range for the lookup as a formula. (i.e A255:A255- one year)

    Thanks! I hope this is clear.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    perhaps you could post a sample spreadsheet with the data - include the expected results - so we can see what you are trying to achieve
     
  3. andrew1968

    andrew1968 Thread Starter

    Joined:
    Jul 3, 2014
    Messages:
    11
    Here is a sample file of what I'm looking for. Thanks everyone! This is my first time here and if I get an answer that would be great!!!!
     

    Attached Files:

  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    what range would you use

    =VLOOKUP(A253-365,$A$1:$B$252,2,FALSE) looks up the date
    Use
    =VLOOKUP(A253-365,$A$1:$B$252,2,TRUE) to get the nearest lowest date (as not all dates are in the list

    But what is the max you are looking for ?
     
  5. andrew1968

    andrew1968 Thread Starter

    Joined:
    Jul 3, 2014
    Messages:
    11
    So I'm not wanting to look up a date.

    For example whichever line I'm on I will go to column A and see what date that is. That will be the end date of my search the beginning date of my search will be one year prior to that (going up column A until I get to one year earlier). Once I have the date range established then I will look in that same range in column B for the MAX number.

    does that make sense?
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    I think this will work
    =MAX(INDIRECT(((ADDRESS(MATCH(A253-365,$A$1:A252,1),2)&":"&ADDRESS(ROW()-1,COLUMN()-1)))))

    I entered into column C253 and copied down
     
  7. andrew1968

    andrew1968 Thread Starter

    Joined:
    Jul 3, 2014
    Messages:
    11
    Thank you... I just made one small change it it was perfect.

    =MAX(INDIRECT(((ADDRESS(MATCH(A253-365,$A$1:A252,1),2)&":"&ADDRESS(ROW(),COLUMN()-1)))))

    I wish I knew excel that well. Any suggestions on how to learn more of these advanced formulas?

    Anyway, I'm supper happy THANK YOU!!!!!!
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    your welcome,
    there maybe a better way to do it.

    i just answer questions in some of the excel forums and then research the question to see how to solve
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    If I understood you correctly, you could also us this array formula, as an alternative to using the INDIRECT function (I just generally try to stay away from it, since it's volatile and expensive, in terms of recalc time)...

    Code:
    =IFERROR(MAX(IF((A$1:A1>=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)))*(A$1:A1<=A1-1)*(MIN(A$1:A1)<=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))),B$1:B1)),"")
    The formula would be entered into cell C1 and confirmed with CTRL+SHIFT+ENTER instead of just ENTER, since it's an array formula. Copy down.

    This does get an offset result of the previously posted formula, in that it looks for everything before the date in the row. So it will be in the previous year (rolling) not including the date of the cell in column A.

    I haven't run any speed tests, but I'd wager a guess that the array formula would probably beat the other in terms of recalc over several iterations. A 101 ways to skin the proverbial Excel cat. ;)

    HTH
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1128962

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice