0

In my current Excel spreadsheet, I have the date in column A and S&P 500 closing prices in column B.

I'm trying to write an formula that returns the date for the last time that closing price was greater than or equal to today's closing price.

I've tried using SUMPRODUCT, MATCH, VLOOKUP, and INDEX but to no avail. Any suggestions?

Here's what my spreadsheet looks like:

Screenshot

I want a formula to go into cell B8.

  • Post up your work please. A screenshot and a text, formatted, that we can take a look at and pick apart. – ejbytes Oct 8 '16 at 0:13
1

A good first step is determining the row of the most recent closing price that was greater than or equal to today’s closing price.  Look at all the rows where the closing price was greater than or equal to today’s closing price.  In your sample data, they are rows 13, 14, 16, 17, 19, 20, 22, 23 and 24 (and presumably more, going further back in time).  Which one is the most recent?  It’s 13, because it is the smallest (minimum) row number in the list.

Now, let’s see if we can construct that list.  It seems like we want to evaluate

IF( Bn >= B12, n, (nothing) )

We can actually do something similar:

=MIN(IF(B13:B100>=B$12, ROW(B13:B100)))

replacing the 100 with a number that’s larger than the largest row number that might ever contain data.  (I imagine in your case that you could use 365, or 260 (52×5), or a multiple of one of those if you have multiple years’ worth of data.)

The above is an array formula.  Therefore, to get it to work, you must type Ctrl+Shift+Enter after you type the formula.

But you don’t want the row number; you want the data.  This part is easy:

=INDEX($A:$A, MIN(IF(B13:B100>B$12, ROW(B13:B100))))

Again, you must type Ctrl+Shift+Enter after you type the formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.