.
This is how I understand it...
I wish I had good use case examples for you. I use Relative Offset when I am blocking out a character and determining proportions. I can put an array modifier on the head in the Z axis to determine the head-height of the character. I haven't had reason to use Constant Offset that I can recall so maybe someone can pop in with a good example?
Hi Jann! Let me see if I can further demystify the difference. As Coyo said above, "relative" treats the dimension of the object as a single unit. I'll use an irregular object as an example, as a cube is the same in each dimension (X, Y and Z).
If we change the Offset to "constant" then we're telling Blender to offset the object by a distance factor. In this case, the units are set to Meters, so the Distance X is 1m. See how Suzanne - which has a dimension larger than 1 meter in the X direction - overlaps? The Object's origin is what is offset in this case.