Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

add comment

3 Answers

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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