Forum Discussion
#REF! Informular when Cut/Paste the reference cells
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
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.
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.