1

In my example, the values and dates are listed in different columns. Also, I am trying to get the result to show the max value between the actual dates (rather than the dates that are listed in the cells).

For example, I have the dates in column AC and stock prices in column AG. I have column SU keeping track of the end date (which stays constant at 10 years ahead of the date in the current AC column). The SV column is where I have the formula. The formula for SV17249 is:

=MAX(IF((AC14739:AC17249<=SU17249)*(AC14739:AC17249>=AC14739),AG14739:AG17249,""))

I chose the range AC14739:AC17249 because it equals the approximate number of yearly trading days (x10) during a 10-year period (2,510). However, I am trying to find the max value between two dates that are precisely 10 years apart (like 1/1/2000 to 1/1/2010). But there are some years where there are more or less trading days than others, and that throws off the 2510 fixed range that I put into the formula.

Is there a way to have the formula above get the highest stock price in AG between the actual date in cell AC17249 and the date exactly 10 years prior to AC17249?

Improve this question
8
  • Why are you trying to change the range? Given you've got <= and >= it doesn't alter your results if you include a bigger range, I'm assuming you want it for the sake of efficiency
    – RickyTillson
    Commented Feb 1, 2019 at 11:43
  • Trying to limit date range to include dates from current cell going backwards to 1st date in the 1st cell of AC (dates for the spreadsheet are ascending). Also trying to do this without locking AC1 in the range ($AC$1:AC17249). If I do (AC:AC) for the range, it doesn't help because it will put in the max price of a future date. If ok with being off a few days every now and then, the formula works if I put in the cell with current date and date from 2,510 cells ago (AC14739:AC17249). But would be nice to always have result from exactly 10 years ago. Any ideas how to do that?
    – stckexhnge57
    Commented Feb 1, 2019 at 16:47
  • So I'm trying to replace (AC14739:AC17249) with something like (AC17429-3625:AC17249), where excel understands that I am saying the date range from the date in AC17429 minus 3625 days from that date to the date in AC17429.
    – stckexhnge57
    Commented Feb 1, 2019 at 17:59
  • If your database includes every trading day, go back 10 yrs (3652 days rather than 3625). If there is not an exact match in your table, select the closest table date (or a similar rule).
    – fixer1234
    Commented Feb 1, 2019 at 20:44
  • My apologies - I meant 3652. I tried to put in ((AC17249-3653):AC17249) for the range, but I get "the formula you entered contains errors" message. I'm guessing i have to do some kind of VLOOKUP or OFFSET or something, but I have not been able to figure it out. Anything come to mind?
    – stckexhnge57
    Commented Feb 1, 2019 at 22:54

1 Answer 1

Reset to default
1

The general approach in your formula is probably as good as any. It just needs tweaking to do what you want.

  • It looks like you have a ton of data, so it may speed things up if you limit the evaluated range. If there are roughly 2510 rows in 10 years, round up to a number guaranteed to include the 10 years, say 2600 rows. So the evaluated range for a given row will end at the given row and include the 2600 previous rows.

  • To within about a day, I believe, 10 calendar years will include 3652 days. That calendar starting date won't necessarily be a trading day, so we need to find the first trading day after that calendar date (you don't want to double-count the end dates; your range includes the end date, so 10 years before that would actually be the end date of the previous period).

I'll base my example on the end date in your question, which is contained in AC17249. That would put the associated start of the evaluation range at AC14649. If you hard-code the ranges as relative addressing, you should be able to copy the formula and have it properly adjust for other rows. It's hard to verify formulas without your massive spreadsheet, but I believe this should work:

{=MAX(IF((AC14649:AC17249>AC17249-3652),AG14649:AG17249,0))}

It's an array formula, so needs to be confirmed with Ctrl-Shift-Enter rather than Enter.

Improve this answer
7
  • It's getting closer - the formula you gave locks the rolling end and start dates to exactly 10 years apart, as desired. However, the value range AG still starts to reference values outside the 10-year period when you get several years past the start date. To also limit the AG value range to exactly 10 years, I tried {LOOKUP(AC17249-3652,AC17249:AC14649,AG17249:AG14649):AG17249} in the value range of the formula you provided. But excel says "formula contains errors" when I try that. Any way to lock the value range to exactly the same 10 year period as the rolling end and start dates?
    – stckexhnge57
    Commented Feb 2, 2019 at 19:22
  • @stckexhnge57, it occurred to me that you don't need to test for the range being <=AC17249 because it always is by virtue of the end date of the evaluation range being that date. So the formula could be reduced to =MAX(IF((AC14649:AC17249>AC17249-3652),AG14649:AG17249,0)). Verify that your row ranges match on AC and AG. You need to start with those being identical in the formula. If the formula is copied, the rows should always match. I tested this on a small dataset and the action does work. There isn't anything that should make the range references for the two columns different.
    – fixer1234
    Commented Feb 2, 2019 at 22:12
  • The 10 yrs is enforced by the IF test on AC. Only rows within that range are true, so those are the only values considered in AG.
    – fixer1234
    Commented Feb 2, 2019 at 22:14
  • 1
    Just in case you're curious - found a way to make it ALWAYS 10 years (taking even leap years into consideration, etc.): {=MAX(IF((AC14649:AC17249>EDATE(AC17249,-120)+1),AG14649:AG17249,0))} goes back 10 years without the guess work.
    – stckexhnge57
    Commented Feb 3, 2019 at 21:24
  • 1
    @stckexhnge57, clever. But you probably don't want the +1 since you're comparing with >.
    – fixer1234
    Commented Feb 3, 2019 at 21:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .