automatically select cell in another column

[ab]

Hi,

I have a question. Suppose you have three columns.

R1 Col1 Col2 Col3
R2 5 100 20
R3 10 200
R4 25 300
R5 30 50
R6 35 0
R7 40 0

I want to calculate the value in Col3 (20 in this case) with a value
in Col1 for which the corresponding value in Col2 is just before 0.
That is, Multiply 20 with 30 (R6Col2=0, so select R5Col1). Now if I
change the values in Col2, the calculated result should reflect the
change automatically. I am wondering if it is possible to do this in
Excel, peferably without writing a macro!

Thanks.

-ab

 

[Frank Kabel]

Hi
try the following (if your range starts in A2)
=INDEX(A1:A99,VERGLEICH(0,B1:B99,0)-1,1)*C2

Frank