Auto-Extend Formula Range

I have the following formula:

=IF(MAX(C2:F2)>0,COUNTIF(C2:F2,">1")/COUNT(C2:F2),0)

This formula is located in the second column of a structured table, and goes through the rest of the columns in the table to the right. The formula basically tells how many instances satisfy a certain criteria and puts it in a fraction over the total number of instances. So if the criteria is met 2 times and there were 3 instances I get 2/3 as the output.

I have been researching for an hour now and I can't figure it out. Is there a way to get the cell range in the formula to auto-expand when a new column is added?

Solution

You should take advantage of Structured Referencing and Index to get the parts of the table you need

=IFERROR( COUNTIF(
                  INDEX(YourTableName[@],1,(COLUMN([@FormulaColumn])-COLUMN(YourTableName[@])+2)):
                  INDEX(YourTableName[@],1,COLUMNS(YourTableName[@])),
                  ">1") /
          COUNT(
                  INDEX(YourTableName[@],1,(COLUMN([@FormulaColumn])-COLUMN(YourTableName[@])+2)):
                  INDEX(YourTableName[@],1,COLUMNS(YourTableName[@])))
        ,0)

This will count number of cell to the right of the Formula Column that are >1 and divide that by the count of cells to the right of the Formula Column that are not blank.

The key point here is COLUMNS(YourTableName[@]). This will increase automatically as you add columns

Note: if you want to include blanks in the divisor count, the formula can be adjusted to suit

Just change YourTableName and FormulaColumn to suit your data