Best formulas In Excel
1. **Basic Arithmetic Formulas:**
- Addition: `=A1 + A2`
- Subtraction: `=A1 - A2`
- Multiplication: `=A1 * A2`
- Division: `=A1 / A2`
2. **SUM Function:** Adds up all the numbers in a range.
- Example: `=SUM(A1:A5)` adds the values in cells A1 to A5.
3. **AVERAGE Function:** Calculates the average of numbers in a range.
- Example: `=AVERAGE(A1:A5)` calculates the average of values in cells A1 to A5.
4. **MAX and MIN Functions:** Find the maximum and minimum values in a range.
- Example: `=MAX(A1:A5)` returns the largest value in cells A1 to A5.
- Example: `=MIN(A1:A5)` returns the smallest value in cells A1 to A5.
5. **COUNT Function:** Counts the number of cells in a range that contain numbers.
- Example: `=COUNT(A1:A5)` counts how many cells in A1 to A5 contain numbers.
6. **IF Function:** Allows you to perform conditional calculations.
- Example: `=IF(A1 > 10, "Yes", "No")` returns "Yes" if A1 is greater than 10, otherwise "No".
7. **VLOOKUP Function:** Searches for a value in a table and returns a corresponding value from another column.
- Example: `=VLOOKUP(A1, B1:C5, 2, FALSE)` searches for the value in A1 in the first column of the table B1:C5 and returns the corresponding value from the second column.
8. **HLOOKUP Function:** Similar to VLOOKUP but searches horizontally.
9. **CONCATENATE Function (& operator):** Combines text from multiple cells into one.
- Example: `=A1 & " " & B1` combines the text in cells A1 and B1 with a space in between.
10. **TEXT Function:** Converts a value to text with a specified format.
- Example: `=TEXT(A1, "yyyy-mm-dd")` converts a date in A1 to the format "yyyy-mm-dd".
11. **COUNTIF Function:** Counts the number of cells that meet a specific condition.
- Example: `=COUNTIF(A1:A5, ">10")` counts how many values in A1 to A5 are greater than 10.
Math and Trigonometry:
- SUM
- AVERAGE
- MAX
- MIN
- SUMIF
- AVERAGEIF
- COUNT
- COUNTA
- COUNTIF
- COUNTIFS
- ROUND
- CEILING
- FLOOR
- ABS
- INT
- SQRT
- POWER
- EXP
- LN
- LOG
- RAND
- RANDBETWEEN
- PI
- MOD
- TRUNC
- SIN
- COS
- TAN
- ASIN
- ACOS
- ATAN
Text Functions: 32. CONCATENATE (& operator)
- TEXT
- LEFT
- RIGHT
- MID
- LEN
- FIND
- SEARCH
- SUBSTITUTE
- REPLACE
- UPPER
- LOWER
- PROPER
- TRIM
- REPT
- CLEAN
- EXACT
Logical Functions: 49. IF
- AND
- OR
- NOT
- IFERROR
Date and Time Functions: 54. TODAY
- NOW
- DATE
- TIME
- YEAR
- MONTH
- DAY
- HOUR
- MINUTE
- SECOND
- WEEKDAY
- WORKDAY
- EDATE
- EOMONTH
- NETWORKDAYS
Lookup and Reference Functions: 69. VLOOKUP
- HLOOKUP
- INDEX
- MATCH
- OFFSET
- CHOOSE
- INDIRECT
- ADDRESS
Financial Functions: 77. PMT
- PV
- FV
- RATE
- NPV
- IRR
Statistical Functions: 83. STDEV
- STDEVP
- VAR
- VARP
- CORREL
- COVAR
- AVEDEV
- MEDIAN
- MODE
- PERCENTILE
- QUARTILE
- RANK
Database Functions: 95. DSUM
- DAVERAGE
- DCOUNT
- DMAX
- DMIN
Array Functions: 100. ARRAYFORMULA (Google Sheets)
- TRANSPOSE
- MMULT
- SUMPRODUCT
Engineering Functions: 104. BIN2DEC
- DEC2BIN
- HEX2DEC
- DEC2HEX
- OCT2DEC
- DEC2OCT
- BITAND
- BITOR
- BITXOR
- BITLSHIFT
- BITRSHIFT
