Cells And Values In AFC
AFC supports the following types of values in cells:
- strings
- numbers (including monetary and percentage values)
- dates and times
- booleans
Internally, AFC really only supports two basic data types: numbers and strings. This suffices because Excel internally treats dates, times, and booleans as numbers, too.
For a particular engine compiled by AFC, the numbers are all of the same type. The default is the double type, which is what Excel uses as well. See the tutorial for details on the available numeric types and how to choose one.
Cell References
AFC supports the following styles of cell and range references:
Relative reference
| A | B | C | ||
| 2 | 1.0 | =C2 | 1.0 |
Absolute references
| A | B | C | ||
| 3 | 6.0 | =(($C$3 + $C3) + C$3) | 2.0 |
Simple name
| A | B | C | ||
| 4 | 3.0 | =MYCELL | 3.0 |
Two names for same cell
| A | B | C | ||
| 5 | 8.0 | =(_MY_CELL_ + _MY_CELL) | 4.0 |
Unconventional names
| A | B | C | D | E | F | G | ||
| 6 | 5.0 | =_MY.CELL.1X | 5.0 | |||||
| 7 | 6.0 | =MY23.43CELL | 6.0 | |||||
| 8 | 7.0 | =_12345 | 7.0 | |||||
| 9 | 400.0 | =((((A1B + A1_) + A1_1) + A_1) + A__1) | 100.0 | 90.0 | 80.0 | 70.0 | 60.0 |
Range with cell references
| A | B | C | D | E | ||
| 11 | 6.0 | =SUM( C11:E11 ) | 1.0 | 2.0 | 3.0 |
Range name
| A | B | C | D | E | ||
| 12 | 15.0 | =SUM( Range ) | 4.0 | 5.0 | 6.0 |
References to other sheets
| A | B | C | D | ||
| 14 | 30.0 | =(SecondSheet!B1 + CellOnThirdSheet) | 10.0 | 20.0 | |
| 15 | 30.0 | ='Sheet with spaces'!B1 | 30.0 | ||
| 16 | 40.0 | ='Sheet-with-hyphens'!B1 | 40.0 | ||
| 17 | 50.0 | ='Sheet''with''quotes'!B1 | 50.0 |
Empty Cells
AFC currently has no proper support for empty cells. It simply treats them like the number zero (0.0). This is usually correct (even for multiplication with *, where Excel treats empty cells as zero too), but fails most noticeably in aggregators like COUNT, AVERAGE, or PRODUCT (where Excel skips empty cells):
Empty cells with +
| A | B | C | D | ||
| 2 | 5.0 | =(C2 + D2) | 2.0 | 3.0 | |
| 3 | 5.0 | ... | 5.0 | ||
| 4 | 6.0 | ... | 6.0 |
Empty cells with *
| A | B | C | D | ||
| 6 | 6.0 | =(C6 * D6) | 2.0 | 3.0 | |
| 7 | 0.0 | ... | 5.0 | ||
| 8 | 0.0 | ... | 6.0 |
Empty cells with PRODUCT return 0!
| A | B | C | D | |||
| 10 | 6.0 | =PRODUCT( C10, D10 ) | 2.0 | 3.0 | ||
| 11 | 0.0 | ... | 5.0 | Excel says: 5.0 | ||
| 12 | 0.0 | ... | 6.0 | Excel says: 6.0 |
Empty cells with SUMIF
| A | B | C | D | E | ||
| 14 | 10.0 | =SUMIF( C14:E14, "=10" ) | 10.0 |
Formula Errors
AFC throws exceptions or returns error values for Excel error values:
Explicitly entered errors
| A | B | |||
| 2 | !NA | #N/A | Excel says: #N/A | |
| 3 | !NA | =NA() | Excel says: #N/A | |
| 4 | !FE | #NUM! | Excel says: #NUM! | |
| 5 | !FE | #VALUE! | Excel says: #VALUE! | |
| 6 | !FE | #DIV/0! | Excel says: #DIV/0! |
Errors as inputs
| A | B | C | D | |||
| 7 | !FE | =(C7 + D7) | 1.0 | #NUM! | Excel says: #NUM! | |
| 8 | !FE | ... | #NUM! | 2.0 | Excel says: #NUM! |
Actual division by zero
| A | B | C | |||
| 9 | !+Inf/AE | =(1.0 / C9) | 0.0 | Excel says: #DIV/0! |
ISNA()
| A | B | C | ||
| 11 | true | =ISNA( C11 ) | #N/A | |
| 12 | false | ... | 4711.0 | |
| 13 | false | ... | #NUM! |
ISERR()
| A | B | C | ||
| 15 | false | =ISERR( C15 ) | #N/A | |
| 16 | false | ... | 4711.0 | |
| 17 | true | ... | #NUM! |
ISERROR()
| A | B | C | ||
| 19 | true | =ISERROR( C19 ) | #N/A | |
| 20 | false | ... | 4711.0 | |
| 21 | true | ... | #NUM! |
COUNTA() is OK
| A | B | C | D | E | ||
| 23 | 3.0 | =COUNTA( C23:E23 ) | #N/A | 4711.0 | #NUM |
COUNT() is currently broken
| A | B | C | D | E | |||
| 24 | 3.0 | =COUNT( C24:E24 ) | #N/A | 4712.0 | #NUM | Excel says: 1.0 |
Numeric Precision
AFC ignores the number of decimal places specified for displaying cell results in Excel. If you want rounded results, you have to use the ROUND() function explicitly. This is consistent with Excel also not limiting the precision on intermediate results.