Breaking News: Grepper is joining You.com. Read the official announcement!

spreadsheet functions

Innocent Iguana answered on March 13, 2024 Popularity 8/10 Helpfulness 4/10

answer spreadsheet functions

spreadsheet functions

Comment

- Filtering : `=FILTER(A2:H20,H2:H20=TRUE)` (Only return result of A2 to H20 if the condifition of H2 to H20 is true)
- Checking data-type: `=ISNUMBER(A1)`, `"=ISTEXT(A1)`, `=ISDATE(A1)`, `=ISLOGICAL(A1)`,  `=ISURL(A1)`, `=ISFORMULA(A1)`
- converting to other data-type : `=TO_DOLLARS(A1)`, `=TO_PERCENT(A1)`, `=N(A1)`
- convert to another unit : `=CONVERT(A1,"ft^2","m^2")`
- If-else : `=IF(A1,1,0)`
- To make the text into a clickable hyperlink: `=HYPERLINK(A1,B1)` (Show content of B1 but it is a link that contains A1 hyperlink)
- Transformation of log (10-based), log (e-based), power, exponential, square-root : `=LOG10(A1)`, `=LN(A1)`,`=10^A1`, `=EXP(A1)`,  `=SQRT(A1)`
- rounding transformation : `=ROUND(A1, 2)`,  `=ROUND(A1, -2)`,  `=CEILING(A1,100)`,  `=FLOOR(A1,100)`
- random numbers: `=RAND()`, `=RANDBETWEEN(-10,10)`
- produce RANDOM numbers for given distribution: `=NORMINV(RAND(),0,1)`, `=LOGINV(RAND(),0,1)`, `=TINV(RAND(),5)`, `=FISHERINV(RAND())`, `=FINV(RAND(),2,2)`, `=BETAINV(RAND(),1,1,-10,10)`
- Logical NOT, AND, OR :  `=NOT(A1)`, `=AND(A1,A2)` , `=OR(A1,A2)`
- Single condition (if and else): `= IF(A3>3, "if_true_then_this","if_false_then_this")`
- Multiple condition (multiple if, no else, `#N/A` if no condition met): `= IFS(A1, "if_A1_then_this",A2,"if_A2_then_this")`
- Switch transformation (transform categorical variables into numerical, `#N/A` if no match found): `=SWITCH(A1,"JAN",1,"FEB",2)`
- Check Blank, count blank : `=ISBLANK(A1)`, `=COUNTBLANK(A1:A10)`
- Correct way to compute with blank (if blank, then `#N/A` ERROR, else OK, then count): `=IF(ISBLANK(A2),NA(),A2)`
- Check Error (all errors produced by functions + missing values): `=ISERROR(A1)`
- Check Error (all errors produced by functions , Blanks and missing are not errors): `=ISERR(A1)`
- Create N/A : `=NA()`
- See row value in integer : `=ROW(A5)`
- See column value in integer : `=COLUMN(A5)`
- Create Absolute Address from row cell and column cell :  `=ADDRESS(A1,A2)`
- Create Relative Address from row cell and column cell :  `=ADDRESS(A1,A2,4)`
- Create pointer to redirect cell address: `= INDIRECT(A1)`
    - go to A1, take the value of A1 as the address of target cell, go to that cell and fetch the value
- Date functions:
    - date to string format : `=TEXT("1/9/2012", "mmmmm-dd-yy")` will make it (J-09-12)
    - `NOW()`, `TODAY()` : current time and date 
    - `=DATEDIF(start_date, end_date, 'M')` : interval as month
- Text functions:
    - `=UPPER()`, `=PROPER()` ,`=LOWER()`, `=TRIM()`, `=CONCATENATE()`, `=LEN()`, `=LEFT()`, `=RIGHT()`
    - `=SEARCH(character, text, starting_index)` : Returns the index where the character is located in the text
    - `=SUBSTITUTE(text, old_substring, new_substring, [occurences])` : Replace a substring inside a text
- Conditional Aggregations:
    - `COUNTIF(A1:A, ">1000")` : count only if A1 to A-downward values are greather than 1000
    - `COUNTIFS(range1, "condition1", [range2, condition2] ....)` : `AND` operation on multiple `COUNTIF`
    - `SUMIF(condition_range, "condition", sum_range)` : check condition in condition_range and sum the adjacent cells in specified range when true
    - `SUMIFS(sum_range,condition1_range, "condition1",[condition2_range, "condition2"] )` : `AND` operation on multiple `SUMIF`
    - `AVERAGEIF`  and `AVERAGEIFS` : Same as `SUMIF` and `SUMIFS`
- Find value of a cell relative to specified cell A1: `=OFFSET(A3,3,1)`
    - Go to cell A3
    - go down 3 cells
    - go right 1 cell
    - take that cell's value
- Find value of a cell in a matrix / dataset: `=INDEX($A$1:$B$6,3,1)`
    - Go to cell $A$1, as the starting point of dataset / matrix
    - go to row no 3 of dataset 
    - go to column no 1 of dataset 
    - take that cell's value
- `=VLOOKUP(A1, $A$5:$B$10, 2, FALSE)` (Joining on column)
    - search value is in A1,
    - Find where that value matches in whole dataset `$A$5:$B$10`
    - Take the row of the match in dataset
    - return the value from second column
    - `FALSE` means strict match, `TRUE` means any close match to nearest number
- `=SORT($A$5:$B$10, 2, FALSE)`
    - Sort the whole table / dataset by second column
    - `FALSE` means descending order, `TRUE` means ascending order
- `=MATCH(10000, $A$5:$B$10, -1)`
    - Find 10000 in the whole table / dataset
    - The dataset is in descending order (-1) or ascending order (1)
    - returns the position of the value in the sorted order
- Other errors:
    - `#DIV/0!` : Dividing by zero
    - `#VALUE!` : Nonsense value in calculation
    - `#REF!` : Reference error
    - `#NAME?` : Forgetting quote in string
    - `#NUM!` : Numbers out of range
    - `#N/A` : Missing value
    - `#ERROR!` : Syntax in formula

Popularity 8/10 Helpfulness 4/10 Language whatever

Source: Grepper

Tags: spreadsheet whatever

Link to this answer
Share Copy Link

Contributed on Mar 13 2024

Innocent Iguana

0 Answers Avg Quality 2/10

spreadsheet functions

Contents

More Related Answers

spreadsheet functions

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.