Here's a prime situation for a relative cell reference:
Notice how the values in column "c" only reference "a" and "b" values within their own row. A relative cell reference enables me to autofill the list and each "c" value to only use relevant "a" and "b" values in the same row.
Relative cell references are best used when you want to autofill down a list, utilizing respective values for each row and/or column.
Here's a prime situation for absolute cell references:
Notice how that interest rate is going to stay in cell F1, and I want to apply it to every loan in the list. This means I should absolutely reference it as I autofill the list so that it continually references the same cell.
Absolute cell references are best used when you know the cell location won't change for a necessary value.