SOLVED
Home

Unwanted change of the formula when increasing the table

%3CLINGO-SUB%20id%3D%22lingo-sub-532973%22%20slang%3D%22en-US%22%3EUnwanted%20change%20of%20the%20formula%20when%20increasing%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532973%22%20slang%3D%22en-US%22%3E%3CP%3EI%20use%20the%20Polish%20version%20of%20Excel%20365.%3CBR%20%2F%3EI%20want%20to%20cumulatively%20calculating%20the%20data%20in%20the%20table%20(Table%20tool)%20row%20by%20row.%20So%20I%20use%20the%20formulas%3A%3CBR%20%2F%3Ein%20C2%3A%20%3DCOUNTBLANK(%24B%242%3A%24B2)%3CBR%20%2F%3Ein%20%3CSTRONG%3EC3%3C%2FSTRONG%3E%3A%20%3DCOUNTBLANK(%24B%242%3A%24%3CSTRONG%3EB3%3C%2FSTRONG%3E)%3CBR%20%2F%3Ee.t.c.%3CBR%20%2F%3EWhen%20I%20enlarge%20a%20table%20by%20one%20or%20more%20rows%2C%20the%20formula%20in%20the%20last%20%22old%22%20row%20changes%20automatically%20to%20the%20scope%20of%20the%20entire%20column.%3CBR%20%2F%3ESo%3A%3CBR%20%2F%3E-%20when%20I%20enlarge%20the%20table%20to%20A1%3AC4%2C%20in%20cell%20%3CSTRONG%3EC3%3C%2FSTRONG%3E%20the%20formula%20changes%20to%20%3DCOUNTBLANK(%24B%242%3A%24%3CSTRONG%3EB4%3C%2FSTRONG%3E)%3CBR%20%2F%3E-%20when%20I%20enlarge%20the%20table%20to%20A1%3AC5%2C%20in%20cell%20%3CSTRONG%3EC3%3C%2FSTRONG%3E%20the%20formula%20changes%20to%20%3DCOUNTBLANK(%24B%242%3A%24%3CSTRONG%3EB5%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20looks%20like%20Excel's%20error%20(365).%20I%20have%20information%20that%20the%20table%20behaves%20correctly%20in%20XL2007.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3EArtik%3C%2FP%3E%3CP%3E(I%20apologize%20for%20my%20language%2C%20but%20it%20is%20as%20good%20as%20Google%20Translator%20%3A-)%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-532973%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E%22Excel%20365%22%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-533740%22%20slang%3D%22en-US%22%3ERe%3A%20Unwanted%20change%20of%20the%20formula%20when%20increasing%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334140%22%20target%3D%22_blank%22%3E%40Artik_K%3C%2FA%3E%26nbsp%3B%2C%20that's%20a%20normal%20behaviour.%20Excel%20remembers%20what%20%24B3%20is%20in%20the%20last%20row%20of%20the%20table%2C%20and%20when%20you%20expand%20the%20table%20it%20automatically%20substitutes%20the%20reference%20on%20the%20cell%20in%20the%20new%20last%20row.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20with%20tables%20it's%20better%20to%20use%20structured%20references%2C%20in%20your%20case%20like%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTBLANK(INDEX(%5BB%5D%2C1)%3A%5B%40B%5D)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539413%22%20slang%3D%22en-US%22%3ERe%3A%20Unwanted%20change%20of%20the%20formula%20when%20increasing%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539413%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334140%22%20target%3D%22_blank%22%3E%40Artik_K%3C%2FA%3E%26nbsp%3B%2C%20that's%20a%20normal%20behaviour.%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EAs%20for%20me%2C%20this%20is%20not%20normal%20behavior.%20Excel%20destroyed%20my%20formula.%20The%20more%20so%20because%20in%20XL2007%20there%20was%20no%20problem.%3C%2FP%3E%3CP%3EHowever%2C%20I%20really%20like%20the%20solution%20from%20INDEX%20().%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EArtik%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539976%22%20slang%3D%22en-US%22%3ERe%3A%20Unwanted%20change%20of%20the%20formula%20when%20increasing%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334140%22%20target%3D%22_blank%22%3E%40Artik_K%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20I%20took%20wrong%20word.%20I%20mean%20that's%20by%20design.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Artik_K
New Contributor

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:$B3)
e.t.c.
When I enlarge a table by one or more rows, the formula in the last "old" row changes automatically to the scope of the entire column.
So:
- when I enlarge the table to A1:C4, in cell C3 the formula changes to =COUNTBLANK($B$2:$B4)
- when I enlarge the table to A1:C5, in cell C3 the formula changes to =COUNTBLANK($B$2:$B5)

 

It looks like Excel's error (365). I have information that the table behaves correctly in XL2007.

 

Greetings

Artik

(I apologize for my language, but it is as good as Google Translator :-) )

3 Replies
Solution

@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])

@Sergei Baklan 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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies