Weird behavior of Excel tables

[J.Ty.]

I have a fundamental problem with understanding how Excel tables work. I expected that the mechanism which extends the table when a new rows is added would be simple: the formulas from the last row are copied, exactly as if there were filled down one row, ranges where necessary exteded to include the now row. However, the practical implementation seems very much different and hard to explain.

I did a small table with 3 rows od data and 2 columns with formulas, and then added a new row of data, causing it to automatically modify the existing formulas and create new formulas in the added row. The result is so odd that I have no idea how this actual mechanism works and how to predict its results without making experiments each time. You can test the file here. I used Excel 365. I do have dynamic array formulas, if that matters.

J.Ty.

Book1
	A	B	C	D
1	Letter	Number	Count	Count2
2	A	1	1	1
3	B	2	1	1
4	C	3	1	1
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=COUNTIFS(A$2:A4,A2,B$2:B4,"<="&B2)
D2:D4	D2	=COUNTIF(A$1:A2,Sheet1!$A2)

Book1
	A	B	C	D
1	Letter	Number	Count	Count2
2	A	1	1	1
3	B	2	2	1
4	C	3	1	1
5	B	1	1	2
Sheet1

Cell Formulas
Range	Formula
C2, C5	C2	=COUNTIFS(A$2:A5,A2,B$2:B5,"<="&B2)
D2:D3, D5	D2	=COUNTIF(A$1:A2,Sheet1!$A2)
C3:C4	C3	=COUNTIFS(A$2:A5,A3,B$2:B5,"<="&B3)
D4	D4	=COUNTIF(A$1:A5,Sheet1!$A4)

 

[DRSteele]

It is best to avoid using cell references in Excel Tables. One of the reasons, as I understand it, is that Tables are meant to be thought of in database milieu, where the rows are Records and the columns are Fields. This makes the Table sortable and easily manipulated from a distance; its formulas are independent of their location on the Table. In other words, Tables work best when the formulas don't refer to prior or subsequent rows. This is not to say that it should never be the case where formulas refer to prior records, but it makes trouble. One situation where I have got it to work is an Excel Table of a bank account cheque register with a running balance.

Create the Table, then enter the formulas. That way the formulas will use Excel Table Nomenclature. I tried it here on your data but I am unclear as to whether you intend to have a cumulative formula (i.e., one where a record depends upon the prior or subsequent records). Is the result you expected?

Book1
	A	B	C	D
1	Letter	Number	Count	Count2
2	A	1	1	1
3	B	2	2	2
4	C	3	1	1
5	B	1	1	2
Sheet67

Cell Formulas
Range	Formula
C2:C5	C2	=COUNTIFS([Letter],[@Letter],[Number],"<="&[@Number])
D2:D5	D2	=COUNTIF([Letter],[@Letter])

 

[J.Ty.]

Create the Table, then enter the formulas. That way the formulas will use Excel Table Nomenclature. I tried it here on your data but I am unclear as to whether you intend to have a cumulative formula (i.e., one where a record depends upon the prior or subsequent records). Is the result you expected?

The formulas resemble ones I would use to automatically sort the data in the pre-dynamic-array-formulas times, but were meant only to demonstrate how odd the mechanism is.

Why bother? Because many slightly more advanced computations in Excel require references to ranges which are neither rows nor complete columns, even if the final result is independent of the actual order of data. This is very much like in SQL, whose queries are independent of the order of records, but algorithms which execute those queries take the order into account.

Eventually, I would like to understand the way the new formulas are created. At present it is pure curiosity. However, understanding this mechnism might give a chance to harness it to produce something of value.

Thanks,

J.Ty.

 

[DRSteele]

If you have some spare time, you could review some videos on youtube from ExcelIsFun. You might already know all this material, but the videos might spark some ideas about how Tables are created and used.

I found these for you. Youtube is full of instructional vids.

And there is this part about Structured References form MS.