Find the first value below zero in a column

[AlanFisher]

Good morning,

I have two columns of data - "C" contains the years from 2014, and "D" contains investment balances. I wish to find the first balance that falls below zero and return the year when that happens.

On the sample file are two examples. In A2:Q3 this has a successful result in A1 but I am choosing from two rows.

In C7:D23 I have not been successful although I have used a similar formula, changing COLUMN to ROW in the formula.

I have used Array formula - what am I doing wrong please?

Thank you - Alan

 

[Bhawani]

Hi,

Try below function
=MIN(IF(D7:D23<0,ROW(C7:C23)))
Hit ctrl+shift+enter

Regards,

 

[NARAYANK991]

Hi Alan ,

The correct formula is :

=INDEX($C$7:$C$23,MIN(IF(D7:D23<0,ROW(C7:C23)-MIN(ROW(C7:C23)) + 1)))

entered as an array formula , using CTRL SHIFT ENTER.

The range may start at any row , but the index value should always start from 1 ; to get this from the range reference , you have to use the construct highlighted in bold.

Narayan

 

[Khalid NGO]

Hi Alan,
If you go through your formulas you will see the difference why you are getting different output:
=COLUMN(A2:Q2) will give you 1
and
=ROW(C7:C23) will give you 7

Regards,

 

[NARAYANK991]

Hi Khalid ,

You are right , but as far as Alan is concerned , it was a coincidence ! Even for the column version , the correct formula would have been :

=INDEX($A$2:$Q$2,MIN(IF(A3:Q3<0,COLUMN(A2:Q2)-MIN(COLUMN(A2:Q2))+1)))

entered as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[AlanFisher]

Thanks for all these suggestions. Bhawani's reply came back first so I used that and it returned the row number. So I adjusted it to read,
=MIN(IF(D7:D23<0,(C7:C23))) - taking out the ROW formula. I've tested it and it was successful. Thank you for your other replies which I will try out but I guess I'm not sure what is the benefit of any of the longer formulas. Alan

 

[NARAYANK991]

Hi Alan ,

My post was addressing your question as to what was wrong with your formula ; in general , when ever you use the construct ROW( start address : end address ) , it forms an array of numbers from the start address to the end address.

Thus , when you use ROW(C7:C23) , you will get the array :

{7;8;9;....;21;22;23}

This cannot directly be used to index a range D7:D23 ; you need to start from 1 upwards , so that the above array is translated to :

{1;2;3;....;15;16;17}

That is what I was pointing out in my formula.

Narayan

 

[Khalid NGO]

Hi Sir Narayan,

Oh... thats the difference between normal and ninja eye :D