Forum Discussion
#REF! Informular when Cut/Paste the reference cells
I am trying to make a score template. The score will be calculated from the x in the scale column that someone else will fill in later. (Right-most column is the calculated score.)
The issue is there will be users who will cut/paste the x to other column if they change their mind.
When the happen the cell that was cut in will become #REF! in the formula.
The formula I am using is as follow:
=IFS(E15<>"",0,F15<>"",0.2*D15,G15<>"",0.5*D15,H15<>"",0.8*D15,I15<>"",D15,TRUE,"")
It will become like this if I cut x from E15 and paste to other column
=IFS(#REF!<>"",0,F15<>"",0.2*D15,G15<>"",0.5*D15,H15<>"",0.8*D15,I15<>"",D15,TRUE,"")
I tried $E15 and it didn’t work.
I tried INDIRECT(“E15”) and it works but when I coppy to the next row it didn’t auto change E15 to E16 for me and my template has multiple shhet of 4,000+ rows. So I cannot manually change all of them.
Is there anyway to make the formula tolerant to cut/paste or is there any other formula more suitable for this kind of suitation?
13 Replies
The attached is built from relative named ranges and a named formula. The purpose of the named formula is simply to avoid the need for Ctrl+Shift+Enter that is required by almost all versions of Excel to commit an array formula.
= MAX( ( performance="X") * percentage * fullScore )
Hi PeterBartholomew1 ,
I believed you add named range variant... As a comment, your legend
could confuse people who are not familiar with named ranges. If to do that from scratch for another workbook, stay on row 29, copy formula and add it as "performance" due to implicit intersection, you have wrong result, you are to be on row 22. That's just cosmetic.
You are correct of course. What I mean is
but most users are more familiar with the hybrid A1 notation that looks like an absolute reference but behaves as a relative reference.
For my own use, I could well go with
= fullScore * MMULT( N(performance="X"), percentage )
which would allow me to use 2D ranges (MMULT being just about the only formula I know that will process a 2D array by row or by column).
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.
SergeiBaklan Thank you for your support. I am still trying to understand the lookup(2,1/... part.
But to update you the result, I tested your formula. It works really well as long as I do not cut/paste the left-most or right-most cell. If I do, the "ISBLANK(E15:I15)" part will be automatically changed to (F15:I15) or (E15:H15)
I can workaround by inserting a hidden column before and after the scale column to use a a reference point but I don't think that is the right thing to do.
Parwitch , found this post https://www.excelforum.com/excel-formulas-and-functions/1043140-explain-lookup-2-1-a2-a10-d2-b2-b10.html, it explains in more details how LOOKUP(2, 1/.. works
