{%hackmd aPqG0f7uS3CSdeXvHSYQKQ %}

:::info
## TODO

+ [ ] Complete the notes for lectures before midterm
+ [ ] Notes for "Date and time"
+ [ ] Notes for "Arrays"
+ [ ] Notes for "Manipulating text strings and data formatting"
+ [ ] Rewrite the sentences shamelessly copied from the professor's slides so that this note seems not to be plagiarized from them.
:::

:::success
## Tips for Final
+ https://www.dcard.tw/f/ntu/p/255537175
:::

# Excel Notes
[TOC]

## Value Types (Formats)
1. Text (e.g. `"Apple"`, `"TRUE"`, `"48763"`)
2. Number (Numeric Values) (e.g. `48763`)
3. Boolean (Logical Value) ::=`TRUE` | `FALSE`
4. Error message (e.g. `#DIV/0!`, `#NAME?`)

::: danger
### Number Precision
Excel stores *numbers* (numeric values) as **floating-point** numbers, and stores only ==**15** effective digits== for each number.

| Initial Input Value / Value Returned by Expression | Adjusted Input Value | `LEN(value)` |
|:-------------------:|:--------------------:|:------------:|
| 1234567890123456789 | 1234567890123450000 | 15+4=19 |
| 0.0001234567890123456789 | 0.00123456789012345 | 15+4=19 |
| 1234567890.123456789 | 1234567890.12345 | 15+1=16 |
| 1/3 = 0.33333333333333333... | 0.333333333333333 | 15+2=17 |
| 1/4 = 0.25 | 0.25 | 2+2=4 |
:::

:::danger
### Blank Value
+ When a cell has only the blank value, the blank value is displayed as number `0`.
+ The blank value can be of type text, number, or boolean. It has different values in different types:
+ text: `""` (empty string)
+ number: `0`
+ boolean: `FALSE`
+ When reading from an empty cell using cell reference, the cell reference will return a blank value. However, empty cells does NOT contain blank values because they are *empty*.
+ e.g. if A1 is empty and A2 is inputted `=A1`, then A2 will contain the blank value as a result, which is displayed as `0` (the value of the blank value in number format).
:::
:::warning
Format of value (i.e. *type*) is orthogonal to format of cell (e.g. date, currency, text, number, percentage, scientific notation, ...).
:::
:::success
### Tips
+ How to escape double quote (`"`) character?
-> Prepend an additional double quote (`"`) to tell Excel to treat this `""` as a literal `"`. (i.e., `""`->`"` in double quotes) [(ref)](https://stackoverflow.com/a/216623)
:::

### Error Values
+ `#DIV/0`: ZeroDivisionError. Returned whenever a number is divided by 0.
+ ==If a formula (expression) contains `#DIV/0` as a subformula, then it evaluates to `#DIV/0` too.==
+ `#N/A`: Not Available. Returned when a formula **failed to find** what it had been told to find.
+ `#NAME?`: Name not defined
+ e.g. having `SIM` in a formula while there is no function named `SIM`.
+ `#REF!`: Cell reference error. Possibly caused by referring to cells that are **out of range** or **cannot be found**.
+ `#VALUE!`:
+ caused by **invalid type**, i.e., the type of an argument cannot be converted into the type of the parameter that the function expects
+ e.g. `"Hi!" * 6`-> `#VALUE!`, where `*` fails to convert `"HI!"` from type text into type number.
+ somestimes a function returns `#VALUE!` normally.
+ e.g. `FIND` and `SEARCH` return `#VALUE!` to indicate the substring is not found in the given string.
+ `#NULL!`: Cell reference or cell reference range refers to a **null collection of cells**.
+ `#NUM!`: Returned when function arguments have **invalid values**
+ e.g. `LOG10(0)`-> `#NUM!`

## Inputting Formula/Text
+ How to enter a formula (say, `<formula>`) in a cell?
+ Input `=<formula>` and press `enter` (or `ctrl`+`shift`+`enter` to input as an array formula).
+ Apostrophe (`'`) in the beginning of a cell:
+ Leading apostrophes force Excel to treat the cell’s contents as a text value.
+ Not involved in (have no effect on) calculation.
+ e.g. If cell A1 has value `'hello`, then formula `LEN(A1)` will return `5` instead of `6`. (That is, leading `'` in a cell is always ignored by formulas.)
+ (Though not being true,) `'<some_text>` can be viewed as a syntax sugar (shorhand) for `="<some_text>"`.
+ [Reference](https://superuser.com/a/1701046)

## Cell Reference
+ Reference to a single cell: `<col><row>`. e.g. `A10`
+ Reference to a range of cells: `<col1><row1>:<col2><row2>`. e.g. `A10:D220`
+ Reference to the range of whole row or column: `row_id1:row_id2`/ `column_id1:column_id2`.
+ e.g. `M:P`, `2:20`
+ `$` can still be used to fix row or column. e.g. `$M:P`, `2:$20`
+ Reference to cells in other worksheets: `<worksheet_name>!<cell_reference>`
+ e.g. `SheetA!A10:D220`
+ If the worksheet name contains space (` `), enclose the worksheet name by ==single== quotations.
+ e.g. `'Starburst Stream'!A10:D220`
+ You can also target a range of worksheets by `<worksheet_name1>:<worksheet_name2>!<cell_reference>`. If any of the referenced worksheet names contains space, enclose the whole referecnce to the range of worksheets by ==single== quotation.
+ e.g. `Sheet1:StarburstStream!A10:D220`, `'Sheet1:Starburst Stream'!A10:D220`
+ Reference to cells in another file: `[<filename>]<worksheet_reference>!<cell_reference>`

:::danger
### Copy vs. Cut
+ When using copy or cut to a cell, it is the formula in the cell that is copied/cut rather than its returned value.
+ Difference b/w copy and cut:
+ copy and paste -> cell references in formulas will be auto-adjusted.
+ cut and paste -> cell references in formulas will NOT be auto-adjusted.
:::

## Operator Precedence
+ Different priority orders:
1. Union operators: `:`, ` ,`/ Intersection operator: ` `(space)
2. `-` (negative sign)
3. `%`
4. `^` (exponent)
5. `*` (mul), `/` (div)
6. `+` (add), `-` (sub)
7. `&` (join operator)
8. Comparison operators: `=`, `>`, `<`, `>=`, `<=`, `<>`
+ Same priority order:
+ Parse from ==left to right.==

## Basic Functions
+ `ISNUMBER(x: any)`->boolean, `ISTEXT(x: any)`->boolean, `ISLOGICAL(x: any)`->boolean, `ISERROR(x: any)`->boolean
+ Returns `TRUE` if `x` is in number/text/boolean/error format.
+ e.g. `ISNUMBER("12")`->`FALSE`, `ISNUMBER(12)`->`TRUE`
+ `ISBLANK(x: any)`-> boolean
+ `INDEX(cell reference range, relative row number: number, relative column number: number)`->any
+ Returns the value of the cell at the relative row and column in the specified cell range.
+ relative row/column number == `0` or blank value:
+ return the **entire column/row entire column/row) as an array** (i.e. select cells in all rows/columns in the given column/row)
+ e.g. `INDEX(B3:D10, 2,)` == `INDEX(B3:D10, 2, 0)` == `{B4, C4, D4}`
+ `ADDRESS(row number: number, col number: number, format: number)`-> text
+ `format` determines the format of the cell reference string:
+ 1 (default): fix both row and column
+ 2 -> fix row
+ 3 -> fix column
+ 4 -> no fix
+ `INDIRECT(cell reference string: text)`-> cell reference
+ Parse the **string** of cell reference / cell reference range and return the corresponding cell reference / cell reference range.
+ Analogous to `eval` in programming languages.
+ `ROW(cell reference/cell reference range)`-> number / `COLUMN(cell reference/cell reference range)`-> number
+ Returns the row/column index of the given cell reference
+ e.g. `ROW(B3)`-> 3, `COLUMN(B3)`-> 2
+ If the input is a cell reference range, then an array of row/column indices is returned:
+ e.g. `ROW(A3:A10)`->`{3,4,5,6,7,8,9,10}`
+ `OFFSET(cell reference, row offset: number, column offset: number, # of rows: number, # of columns: number)`-> cell reference
+ Returns the cell reference/cell reference range specified by the number of rows and columns from the cell reference with the specified # of rows and # of columns.

## Comparison operators
+ not equal: `<>`
+ When a character (string of length 1) is compared with another character, it will be:
1. Converted to lowercase if it is an uppercase alphabet.
2. Converted to its ASCII value
3. Compared.
+ When a string (text) is compared (with, say, another string), it is compared in **lexicographic order**. That is, the strings are compared character-by-character, where each character is compared by the corresponding value (using the aforementioned conversion method).
+ ==Case Insensitive when compared==: Text (strings) are converted to lowercase if possible when compared.
+ However, **any string (including characters) is larger than (pure) numbers and smaller than booleans**.
+ TRUE/FALSE > any number
TRUE/FALSE > any text (string)
+ TRUE > FALSE

| Expression | Evaluation | Comment |
|:-------------------------:|:----------:| --- |
| `"A"<"Z"` | TRUE | `A`->`a`->97, `Z`->`z`->122 (**case insensitive**) |
| `"A"="a"` | TRUE | `A`->`a`->97 (**case insensitive**) |
| `"gas"<"gold"` | TRUE | by lexicographic order |
| `"gas"<"GOLD"` | TRUE | `"GOLD"` will be converted to lower case `"gold"` (**case insensitive**) |
| `"Z">1145141919810` | TRUE | |
| `TRUE>1145141919810` | TRUE | |
| `TRUE>"Z"` | TRUE | |
| `TRUE>"StarburstStream"` | TRUE | |
| `FALSE>"StarburstStream"` | TRUE | |
| `TRUE>FALSE` | TRUE | |

:::danger
`TRUE`>`FALSE`>text (strings, including characters)>numbers
where text are compared in lexicographic order and are case insensitive when cmopared.
:::

## Arithmetic
+ `SQRT`
+ `POWER(x: number, y: number)`->number
+ Returns $x^y$
+ `EXP(y: number)`->number
+ Returns $e^y$
+ `LOG`
+ `LOG10`
+ `LN`

### Rounding
+ `ROUND(x: number, k: number)`->number
+ `k`: an integer, **can be negative**
+ Rounds `x` to the `k`-th digit after the decimal point
+ e.g.
+ `ROUND(x, 2)`==`MROUND(x, 100)` returns the closet multiple of 0.01 to `x`
+ `ROUND(x, 0)`==`MROUND(x, 1)` returns the closet integer to `x`
+ `ROUND(x, -2)`==`MROUND(x, 100)` returns the closet multiple of 100 to `x`
+ `MROUND(x: number, y: number)`->number
+ `y` can be any real number
+ Rounds `x` to the closet multiple of `y`
=> `ROUND(x, k)`==`MROUND(x, POWER(10, -k))`
+ `ROUNDUP(x: number, k: number)`->number
+ `k`: an integer, **can be negative**
+ e.g. 8.9->9, -8.9->-9
+ `TRUNC(x: number, k: number)`->number / `ROUNDDOWN(x: number, k: number)`->number
+ `k`: an integer, **can be negative**
+ `TRUNC` == `ROUNDDOWN`
+ e.g. 8.9->8, -8.9->-8
+ `INT(x: number)`-> number
+ e.g. 8.9->8, -8.9->-9
+ `ABS`

## String (Text) Processing
+ `TEXT(x: number, format: text)` -> text
+ Converts the **numeric** value `x` to the text in specified format `format`.
+ `format`:
+ **Digit Placeholders: `0`, `#`, `?`**:
+ `0`: displays 0 for non-significant digits (digits that can be ignored without changing the value). Significant digits are displayed as they are.
+ e.g. `TEXT(1.200333, "000.000")`-> `001.200`
+ `?`: displays space (` `) for non-significant digits (digits that can be ignored without changing the value). Significant digits are displayed as they are. **(Trailing/leading 0's are replaced by spaces)**
+ e.g. `TEXT(1.200333, "???.???")`-> ` 1.2 `
+ `#`: only displays significant digits. **(Truncate trailing/leading 0's)**
+ e.g. `TEXT(1.200333, "###.###")`-> `1.2`
+ e.g. `TEXT(0.21, "###.###")`-> `.21`
+ e.g. `TEXT(21, "###.###")`-> `21.`
+ Fraction:
+ Improper fraction(假分數): `?/?`, `?/??`, `??/????`, ...
+ Proper fraction(帶分數): `# ?/?`, `# ?/??`, `# ??/????`, ...
+ Scientific notation:
+ e.g. `0E+0`, `0.00E+00`
+ Percentage/Thousands:
+ `%` is at the end of `format` -> the (floating-point) number is multiplied by $100$ and suffixed by $\%$
+ e.g. `TEXT(0.456, "000.00%")`->`TEXT(45.6, "000.00")&"%"`->`045.60%`
+ `,` exists in `format` and there are no placeholders (`0`, `#`, `?`) between `,` and `.` (if exists) -> the (floating-point) number is divided by $1000$
+ e.g. `TEXT(123556.789, "0,")`->`TEXT(123.556789, "0")`->`124`
+ e.g. `TEXT(123556.789, "0,.00")`->`TEXT(123.556789, "0.00")`->`123.56`
+ Date and Time:
+ (TBA)
::: info
#### Examples
+ `TEXT(0.1234567890123456789, "0,00000.000%")`
1. Only 15 effective digits are stored for floating-point numbers
+ 0.1234567890123456789 -> 0.123456789012345
2. % -> display in percentage
+ 0.123456789012345 -> 12.3456789012345%
3. 6 `0`'s on the left of the decimal point (`.`)
+ 12.3456789012345% -> 000012.3456789012345%
4. 3 `0`'s on the right of the decimal point (`.`) -> ==round== to the 3rd digit after the decimal point
+ 000012.3456789012345% -> 000012.346%
6. `,` is included in `format` -> enable **thousands separator**
+ 000012.346% -> 000,012.346%
+ => Result: 000,012.346%
:::
+ `CHAR(n: number)` -> text
+ `n`: an integer in $[0,255]$
+ Returns the character whose number in the character set (e.g. ASCII) used by the OS is `n`.
+ `CODE(s: text)` -> number
+ Returns the corresponding number (e.g. ASCII code) of the **FIRST** character of `s` in the character set used.
+ `SEARCH(x: text, y: text[, n: number])`->number / `FIND(x: text, y: text[, n: number])`->number
+ `n`: the starting index to find `x` in `y`. Defaults to `1`.
+ Returns the index (i.e., position) of the **first** substring `x` within string `y[n:]` (`[n:]` means string `y` starting at index `n`).
+ Returns `#VALUE!` if `y[n:]` does not contain `x` (not found)
+ ==1-based indexing==: e.g. `SEARCH("str", "strbbbbbbb")`->`1`
+ `SEARCH`-> ==Case insensitive==: e.g. `SEARCH("STR", "aastrbb")`->`3`
+ `FIND`-> ==Case sensitive==: e.g. `FIND("STR", "aastrbb")`->`#VALUE!`
+ `REPT(s: text, n: number)`->text:
+ Repeat the text $s$ for $n$ times.
+ That is, return $s^n = \overbrace{s\ldots s}^{n}$.
+ `REPLACE`:
+ `SUBSTITUTE`:
+ ==Case sensitive==

## Conditionals (IFs)
+ `IF`
+ `IFERROR`
+ `IFNA`
+ `IFS`
+ If no boolean expressions evaluate to `TRUE`, `#N/A` (not available) is returned meaning that the target return value is NOT FOUND.
+ `SWITCH`
+ Similar to `IFS`, if no values are are matched, `#N/A` (not available) is returned meaning that the target return value is NOT FOUND.

## Statistical Functions
+ `COUNT(value1: any|array[any] [, value2: any|array[any], ...])`->number
+ Returns the number of values (either as arguments or in referenced cells) that are in number format
+ Values provided as arguments will be converted into number format if possible. However, values in referenced cells will NOT be converted into number format.

:::danger
For several functions including `COUNT`:
Excel will attempt to convert types of arguments to what the function expects if needed.
In contrast, Excel will NOT convert the types of values in referenced cells.
:::
+ `COUNTA(value1: any|array[any] [, value2: any|array[any], ...])`->number
+ Count all **non-empty** cells or values:
+ number -> counted (even counted by `COUNT`)
+ text -> 0 -> counted
+ `TRUE` -> 1 -> counted
+ `FALSE` -> 0 -> counted
+ empty cell -> **NOT** counted
+ `SUM(value1: any|array[any] [, value2: any|array[any], ...])`->number
+ `PRODUCT(value1: any|array[any] [, value2: any|array[any], ...])`->number
+ `SUMPRODUCT(x: array[any], y: array[any])`->number
+ Calculate the dot product of vectors x and y
+ Ignore non-numeric pairs in x and y.
+ e.g. `x`=`{1,TRUE,3}`, `y`=`{4,5,6}` -> `SUMPRODUCT(x,y)`=$\mathbf{x}\cdot \mathbf{y} = 1\cdot 4 + 3\cdot 6$
+ `MAX(value1: any|array[any] [, value2: any|array[any], ...])`-> number
+ `MIN(value1: any|array[any] [, value2: any|array[any], ...])`-> number
+ `LARGE(values: array[any], k: number)`-> number
+ Returns the k-th largest number in the given array (cell ref range)
+ `SMALL(values: array[any], k: number)`-> number
+ Returns the k-th smallest number in the given array (cell ref range)
+ `CORREL(x: array[any], y: array[any])`-> number
+ Returns the correlation coefficient of x and y
+ `AVERAGE` / `AVERAGEA`
+ `STDEV` family: standard deviation
+ `STDEV` / `STDEVA`
+ `STDEVP` / `STDEVPA`
+ `STDEV.S`
+ `STDEV.P`
+ `VAR` family: variance
+ `VAR` / `VARA`
+ `VARP` / `VARPA`
+ `VAR.S`
+ `VAR.P`
+ `MEDIAN`
+ `MODE` / `MODE.MULT`

### Combined with Conditions

+ `COUNTIF(cell reference range, criteria)`->number
+ `=` can be omitted if it is the only operator in criteria.
+ e.g. `COUNTIF(A1:B2, 33)` == `COUNTIF(A1:B2, "=" & 33)` == `COUNTIF(A1:B2, "=33")`
+ Criteria argument does NOT support `AND()`/`OR()`/`NOT()`. Besides, directly putting `AND()`/`OR()`/`NOT()` in criteria will result in `AND(...)`/`OR(...)`/`NOT(...)` being evaluated first, and then merged into text (as the criteria in text form).
+ Conjunction: To count the number of cells or tuples of cells with multiple criteria, use `COUNTIFS` instead.
+ Disjunction: To count the number of cells or tuples of cells that satisfy either one of several criteria, add the results of `COUNTIF` and/or `COUNTIFS`.
+ Criteria in its text format will NOT parse cell reference.
+ e.g. `COUNTIF(A1:D4, ">A3")` will compare all values to `"A3"` rather than the value of cell A3.

:::success
### Wildcards
+ Only available in *criteria* argument of statistical conditional functions (e.g. `COUNTIF`, `COUNTIFS`, `AVERAGEIFS`).
#### `*`
+ Match ANY non-empty string (text), i.e., string of length >0
+ This is different from the `*` in bash.
#### `?`
+ Match ANY single character, i.e., string of length == 0

#### Escape Characters -> Use `~`
+ `~*` represents the character `*`
+ `~?` represents the character `?`
:::

:::danger
**Comparison operators and `COUNTIF` follow different comparison guidelines.**
+ `COUNTIF` will detect and convert values in both cell reference range
and criteria to their **most likely format**
+ Values of different formats are incomparable in `COUNTIF`, thereby not being counted.


:::

+ `COUNTIFS(cell reference range 1, criteria 1, cell reference range 2, criteria 2, ..., cell reference range n, criteria n)`->number
+ Returns the number of $i$ ($1\le i\le n$) such that the $i$th cell in cell reference range j meets criteria j for all j=1,2,...,n.
+ Therefore, the cell reference ranges as arguments have to be of the **same shape**.
+ `AVERAGEIF`
+ `AVERAGEIFS`

## Lookup functions
+ `VLOOKUP(target value, cell reference range, relative column number of the returned cell: number, match method: boolean)`-> any
+ abbr. for vertical lookup
+ Suppose `relative column number of the returned cell` = i.
`VLOOKUP` returns the value of the cell that are both:
1. in ith column of the cell reference range and
2. in the last *(if approx match)* or first *(if exact match)* row whose value of the cell in the first column matches target value.
+ Returns `#N/A` if no match found
+ For exact match, `#N/A` is returned only if there is no cell in the first (relative) column that has the target value.
+ For Approximate match, `#N/A` is returned only if all values in the first (relative) column are greater than the target value.
+ match method:
+ `TRUE` (default) -> Approximate match
+ Matches the **largest value that is not greater** than the target value ==ONLY IF the first column of the specified range is pre-sorted in ascending order==.
+ Return the **last** match result (i.e., the one with the largest row number) if there are multiple matches
+ `FALSE` -> Exact match
+ Match the **first** row (i.e., the one with the smallest row number) whose value of the cell in the first column equals target value.
+ Use `=` to compare values -> ==case insensitive==

+ `HLOOKUP(target value, cell reference range, relative row number of the returned cell: number, matching method: boolean)`-> any
+ abbr. for horizontal lookup
+ similar to `VLOOKUP`
+ `LOOKUP(target value, single row/col ref range for target value, [single row/col ref range for return value])`-> any
+ If cell ref range for return value is not provided, it is set as the cell ref range for target value
+ cell ref ranges for target value and return value:
1. have to be **SINGLE** row/column
2. no need to be of same dimensions (i.e., they can be in different directions and of different lengths)
3. `LOOKUP` will ==extend the range for return value towards its long side== to match lookup results if necessary
+ If return range is single cell, then by default will extend ==horizontally==
+ ==approximate match only==
+ `#N/A` is still returned if no matches are found.

+ `XLOOKUP(target value, single row/col ref range for target value, single row/col ref range for return value, [return value if no matches], [match method: number], [search algorithm: number])`-> any
+ basically `LOOKUP` but with advanced options
+ `return value if no matches`:
+ default to `#N/A`
+ match method:
+ 0 (default): exact match
+ -1: matches greatest value no greater than the target value
+ 1: matches smallest value no less than the target value
+ 2: exact match, but the target value can contain wildcards (`*`, `?`) for matching
+ search algorithm:
+ 1 (default): search from the 1st cell
+ -1: search from the last cell
+ 2: binary search. The cell ref range to search for the target value has to be pre-sorted in ==ascending order==
+ -2: binary search. The cell ref range to search for the target value has to be pre-sorted in ==descending order==

+ `MATCH(target value, cell reference range, match method: number)`-> number
+ Returns the **relative** row/column number of the target value within specified range.
+ Cell reference range has to be **single row/column**
+ match method:
+ `0`: exact match. match the ==first== cell if multiple are matched.
+ `1`: match the ==last==(if multiple are matched) greatest value no greater than target value, requires being pre-sorted in **ascending order**
+ `-1`: match the ==first==(if multiple are matched) smallest value no lesser than target value, requires being pre-sorted in **descending order**
+ That is, the **closest** cell is matched if approximate match; the first cell is matched if exact match.

:::success
`VLOOKUP` can be implemented by `INDEX(..., MATCH(...), ...)`
:::

+ `CHOOSE(i: number, x_1: any, [x_2: any], ...)`-> any
+ Returns $x[i]$, i.e., the `i`th argument after `i`.
+ `x_j` (for any j) can be either a single value (might be a reference to a cell) or cell reference RANGE.
+ a multiplexer

## Randomness
+ `RAND()` -> number
+ Generate a random **real number** (floating-point) within interval $[0, 1)$.
+ `RANDBETWEEN(low: number, high: number)` -> number
+ Generate a random **integer** within interval $[low, high]$.

## Date and Time
:::danger
### There's no "date" type. Times/Dates are all numbers!
+ If you directly input (i.e. without using `=<formula>`) a value in the form of time or date into a cell of format ***General Format***, Excel will store NOT the text you've just inputted but the corresponding serial **number** in the cell. This means that the value in the cell is actually of type **number** instead of **text**. Besides, Excel will change the **format of the cell** from General format to **Date**.
+ For example, if the input of a cell is `1905/7/15`, then the resulting value in the cell will be `2023`(type: number) rather than `1905/7/15`(type: text). However, what the cell will end up showing is `1905/7/15` (instead of `2023`!). This is because the format of the cell has been set to **Date**, so the actual value in the cell is `2023` (type: number) but shown as `1905/7/15`(type: number).
+ That is, regarding values:
`1905/7/15`(type: number, cell format: Date) == `2023`(type: number, cell format: Generic) != `1905/7/15`(type: text)
+ However, RHS can actually be converted to LHS!!!
:::
:::danger
+ Text in the form of date/time can be converted to number (whose value is the corresponding serial number):
+ ✅ `"1905/7/15" + 1` -> `2023 + 1` -> `2024`
+ ❌ `"1905/7/15" + 1` -> `#VALUE!` since `"1905/7/15"` (type: text) cannot be converted into a number
:::

| Expression | Evaluation | Comment |
|:-------------------------:|:----------:| --- |
|`"1905/7/15"`|`"1905/7/15"`|
|`"1905/7/15"+1`|`2024`|`"1905/7/15"`(type: text) -> `2023`(type: number) |
|`"1900/1/1"+"1900/1/2"`|`3`|`"1900/1/1`(type: text) -> `1`(type: number) `"1900/1/2`(type: text) -> `2`(type: number)|


+ `WEEKDAY`
+ 2: monday(1) ~ sunday(7)
+ `WEEKNUM`
+ `WORKDAY`
+ `NETWORKDAYS`
+ `NETWORKDAYS.INTL`
+ `EDATE`
+ `EOMONTH`

## Arrays
+ When to enter a formula as an array formula?
+ When you want function applications to be able to be distributed into arrays.
+ How to enable array parsing in a formula? (Such formula is called an **array formula**.)
+ `Ctrl`+`Shift`+`Enter` instead of `Enter` after entering a formula into a cell.

:::danger
### What if some selected cells are NOT covered by the returned array?
+ Case 1: the array returned (by the array formula) is a single row or column
+ Then Excel will truncate the array to fit in the selected cell range, and copy the array to ALL rows/columns.
+ If there are still cells that are not covered by (duplicates) of the returned array, assign `#N/A` to these cells.
+ e.g.
+ Single row: select a $5\times 3$ cell range and input `={1,2,3,4}` as an array formula
+ Single column: select a $5\times 3$ cell range and input `={1;2;3;4}` as an array formula
+ Case 2: the array returned (by the array formula) spans across multiple rows and columns
+ Then Excel will only truncate the array to fit in the selected cell range. It will NOT copy the array to fill the rest of the cells.
+ If there are still cells that are not covered by (duplicates) of the returned array, assign `#N/A` to these cells.
+ e.g. select a $5\times 3$ cell range and input `={1,2;3,4}` as an array formula

:::

:::danger
### What if a formula should be inputted as an array formula, but is instead inputted as a regular formula?

:::

:::warning
If the shape of arrays in formula do NOT match, then `#N/A` is returned for mismatched entries.

e.g. `{1,2,3,4,5,6}-{0,1,2,3}` -> `{1,1,1,1,#N/A,#N/A}` (both arrays should be of the same size)
:::

+ `TRANSPOSE(arr: array[any])`-> array[any]
+ Returns the transpose of the given array.
+ Analogous to matrix transposition

# Questions
+ wrong expression?
+ why not returning C?
+ why not returning FALSE?