Forum Discussion
#REF! Informular when Cut/Paste the reference cells
Parwitch , in addition, the cause of error is explained here https://support.office.com/en-us/article/how-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be. That's the specific of IFS, any formula where you use individual cell reference. If you move or delete cell (not cell value) you receive such error. You may try on =A1+B1 in C1 and drag it down, then cut and paste B1.
Using of any formula with range helps, that could be OFFSET as well.
Thank you for the link to the documentation page.
I have come to detest the normal Excel practice of direct references to single cells and to find encouragement from an authoritative statement,
"this is the primary reason why using explicit cell references in functions is not recommended",
which provides some further support for the concept that the unit of reference for formulas should be the array and not the cell (unless the content of the cell is truly unique).
In a wider context, I am looking for support for an operator or a function that would take a 2D array, such as the assessment of performance in this post, and treat it as a collection of rows for the purposes of the next aggregation. In this case, it would allow one formula to spill down to apply scores to every row (student or subject?) rather than requiring the use of row-relative referencing.