Select values form a table

[Ozorio]

Hi. I wonder if someone could help me with the following:

Column A contains dates (ex. from 2004/01/01 to 2004/09/30);
Column B contains week number (formula =weeknum(A:A))
Column C contains stock index

I want, in other sheet, to calculate the weekly variantion from the index.
The new sheet will be like this:

Column A contains the week number ( 1, 2, 3, ...)
Column B contains the weekly variantion ( 0,3% for week 1, -0,2% for week 2
....)

This calculation is made comparing (for week 2) the index from the last day
of the week 1 and the index of the last day of week 2).

How can I do this?

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER) in cell
B1
=(INDEX('sheet1'!$C$1:$C$100,MAX(IF('sheet1'!$B$1:$B$100=A1,'sheet1'!$A
$1:$A$100)))-INDEX('sheet1'!$C$1:$C$100,MAX(IF('sheet1'!$B$1:$B$100=A2,
'sheet1'!$A$1:$A$100)))/INDEX('sheet1'!$C$1:$C$100,MAX(IF('sheet1'!$B$1
:$B$100=A2,'sheet1'!$A$1:$A$100)))