2

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?

3 Answers 3

4

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).

2

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.

1

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.

You must log in to answer this question.

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