Formula keeps changing range when a new row is being added

0
votes

I have multiple tables with score information , i.e. Name , Score 1, Score 2 ,3 ,etc.

After all the scores i have the following formula on cell L2 : =IFERROR(AVERAGE(D2:K2),"")

The problem is that if I add a new row to the table including a name and Score 1 ,2, 3 , etc. The range on the formula changes automatically to : =IFERROR(AVERAGE(D2:K3),"") and the average will not be accurate because is calculating a result base on 2 rows when is suppose to calculate the range using 1 row

I've been fixing this issue manually after adding all the names and scores by pasting the original formula on cell L2 and dragging down but I would like to know if there is any way that the table can do it automatically without changing the range on the formula ?

1
excelexcel-formula
Have you tried =IFERROR(AVERAGE($D$2:$K$2),"") - Gary's Student
I've tried and excel always change the range automatically to $D$2:$K$3 . Before adding a new row the range is D2:K2 on cell L2 but as soon as I add a new row it changes to D2:K3 - Sam

1 Answers

0
votes

This should "freeze" the formula range:

=IFERROR(AVERAGE(INDIRECT("D2:K2")),"")

because the argument of INDIRECT() is not adjustable resulting from changing table limits.