This simple illustrative worksheet explains how the offset() function in excel works.

This formula uses sum(), offset() and the min() function to calculate the yearly depreciation charge (straight-line) based on capex and useful life.