Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

up vote 4 down vote accepted

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

=INDEX(A63:A91,MATCH(MAX(IF(D63:D91<0,D63:D91)),D63:D91,0))

, 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:

{=MIN(IF(D63:D91<0,A63:A91,""))}

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:

{=INDIRECT("A"&MIN(IF(D63:D91<0,ROW(63:91),"")))}

or, if the A column needs to be dynamic:

{=INDEX(A$1:A91,MIN(IF(D63:D91<0,ROW(63:91),"")))}

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

You must log in to answer this question.

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