Bitt is right. Formulas (functions) are not permitted to write to other cells. Nor can macros.

Excel only allows such things to happen in specific ways.

See this page for the long description. The exception noted is the one used in the VBA code I supplied.