Oh interesting! I was highlighting the whole formula, not just the offset/index portion. I do get those different results where the blanks are being converted to 0's. That happens for both formulas.

If the blank cells are supposed to be calculated as 0's, you can tell the formula to do so by adding another index to convert blanks, like so:

Index version:

`=PERCENTRANK(INDEX(--(0&INDEX('DIFOT Transaction Data'!$E:$SJ,MATCH(C5&A5,'DIFOT Transaction Data'!A:A,0),0)),),AE5)`

Offset version:

`=PERCENTRANK(INDEX(--(0&OFFSET('DIFOT Transaction Data'!$A$1,MATCH(C5&A5,'DIFOT Transaction Data'!A:A,0)-1,4,1,500)),),AE5)`

Both of those adjusted formulas will return the result 0.777 (which is the result when calculating blanks as 0s).

So basically, the OP needs to choose whichever version s/he needs (calculate blanks as 0's or ignore blanks).

## Bookmarks