Forum Discussion
#REF! Informular when Cut/Paste the reference cells
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).
PeterBartholomew1 , yes, but that forwards to CSE
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.