Forum Discussion

Artik_K's avatar
Artik_K
Copper Contributor
May 06, 2019
Solved

Unwanted change of the formula when increasing the table

I use the Polish version of Excel 365. I want to cumulatively calculating the data in the table (Table tool) row by row. So I use the formulas: in C2: =COUNTBLANK($B$2:$B2) in C3: =COUNTBLANK($B$2...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    May 06, 2019

    Artik_K , that's a normal behaviour. Excel remembers what $B3 is in the last row of the table, and when you expand the table it automatically substitutes the reference on the cell in the new last row.

     

    In general with tables it's better to use structured references, in your case like

    =COUNTBLANK(INDEX([B],1):[@B])
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 06, 2019

Artik_K , that's a normal behaviour. Excel remembers what $B3 is in the last row of the table, and when you expand the table it automatically substitutes the reference on the cell in the new last row.

 

In general with tables it's better to use structured references, in your case like

=COUNTBLANK(INDEX([B],1):[@B])
Artik_K's avatar
Artik_K
Copper Contributor
May 07, 2019
SergeiBaklan wrote:

Artik_K , that's a normal behaviour.

As for me, this is not normal behavior. Excel destroyed my formula. The more so because in XL2007 there was no problem.

However, I really like the solution from INDEX ().

Thank you.

 

Artik

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 07, 2019

    Artik_K , yes, I took wrong word. I mean that's by design. 

Resources