Excel 2007 - Finding corresponding value 2

[mrkuosea]

I have 2 columns of data which are a list of numbers. The second column contains a list of numbers which changes from negative values to positive values. I am trying to find a way to select the cell in the first column which is adjacent to the first positive value in the second column. Please suggest a way I can do this. Thanks, Mark.

 

[lionelhill]

if the second column is sorted, then
=INDEX(A:A, MATCH(0,B:B, 1))
This will select the first cell that is zero or greater, if that's your definition of positive.

 

[SkipVought]

hi,

select the cell in the first column which is adjacent to the first positive value in the second column.

There is not way, using spreadsheet features to SELECT a cell automatically.

lionelhill gave you a solution to display the VALUE of that cell.

Since that VALUE may not be unique, the ROW or OFFSET might be better, of you might use Conditional Formatting to shade the cell.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrkuosea]

Thanks for your responses Lionelhill and Skip!

Lionelhill’s solution works but I want to select the value in the first column that adjacent to the first positive value in the second column. I am trying to create an automated payback period table which calculates the payback period. In the example below, I get the years (number of the left side of the decimal by using countif) and the numerator (on the right side of the decimal by using lookup) but I can’t get denominator. Lionelhill’s solution selects $7000 instead of $5000.

Payback period is calculated by the formula: number of years (in column A) that there was a negative cash flow Plus (last negative value in column C divided by value in column B adjacent to the first positive value in column C). The answer should by 20.20 in the case below.

Column A Column B Column C
0 -$101,000.00
1 $5,000.00 -$96,000.00
2 $2,000.00 -$94,000.00
3 $6,000.00 -$88,000.00
4 $7,000.00 -$81,000.00
5 $5,000.00 -$76,000.00
6 $2,000.00 -$74,000.00
7 $6,000.00 -$68,000.00
8 $7,000.00 -$61,000.00
9 $5,000.00 -$56,000.00
10 $2,000.00 -$54,000.00
11 $6,000.00 -$48,000.00
12 $7,000.00 -$41,000.00
13 $5,000.00 -$36,000.00
14 $2,000.00 -$34,000.00
15 $6,000.00 -$28,000.00
16 $7,000.00 -$21,000.00
17 $5,000.00 -$16,000.00
18 $2,000.00 -$14,000.00
19 $6,000.00 -$8,000.00
20 $7,000.00 -$1,000.00
21 $5,000.00 $4,000.00
22 $2,000.00 $6,000.00
23 $6,000.00 $12,000.00
24 $7,000.00 $19,000.00
25 $5,000.00 $24,000.00
26 $2,000.00 $26,000.00
27 $6,000.00 $32,000.00
28 $7,000.00 $39,000.00
29 $5,000.00 $44,000.00
30 $2,000.00 $46,000.00
31 $6,000.00 $52,000.00
32 $7,000.00 $59,000.00
33 $5,000.00 $64,000.00
34 $2,000.00 $66,000.00
35 $6,000.00 $72,000.00
36 $7,000.00 $79,000.00
37 $3,000.00 $82,000.00
20 =COUNTIF(C3:C39,"<0")
-1000 =(LOOKUP(0, C3:C39))
5000 =?????
Payback Period = 20 + -(-1000/5000)

 

[SkipVought]

Hmmmm???

1) your COUNTIF() function ALWAYS returns the same value. Do the values in col C ever change?

2) I get 21 for the COUNTIF() result.

Lionelhill's solution selects $7000 instead of $5000.

HOW do you get 5000??? lionelhill's 7000 is the value conrresponding to the LAST NEGATIVE VALUE in Column C???

Here's my solution for DISPLAYING the value of intetest. I used Named Ranges, and I do not know what you would call column B values or column C values, so I used, YRS, ColB, ColC
[tt]
=INDEX(YRS,COUNTIF(ColC,"<0"),1)-(INDEX(ColC,COUNTIF(ColC,"<0"),1)/INDEX(ColB,COUNTIF(ColC,"<0"),1))
[/tt]
My result: 20.14285714




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrkuosea]

Skip thanks for the prompt response. But, I clearly mentioned in both the postings that "I want to select the value in the first column that adjacent to the first positive value in the second column". So the first positive value in column C see is $4000 and $5000 is adjacent to it which is what I am looking for. Pay back period is calculated in this specific way, I cannot change that!

 

[SkipVought]

Please, you are not selecting anything. You are returning a value from a formula. SELECT, in Excel means something entire different.
[tt]
=INDEX(YRS,MATCH(0,ColC,1)+1,1)-(INDEX(ColC,MATCH(0,ColC,1)+1,1)/INDEX(ColB,MATCH(0,ColC,1)+1,1))
[/tt]
My value: 20.2

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

so sorry:
yes, you're right, my formula gets the last negative value or zero. If you want the first positive, you can probably modify it slightly to something like
=INDEX(A:A, MATCH(-0.000001,B:B, 1)+1)
There might be something more elegant but it's late!

Yes, you should use named ranges instead of A:A etc.

 

[mrkuosea]

Skip and Lionelhill,

Thank you both for all your help.

Yes, Skip I am sorry I misworded the question. But your first solution did the trick with one small change i.e. changing "<0" to ">0" for the denominator.

=INDEX(YRS,COUNTIF(ColC,"<0"),1)-(INDEX(ColC,COUNTIF(ColC,"<0"),1)/INDEX(ColB,COUNTIF(ColC,">0"),1))

Again, thank you very much!