#REF! Informular when Cut/Paste the reference cells

Copper Contributor

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.)F6EE675E-0708-4BF5-885D-56DD0B67DA80.jpeg

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

@Parwitch , you may use

=IFNA(LOOKUP(2,1/NOT(ISBLANK(E15:I15)),{0,0.2,0.5,0.8,1})*D15,"")

 

@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-ec4b8c5f.... 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.

@Parwitch 

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 )

@Sergei Baklan 

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.

Hi @Peter Bartholomew ,

 

I believed you add named range variant... As a comment, your legend

image.png

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.

@Peter Bartholomew , do you mean

=@MAX((E15:I17="X")*percentage*D15:D17)

 

 

@Sergei Baklan 

You are correct of course.  What I mean is

Capture.JPG

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).

@Peter Bartholomew , yes, but that forwards to CSE

@Sergei Baklan 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 , you are right, that's the same effect with left/right most cells if move them horizontally. When

=IFNA(LOOKUP(2,1/NOT(ISBLANK(OFFSET($D15,0,1,,5))),{0,0.2,0.5,0.8,1})*$D15,"")

fixes that.

 

@Parwitch , as for lookup

 

1/... returns error (division on zero) for blank cells and 1 for non blank one.

LOOKUP ignores all errors and finds in first part most close value to lookup one from down side; after that returns the value from second array from same position.

Lookup value 2 here is to avoid errors in some situations, in general any value which is greater than 1 works.

@Sergei Baklan 

The MMULT version works just as well left as a dynamic (spilt) array.

 

Meanwhile I need to check out the use of implicit intersection with a function.

I have used it with 1D named ranges (@fullScore) and with single named cells containing a formula that spills to create a 1D spilt array (@score#) but hadn't thought of operating on the output from a function.  

 

What I really want is to restrict the aggregations such as MAX to act only over one dimension of the array, here the 3 rows of the array, aggregating over columns to give the 3 row results as an array.