# 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.