First data in a range that equals less than zero, return another cell

**WillGrice** · 04-21-2013, 04:09 AM

Hi all,
I am calculating a loan repayment. I have a set of data that eventually equals zero. I want a cell that will tell me at what time this happens.
So, I have a set of values in Column L, it eventually reached 0, I have a time vale in column G, it increases by 1 each cell. I want a formula that says when the first value in column L is less that or equal to 0, display cell G that is in the same row. I can't figure out how to do this though. Can anyone help please?

**Harribone** · 04-21-2013, 05:24 AM

If Column M is not in use type into M2 (If in use insert a new column):

Please Login or Register  to view this content.

Drag down as far as required, this can be hidden afterwards.

Then in the cell that you want to show the time enter:

Please Login or Register  to view this content.

**Marcol** · 04-21-2013, 05:48 AM

Maybe this?

Formula:

Please Login or Register  to view this content.

Or

Formula:

Please Login or Register  to view this content.

**WillGrice** · 04-21-2013, 05:57 AM

Originally Posted by Harribone

If Column M is not in use type into M2 (If in use insert a new column):

Please Login or Register  to view this content.

Drag down as far as required, this can be hidden afterwards.

Then in the cell that you want to show the time enter:

Please Login or Register  to view this content.

I started working with this, and it was working, then I played with the second code and managed to get this to give me the time for first zero cell

=INDEX(G:G,MATCH(0,L:L,0))

**Harribone** · 04-21-2013, 05:58 AM

Ah ha, ignore my first post.

Use =INDEX(G:G,MATCH(0,L:L,1)). I put 1 instead of 0 which explains why I couldn't get it work when checking! (i rarely use index/match).
Will be no need for the helper column.

**WillGrice** · 04-21-2013, 06:29 AM

If I use =INDEX(G:G,MATCH(0,L:L,1)) rather than =INDEX(G:G,MATCH(0,L:L,0)) I get the highest T value.
=INDEX(G:G,MATCH(0,L:L,0)) seems to work fine.

**Marcol** · 04-21-2013, 06:30 AM

Originally Posted by Harribone

Ah ha, ignore my first post.

Use =INDEX(G:G,MATCH(0,L:L,1)). I put 1 instead of 0 which explains why I couldn't get it work when checking! (i rarely use index/match).
Will be no need for the helper column.

On it's own that isn't reliable if there are a mix of +ve/-ve numbers, i.e. not sorted. See my second formula.

**Harribone** · 04-22-2013, 01:07 PM

Originally Posted by Marcol

On it's own that isn't reliable if there are a mix of +ve/-ve numbers, i.e. not sorted. See my second formula.

Good point there, good someone is on the ball!

First data in a range that equals less than zero, return another cell

LinkBack

Thread Tools

Rate This Thread

Display

First data in a range that equals less than zero, return another cell

Re: First data in a range that equals less than zero, return another cell

Re: First data in a range that equals less than zero, return another cell

Re: First data in a range that equals less than zero, return another cell

Re: First data in a range that equals less than zero, return another cell

Re: First data in a range that equals less than zero, return another cell

Re: First data in a range that equals less than zero, return another cell

Re: First data in a range that equals less than zero, return another cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions