Presuming your data looks exactly like this, you can do this:
Divide the current row by the lookup up the 3rd value of average when sorted by index
The calc is:
Attr(avg) is the current row value. We need attr as we're using window calculations. Divide this by the Lookup result of the 3rd row (lookups are relative to the current row unless we force it to first() plus 2
Then when this is added to the sheet, we have to choose the correct partitioning (edit table calculation from right clicking the pill). Then advanced, and have both avg and rank on the right side (then we only have one partition). Finally we must choose how to determine what is the 3rd row - in this case by sorting by index ascending.
ref cell AK.twbx.zip 7.9 KB
Did this answer your question?
it wasn't exactly what i was looking for. Think i solved using a table calcuation referencing to the ranking. thank you though.