Aggregators In AFC
Aggregators are functions that compute values over (possibly non-contiguous) ranges of values.
SUM and Range Specifications
I use the SUM function to show the types of range specification supported by AFC, as well as the support for SUM as such:
1-dim range
| A | B | C | D | E | ||
| 2 | 6.0 | =SUM( C2:E2 ) | 1.0 | 2.0 | 3.0 | |
| 3 | 15.0 | ... | 4.0 | 5.0 | 6.0 |
2-dim range
| A | B | C | D | E | ||
| 5 | 21.0 | =SUM( C5:E6 ) | 1.0 | 2.0 | 3.0 | |
| 6 | 30.0 | =(SUM( C6:E6 ) * 2.0) | 4.0 | 5.0 | 6.0 |
Single cell
| A | B | C | ||
| 8 | 1.0 | =SUM( C8 ) | 1.0 | |
| 9 | 4.0 | ... | 4.0 |
Multiple cells
| A | B | C | D | ||
| 11 | 3.0 | =SUM( C11, D11 ) | 1.0 | 2.0 | |
| 12 | 9.0 | ... | 4.0 | 5.0 |
Three cells with blanks
| A | B | C | D | E | ||
| 14 | 2.0 | =SUM( C14, D14, E14 ) | 2.0 | |||
| 15 | 10.0 | ... | 4.0 | 6.0 |
Non-contiguous cells
| A | B | C | D | E | F | G | H | I | ||
| 17 | 15.0 | =SUM( C17, G17, D17, I17 ) | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 7.0 |
| 18 | 17.0 | ... | 7.0 | 6.0 | 5.0 | 4.0 | 3.0 | 2.0 | 1.0 | 7.0 |
Cells referenced multiply
| A | B | C | D | ||
| 20 | 6.0 | =SUM( D20, C20, C20, D20 ) | 1.0 | 2.0 | |
| 21 | 18.0 | ... | 4.0 | 5.0 |
Named range
| A | B | C | D | E | ||
| 23 | 15.0 | =SUM( SumRange ) | 4.0 | 5.0 | 6.0 | |
| 24 | 4.0 | =SUM( C24:E24 ) | 1.0 | 3.0 |
Range with blanks
| A | B | C | D | E | F | G | H | ||
| 26 | 3.0 | =SUM( C26:H26 ) | 1.0 | 2.0 | |||||
| 27 | 15.0 | ... | 4.0 | 5.0 | 6.0 |
Mixture of ranges and cells with blanks
| A | B | C | D | E | F | G | H | I | ||
| 29 | 9.0 | =SUM( C29:E29, G29, I29 ) | 1.0 | 100.0 | 200.0 | 8.0 | 7.0 | |||
| 30 | 6.0 | ... | 2.0 | 80.0 | 4.0 | 7.0 |
Other Aggregators Like SUM
There are a number of other supported aggregation functions which behave much like SUM in the type of arguments they accept. They are:
PRODUCT (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 2 | 6.0 | =PRODUCT( C2:E2 ) | 1.0 | 2.0 | 3.0 | |||||
| 3 | 120.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 5 | 720.0 | =PRODUCT( C5:E6 ) | 1.0 | 2.0 | 3.0 | |||||
| 6 | 14400.0 | =(PRODUCT( C6:E6 ) ^ 2.0) | 4.0 | 5.0 | 6.0 | |||||
| 8 | 1.0 | =PRODUCT( C8 ) | 1.0 | |||||||
| 9 | 4.0 | ... | 4.0 | |||||||
| 11 | 2.0 | =PRODUCT( C11, D11 ) | 1.0 | 2.0 | ||||||
| 12 | 20.0 | ... | 4.0 | 5.0 | ||||||
| 14 | 6.0 | =PRODUCT( C14, D14, E14 ) | 1.0 | 2.0 | 3.0 | |||||
| 15 | 120.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 17 | 70.0 | =PRODUCT( C17, G17, D17, I17 ) | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 7.0 |
| 18 | 126.0 | ... | 7.0 | 6.0 | 5.0 | 4.0 | 3.0 | 2.0 | 1.0 | 7.0 |
| 20 | 4.0 | =PRODUCT( D20, C20, C20, D20 ) | 1.0 | 2.0 | ||||||
| 21 | 400.0 | ... | 4.0 | 5.0 | ||||||
| 23 | 120.0 | =PRODUCT( ProductRange ) | 4.0 | 5.0 | 6.0 | |||||
| 24 | 6.0 | =PRODUCT( C24:E24 ) | 1.0 | 2.0 | 3.0 | |||||
| 26 | 210.0 | =PRODUCT( C26:E26, G26, I26 ) | 1.0 | 2.0 | 3.0 | 100.0 | 5.0 | 200.0 | 7.0 | 7.0 |
| 27 | 30240.0 | ... | 6.0 | 7.0 | 8.0 | 80.0 | 9.0 | 10.0 | 7.0 |
COUNT (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 29 | 3.0 | =COUNT( C29:E29 ) | 1.0 | 2.0 | 3.0 | |||||
| 30 | 3.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 32 | 6.0 | =COUNT( C32:E33 ) | 1.0 | 2.0 | 3.0 | |||||
| 33 | 6.0 | =(COUNT( C33:E33 ) * 2.0) | 4.0 | 5.0 | 6.0 | |||||
| 35 | 1.0 | =COUNT( C35 ) | 1.0 | |||||||
| 36 | 1.0 | ... | 4.0 | |||||||
| 38 | 2.0 | =COUNT( C38, D38 ) | 1.0 | 2.0 | ||||||
| 39 | 2.0 | ... | 4.0 | 5.0 | ||||||
| 41 | 3.0 | =COUNT( C41, D41, E41 ) | 1.0 | 2.0 | 3.0 | |||||
| 42 | 3.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 44 | 4.0 | =COUNT( C44, G44, D44, I44 ) | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 7.0 |
| 45 | 4.0 | ... | 7.0 | 6.0 | 5.0 | 4.0 | 3.0 | 2.0 | 1.0 | 7.0 |
| 47 | 4.0 | =COUNT( D47, C47, C47, D47 ) | 1.0 | 2.0 | ||||||
| 48 | 4.0 | ... | 4.0 | 5.0 | ||||||
| 50 | 3.0 | =COUNT( CountRange ) | 4.0 | 5.0 | 6.0 | |||||
| 51 | 3.0 | =COUNT( C51:E51 ) | 1.0 | 2.0 | 3.0 | |||||
| 53 | 5.0 | =COUNT( C53:E53, G53, I53 ) | 1.0 | 2.0 | 3.0 | 100.0 | 5.0 | 200.0 | 7.0 | 7.0 |
| 54 | 5.0 | ... | 6.0 | 7.0 | 8.0 | 80.0 | 9.0 | 10.0 | 7.0 |
COUNTA
| A | B | C | D | E | ||
| 56 | 3.0 | =COUNTA( C56:E56 ) | 1.0 | true | ||
| 57 | 3.0 | ... | false | 5.0 | Hello |
AVERAGE (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 59 | 2.0 | =AVERAGE( C59:E59 ) | 1.0 | 2.0 | 3.0 | |||||
| 60 | 5.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 62 | 3.5 | =AVERAGE( C62:E63 ) | 1.0 | 2.0 | 3.0 | |||||
| 63 | 5.0 | =AVERAGE( C63:E63, C63:E63 ) | 4.0 | 5.0 | 6.0 | |||||
| 65 | 1.0 | =AVERAGE( C65 ) | 1.0 | |||||||
| 66 | 4.0 | ... | 4.0 | |||||||
| 68 | 1.5 | =AVERAGE( C68, D68 ) | 1.0 | 2.0 | ||||||
| 69 | 4.5 | ... | 4.0 | 5.0 | ||||||
| 71 | 2.0 | =AVERAGE( C71, D71, E71 ) | 1.0 | 2.0 | 3.0 | |||||
| 72 | 5.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 74 | 3.75 | =AVERAGE( C74, G74, D74, I74 ) | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 7.0 |
| 75 | 4.25 | ... | 7.0 | 6.0 | 5.0 | 4.0 | 3.0 | 2.0 | 1.0 | 7.0 |
| 77 | 1.5 | =AVERAGE( D77, C77, C77, D77 ) | 1.0 | 2.0 | ||||||
| 78 | 4.5 | ... | 4.0 | 5.0 | ||||||
| 80 | 5.0 | =AVERAGE( AverageRange ) | 4.0 | 5.0 | 6.0 | |||||
| 81 | 2.0 | =AVERAGE( C81:E81 ) | 1.0 | 2.0 | 3.0 | |||||
| 83 | 3.6 | =AVERAGE( C83:E83, G83, I83 ) | 1.0 | 2.0 | 3.0 | 100.0 | 5.0 | 200.0 | 7.0 | 7.0 |
| 84 | 8.0 | ... | 6.0 | 7.0 | 8.0 | 80.0 | 9.0 | 10.0 | 7.0 |
MIN (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 86 | -1.0 | =MIN( C86:E86 ) | -1.0 | 2.0 | -1.0 | |||||
| 87 | 4.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 89 | 1.0 | =MIN( C89:E90 ) | 1.0 | 2.0 | 3.0 | |||||
| 90 | 4.0 | =MIN( C90:E90 ) | 4.0 | 5.0 | 6.0 | |||||
| 92 | 1.0 | =MIN( C92 ) | 1.0 | |||||||
| 93 | 4.0 | ... | 4.0 | |||||||
| 95 | 1.0 | =MIN( C95, D95 ) | 1.0 | 2.0 | ||||||
| 96 | 4.0 | ... | 4.0 | 5.0 | ||||||
| 98 | 1.0 | =MIN( C98, D98, E98 ) | 1.0 | 2.0 | 3.0 | |||||
| 99 | -6.0 | ... | -4.0 | -5.0 | -6.0 | |||||
| 101 | 1.0 | =MIN( C101, G101, D101, I101 ) | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 7.0 |
| 102 | 1.0 | ... | 7.0 | 6.0 | 5.0 | 4.0 | 3.0 | 2.0 | 1.0 | 7.0 |
| 104 | 1.0 | =MIN( D104, C104, C104, D104 ) | 1.0 | 2.0 | ||||||
| 105 | 4.0 | ... | 4.0 | 5.0 | ||||||
| 107 | 4.0 | =MIN( MinRange ) | 4.0 | 5.0 | 6.0 | |||||
| 108 | 1.0 | =MIN( C108:E108 ) | 1.0 | 2.0 | 3.0 | |||||
| 110 | 1.0 | =MIN( C110:E110, G110, I110 ) | 1.0 | 2.0 | 3.0 | 100.0 | 5.0 | 200.0 | 7.0 | 7.0 |
| 111 | 6.0 | ... | 6.0 | 7.0 | 8.0 | 80.0 | 9.0 | 10.0 | 7.0 |
MAX (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 113 | 2.0 | =MAX( C113:E113 ) | -1.0 | 2.0 | -1.0 | |||||
| 114 | 6.0 | ... | 4.0 | 5.0 | 6.0 | |||||
| 116 | 6.0 | =MAX( C116:E117 ) | 1.0 | 2.0 | 3.0 | |||||
| 117 | 6.0 | =MAX( C117:E117 ) | 4.0 | 5.0 | 6.0 | |||||
| 119 | 1.0 | =MAX( C119 ) | 1.0 | |||||||
| 120 | 4.0 | ... | 4.0 | |||||||
| 122 | 2.0 | =MAX( C122, D122 ) | 1.0 | 2.0 | ||||||
| 123 | 5.0 | ... | 4.0 | 5.0 | ||||||
| 125 | 3.0 | =MAX( C125, D125, E125 ) | 1.0 | 2.0 | 3.0 | |||||
| 126 | -4.0 | ... | -4.0 | -5.0 | -6.0 | |||||
| 128 | 7.0 | =MAX( C128, G128, D128, I128 ) | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 7.0 |
| 129 | 7.0 | ... | 7.0 | 6.0 | 5.0 | 4.0 | 3.0 | 2.0 | 1.0 | 7.0 |
| 131 | 2.0 | =MAX( D131, C131, C131, D131 ) | 1.0 | 2.0 | ||||||
| 132 | 5.0 | ... | 4.0 | 5.0 | ||||||
| 134 | 6.0 | =MAX( MaxRange ) | 4.0 | 5.0 | 6.0 | |||||
| 135 | 3.0 | =MAX( C135:E135 ) | 1.0 | 2.0 | 3.0 | |||||
| 137 | 7.0 | =MAX( C137:E137, G137, I137 ) | 1.0 | 2.0 | 3.0 | 100.0 | 5.0 | 200.0 | 7.0 | 7.0 |
| 138 | 10.0 | ... | 6.0 | 7.0 | 8.0 | 80.0 | 9.0 | 10.0 | 7.0 |
VARP (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 140 | 0.0 | =VARP( C140 ) | 1.0 | |||||||
| 141 | 0.0 | ... | 4.0 | |||||||
| 143 | 0.25 | =VARP( C143, D143 ) | 1.0 | 2.0 | ||||||
| 144 | 0.25 | ... | 4.0 | 5.0 | ||||||
| 146 | 4.64 | =VARP( C146:E146, G146, I146 ) | 1.0 | 2.0 | 3.0 | 100.0 | 5.0 | 200.0 | 7.0 | 7.0 |
| 147 | 2.0 | ... | 6.0 | 7.0 | 8.0 | 80.0 | 9.0 | 10.0 | 7.0 |
VAR (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 149 | 0.5 | =VAR( C149, D149 ) | 1.0 | 2.0 | ||||||
| 150 | 0.5 | ... | 4.0 | 5.0 | ||||||
| 152 | 5.8 | =VAR( C152:E152, G152, I152 ) | 1.0 | 2.0 | 3.0 | 100.0 | 5.0 | 200.0 | 7.0 | 7.0 |
| 153 | 2.5 | ... | 6.0 | 7.0 | 8.0 | 80.0 | 9.0 | 10.0 | 7.0 |
STDEV (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 155 | 2.8284271247461903 | =STDEV( C155, D155 ) | -1.0 | 3.0 | ||||||
| 156 | 2.0816659994661326 | =STDEV( C156, D156, E156 ) | -1.0 | 0.0 | 3.0 | |||||
| 158 | 40.688718279617696 | =STDEV( C158:E158, F158:H158, I158 ) | -1.0 | 0.0 | 5.45 | 7.62 | 12.7 | 14.5 | -100.0 | 7.0 |
| 159 | 4.15187851918806 | ... | -10.0 | 0.0 | 1.0 | 0.0 | -1.0 | -2.0 | 3.0 | 7.0 |
STDEVP (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 161 | 0.0 | =STDEVP( C161 ) | 7.0 | |||||||
| 162 | 1.5 | =STDEVP( C162, D162 ) | 2.0 | 5.0 | ||||||
| 163 | 2.0548046676563256 | =STDEVP( C163, D163, E163 ) | 2.0 | 0.0 | 5.0 | |||||
| 165 | 37.6704332172529 | =STDEVP( C165:E165, F165:H165, I165 ) | -1.0 | 0.0 | 5.45 | 7.62 | 12.7 | 14.5 | -100.0 | 7.0 |
| 166 | 3.843892584878203 | ... | -10.0 | 0.0 | 1.0 | 0.0 | -1.0 | -2.0 | 3.0 | 7.0 |
AVEDEV (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 168 | 0.0 | =AVEDEV( C168 ) | 40.0 | |||||||
| 169 | 2.0 | =AVEDEV( C169, D169 ) | -1.0 | 3.0 | ||||||
| 170 | 1.5555555555555554 | =AVEDEV( C170, D170, E170 ) | -1.0 | 0.0 | 3.0 | |||||
| 172 | 26.092653061224492 | =AVEDEV( C172:E172, F172:H172, I172 ) | -1.0 | 0.0 | 5.45 | 7.62 | 12.7 | 14.5 | -100.0 | 7.0 |
| 173 | 2.693877551020408 | ... | -10.0 | 0.0 | 1.0 | 0.0 | -1.0 | -2.0 | 3.0 | 7.0 |
DEVSQ (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 175 | 0.0 | =DEVSQ( C175 ) | 40.0 | |||||||
| 176 | 8.0 | =DEVSQ( C176, D176 ) | -1.0 | 3.0 | ||||||
| 177 | 8.666666666666668 | =DEVSQ( C177, D177, E177 ) | -1.0 | 0.0 | 3.0 | |||||
| 179 | 9933.430771428571 | =DEVSQ( C179:E179, F179:H179, I179 ) | -1.0 | 0.0 | 5.45 | 7.62 | 12.7 | 14.5 | -100.0 | 7.0 |
| 180 | 103.4285714285714 | ... | -10.0 | 0.0 | 1.0 | 0.0 | -1.0 | -2.0 | 3.0 | 7.0 |
SKEW (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 182 | 0.9352195295828235 | =SKEW( C182, D182, E182 ) | 1.0 | 2.0 | 4.0 | |||||
| 184 | -2.532706643504132 | =SKEW( C184:E184, F184:H184, I184 ) | -1.0 | 0.0 | 5.45 | 7.62 | 12.7 | 14.5 | -100.0 | 7.0 |
| 185 | -1.8491263727532468 | ... | -10.0 | 0.0 | 1.0 | 0.0 | -1.0 | -2.0 | 3.0 | 7.0 |
KURT (does not support blanks!)
| A | B | C | D | E | F | G | H | I | ||
| 187 | 0.3905325443786989 | =KURT( C187, D187, E187, F187 ) | 1.0 | 2.0 | 4.0 | -1.0 | ||||
| 189 | 6.5478454980828005 | =KURT( C189:E189, F189:H189, I189 ) | -1.0 | 0.0 | 5.45 | 7.62 | 12.7 | 14.5 | -100.0 | 7.0 |
| 190 | 4.2380757608131585 | ... | -10.0 | 0.0 | 1.0 | 0.0 | -1.0 | -2.0 | 3.0 | 7.0 |
SUMSQ
| A | B | C | D | E | F | G | H | I | ||
| 192 | 49.0 | =SUMSQ( C192 ) | 7.0 | |||||||
| 193 | 29.0 | =SUMSQ( C193, D193 ) | 2.0 | 5.0 | ||||||
| 194 | 29.0 | =SUMSQ( C194, D194, E194 ) | 2.0 | 5.0 | ||||||
| 196 | 10460.3069 | =SUMSQ( C196:E196, F196:H196, I196 ) | -1.0 | 0.0 | 5.45 | 7.62 | 12.7 | 14.5 | -100.0 | 7.0 |
| 197 | 115.0 | ... | -10.0 | 1.0 | 0.0 | -1.0 | -2.0 | 3.0 | 7.0 |
COVAR
| A | B | C | D | E | F | G | H | ||
| 199 | 0.6666666666666666 | =COVAR( C199:E199, F199:H199 ) | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | |
| 200 | -0.1111111111111111 | ... | 2.0 | 3.0 | 2.0 | 2.0 | 2.0 | 3.0 | |
| 202 | 0.25 | =COVAR( C202:D202, E202:F202 ) | 1.0 | 2.0 | 3.0 | 4.0 | |||
| 203 | !NA | =COVAR( C203:E203, F203:G203 ) | 2.0 | 3.0 | 5.0 | 4.0 | 5.0 | Excel says: #N/A |
SUMIF
| A | B | C | D | E | F | G | H | I | ||
| 205 | 43.0 | =SUMIF( D205:F205, (">" & C205), G205:I205 ) | 10.0 | 10.0 | 11.0 | 12.0 | 20.0 | 21.0 | 22.0 | 7.0 |
| 206 | 23.0 | =SUMIF( D206:F206, (">" & C206) ) | 10.0 | 10.0 | 11.0 | 12.0 |