Reference relative to named range?

[JenniferMurphy]

I have a lot of applications where I need to compare a value in a named column in the same row with a value in that same column in an adjacent row. One example is to calculate the change in some value from one time period to the next.

Here's a simple example. Column C has some ratings. The entire column has been given the name "Ratings".

R/C	C	D	E	F	G
5	Rating	Change	Formula	Change	Formula
6	10	--	--	--	--
7	12	#REF!	=Rating-OFFSET(Rating,-1,0)	2	=Rating-OFFSET(C7,-1,0)
8	8	#REF!	=Rating-OFFSET(Rating,-1,0)	-4	=Rating-OFFSET(C8,-1,0)
9	15	#REF!	=Rating-OFFSET(Rating,-1,0)	7	=Rating-OFFSET(C9,-1,0)

<tbody>
</tbody>

The formulas in F fail because the Offset function cannot use the named range "Rating".

Is there a formula I can use that will allow me to use the name Rating to get both the value in the same row and the one in the previous row?

 

[sericom]

Does this do it

=Rating-INDEX(Rating,ROW()-ROW(Rating),1)

 

[JenniferMurphy]

Does this do it

=Rating-INDEX(Rating,ROW()-ROW(Rating),1)

Nice, that works perfectly. That's certainly better than cell references.

If only there were a way to index into named ranges relative to the current row. Something like

=Rating - Rating[-1]

would be so clear and easy. (sigh)