Hi gurus,

Long time reader, first time poster.

I'm having trouble using the offset function to define the range for the PercentRank function.

It looks like this:

=PERCENTRANK(OFFSET('DIFOT Transaction Data'!$A$1,MATCH(C5&A5,'DIFOT Transaction Data'!A:A,0)-1,4,1,500),AE5)
The formula is not returning an error, but it's returning a value lower than it should. The strangest part is, when I highlight the offset part of the formula and hit F9 to evaluate, and then hit enter, it returns the correct result.

So the offset function is providing something (no error), but not the right range (unless evaluated manually).

Does anyone know why this is happening, and if there are any workarounds?

Many thanks