Database Table Aggregators In AFC

Database table aggregators are functions that compute values over contiguous ranges of values that are structured like a database table. This means they have a header row of column labels. The table aggregators can filter the table prior to aggregating it.

Test Sheet

The following test cases reference this sheet:

	A	B	C	D	E	F	G	H	I	J	K
2	Test table	Tree	Height	Yield	Profit	Date
3		Apple	18	14	105.00	8/2/05 14:15
4		Pear	12	10	96.00	8/2/05 14:16
5		Cherry	13	9	105.00	9/3/05
6		Apple	14	10	75.00	8:45
7		Pear	9	8	76.80	8:44
8		Apple	8	6	45.00	8:43
9
10	Criteria sets	Tree	Height	Height	Tree	Height
11		Apple	>10.0	<16	Nonexistent	<1.4e6
12		Pear		<1'000
13
14		Tree	Height	Height	RefTree	RefHeight	RefHeight
15		=E15	=">"&F15	=CONCATENATE("<",G15)	Apple	10	16
16
17		Date	Date	Date	RefDate	Date	Date	RefDate	Date	Date	RefDate
18		8/2/05 14:16	>=2.9.2005	=">="&E18	9/2/05 (ValDate1)	>=2.8.05 14:16	=">="&H18	8/2/05 14:16 (ValDate2)	>=8:45	=">="&K18	8:45 (ValDate3)
19
20		Free Form
21		=AND(B3="Apple",C3>$F$15,C3<$G$15)

B3:E4 (Data1)
B11:B12 (Vals1)
E15:G15 (Vals2)

Aggregators

DSUM

	B	C	D
26	247.8	=DSUM( B2:E8, "Profit", B10:D12 )
27	0.0	=DSUM( B2:E8, "Profit", E10:E11 )
28	247.8	=DSUM( B2:E8, D28, B10:D12 )	4.0	Data1
29	28.0	=DSUM( B2:E8, "Yield", B10:D12 )
30	30.0	=DSUM( B2:E8, "Yield", B10:B11 )
31	29.0	=DSUM( B2:E8, "Yield", C10:D11 )
32	10.0	=DSUM( B2:E8, "Yield", B10:D11 )
33	48.0	=DSUM( B2:E8, "Yield", B10:B12 )
34	57.0	=DSUM( B2:E8, "Yield", F10:F11 )

date/time criteria

	B	C
35	10.0	=DSUM( B$2:F$8, "Yield", B$17:B$18 )
36	9.0	=DSUM( B$2:F$8, "Yield", C$17:C$18 )
37	9.0	=DSUM( B$2:F$8, "Yield", D$17:D$18 )
38	19.0	=DSUM( B$2:F$8, "Yield", F$17:F$18 )
39	19.0	=DSUM( B$2:F$8, "Yield", G$17:G$18 )
40	43.0	=DSUM( B$2:F$8, "Yield", I$17:I$18 )
41	43.0	=DSUM( B$2:F$8, "Yield", J$17:J$18 )

&-ed criteria

	B	C	D
42	75.0	=DSUM( B2:E8, D42, B14:D15 )	4.0

free-form criteria

	B	C	D
43	75.0	=DSUM( B2:E8, D43, B20:B21 )	4.0

DPRODUCT

	B	C
45	552960.0	=DPRODUCT( B2:E8, "Profit", B10:D12 )
46	0.0	=DPRODUCT( B2:E8, "Profit", E10:E11 )

DCOUNT

	B	C
48	3.0	=DCOUNT( B2:E8, "Profit", B10:D12 )
49	0.0	=DCOUNT( B2:E8, "Profit", E10:E11 )

DCOUNTA

	B	C
51	3.0	=DCOUNTA( B2:E8, "Profit", B10:D12 )
52	0.0	=DCOUNTA( B2:E8, "Profit", E10:E11 )

DMIN

	B	C
54	75.0	=DMIN( B2:E8, "Profit", B10:D12 )
55	0.0	=DMIN( B2:E8, "Profit", E10:E11 )

DMAX

	B	C
57	96.0	=DMAX( B2:E8, "Profit", B10:D12 )
58	0.0	=DMAX( B2:E8, "Profit", E10:E11 )

DAVERAGE

	B	C
60	82.6	=DAVERAGE( B$2:E$8, "Profit", B$10:D$12 )
61	!NaN/AE	=DAVERAGE( B$2:E$8, "Profit", E$10:E$11 )		Excel says: #DIV/0!

DVARP

	B	C
63	90.3199999999989	=DVARP( B$2:E$8, "Profit", B$10:D$12 )
64	!NaN/AE	=DVARP( B$2:E$8, "Profit", E$10:E$11 )		Excel says: #DIV/0!

DVAR

	B	C
66	135.47999999999774	=DVAR( B$2:E$8, "Profit", B$10:D$12 )
67	!NaN/AE	=DVAR( B$2:E$8, "Profit", E$10:E$11 )		Excel says: #DIV/0!

DSTDEVP

	B	C
69	9.50368349641332	=DSTDEVP( B$2:E$8, "Profit", B$10:D$12 )
70	!NaN/AE	=DSTDEVP( B$2:E$8, "Profit", E$10:E$11 )		Excel says: #DIV/0!

DSTDEV

	B	C
72	11.639587621561072	=DSTDEV( B$2:E$8, "Profit", B$10:D$12 )
73	!NaN/AE	=DSTDEV( B$2:E$8, "Profit", E$10:E$11 )		Excel says: #DIV/0!

DGET

	B	C
75	75.0	=DGET( B$2:E$8, "Profit", B$10:D$11 )
76	!FE	=DGET( B$2:E$8, "Profit", B$10:C$11 )		Excel says: #NUM!
77	!FE	=DGET( B$2:E$8, "Profit", E$10:E$11 )		Excel says: #VALUE!

SUMIF

	B	C
79	381.0	=SUMIF( C3:C8, C11, E3:E8 )
80	56.0	=SUMIF( C3:C8, D11 )
81	225.0	=SUMIF( B3:B8, B11, E3:E8 )
82	0.0	=SUMIF( B3:B8, E11, E3:E8 )

COUNTIF

	B	C
84	4.0	=COUNTIF( C3:C8, C11 )
85	5.0	=COUNTIF( C3:C8, D11 )
86	3.0	=COUNTIF( B3:B8, B11 )
87	0.0	=COUNTIF( B3:B8, E11 )

Repeating Section Support

You can use vertically repeating sections in the data area. They must not, however, cover the label area, and they must always cover exactly the width of the table. You can mix them with static rows. An example is shown below:

	D	E	F
85	Kind	Age	Yield
86	1	12	28
87	1	8	16
88	2	9	28
89	1	9	30
90	1	5	22

D87:F89 (V_DSUM_PART)

It is also possible to have multiple sibling sections in the data table, as shown below:

	B	C	D
2	Kind	Age	Yield
3	1	12	28
4	2	7	10
5	1	8	11
6	1	6	40
7	2	4	12
8	1	5	13
9	1	5	22

B4:D5 (RS_One)
B7:D8 (RS_Two)

TODO: This example is not automatically tested yet!