The offset is used when you want to add something like 'Continued from page xx" or “Continue on page yy” when you have a periodic pattern generally with frames, e.g. you have side text (secondary text flow) every other page = offset ±2. You can then paste repeatedly the same frame. You could of course insert a cross-reference to the preceding/next frame but you would have to modify the fields in every frame.
When there is no target “object” (the page does not exist because it is beyond the end of document or before the beginning), the returned page is void. Unfortunately, even with conditional text you can’t eliminate 'Continued from" because you can’t use fields in condition expressions. This seriously limits the usefulness of offset.
A possible other usage of offset would be in headers to display a “range” of pages. A twisted example would be numbering the pages only in the right page, the facing left page not being numbered (for whatever reason). You could have the left page number (offset -1) at left in the right page header with the current number at right. When the left page does not exist (the right page is the first page of the document), the number is blank thus correct.
I admit that my second example is a bit far-fetched. Anyway, I never met contexts in which an offset was required/mandatory.