Forum Discussion

Parwitch's avatar
Parwitch
Copper Contributor
Mar 30, 2019

#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 ...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 30, 2019

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 )

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 30, 2019

    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.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Mar 30, 2019

      SergeiBaklan 

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

Resources