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.
AFC supports the following styles of cell and range references:
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):
| A | B | C | D |
2 | 5.0 | =C2+D2 | 2.0 | 3.0 | |
3 | 5.0 | ... | | 5.0 | |
4 | 6.0 | ... | 6.0 | | |
| A | B | C | D |
6 | 6.0 | =C6*D6 | 2.0 | 3.0 | |
7 | 0.0 | ... | | 5.0 | |
8 | 0.0 | ... | 6.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 |
AFC throws exceptions or returns error values for Excel error values:
| 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! |
| A | B | C | D |
7 | !FE | =C7+D7 | 1.0 | #NUM! | | Excel says: #NUM! |
8 | !FE | ... | #NUM! | 2.0 | | Excel says: #NUM! |
| A | B | C |
9 | !+Inf/AE | =1.0/C9 | 0.0 | | Excel says: #DIV/0! |
| A | B | C |
11 | true | =ISNA(C11) | #N/A | |
12 | false | ... | 4711.0 | |
13 | false | ... | #NUM! | |
| A | B | C |
15 | false | =ISERR(C15) | #N/A | |
16 | false | ... | 4711.0 | |
17 | true | ... | #NUM! | |
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.