Take the 2-minute tour ×

I have an excel table that looks like this:

Date         Original       Actual
11/10/10     15             12
12/10/10     12             10
24/10/10     2              1
25/10/10     -2             0

I want to know the date at which the original column becomes negative, 25/10/10 in this example.

I've managed to figure out the value using this formula: =MAX(IF(D63:D91 <0,D63:D91)) which tells me -2.

I just want it to look at the date column where the original value = -2 and tell me that date, but I can't figure out a way to do this at all!

Anyone have any ideas?

share|improve this question

migrated from stackoverflow.com Oct 20 '10 at 11:23

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 4 down vote accepted

Since LOOKUP is no good because it takes an ordered set, that'd be


, entered as an array formula (Ctrl+Shift+Enter) (provided column A has dates).

share|improve this answer

Unfortunately GSerg solution does not work if there are other negative values greater than -2, for instance -1, further down the table.

To identify the first instance of a negative value (according to the date), and the related date, I'd suggest:


Note that the dates in column A do not need to be sorted.

If instead one needs the very first instance of a negative entry regardless of the date, then I'd suggest:


or, if the A column needs to be dynamic:


hope this helps.

share|improve this answer

I would add a hidden column in front of the dates, putting with an if a 1 if the previous value in the original column is positive and the current value in the original column is negative, putting a 0 otherwise.

And perform a vertical lookup on 1 in the new column.

I find that very often a hidden column makes the code much more clearer (in stead of some very long formula). I am a programmer, non-programmers tend to use very long functions making it much harder to maintain.

share|improve this answer

Your Answer


