Saturday, September 9, 2023

Best formulas In Excel

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:

  1. SUM
  2. AVERAGE
  3. MAX
  4. MIN
  5. SUMIF
  6. AVERAGEIF
  7. COUNT
  8. COUNTA
  9. COUNTIF
  10. COUNTIFS
  11. ROUND
  12. CEILING
  13. FLOOR
  14. ABS
  15. INT
  16. SQRT
  17. POWER
  18. EXP
  19. LN
  20. LOG
  21. RAND
  22. RANDBETWEEN
  23. PI
  24. MOD
  25. TRUNC
  26. SIN
  27. COS
  28. TAN
  29. ASIN
  30. ACOS
  31. ATAN

Text Functions: 32. CONCATENATE (& operator)

  1. TEXT
  2. LEFT
  3. RIGHT
  4. MID
  5. LEN
  6. FIND
  7. SEARCH
  8. SUBSTITUTE
  9. REPLACE
  10. UPPER
  11. LOWER
  12. PROPER
  13. TRIM
  14. REPT
  15. CLEAN
  16. EXACT

Logical Functions: 49. IF

  1. AND
  2. OR
  3. NOT
  4. IFERROR

Date and Time Functions: 54. TODAY

  1. NOW
  2. DATE
  3. TIME
  4. YEAR
  5. MONTH
  6. DAY
  7. HOUR
  8. MINUTE
  9. SECOND
  10. WEEKDAY
  11. WORKDAY
  12. EDATE
  13. EOMONTH
  14. NETWORKDAYS

Lookup and Reference Functions: 69. VLOOKUP

  1. HLOOKUP
  2. INDEX
  3. MATCH
  4. OFFSET
  5. CHOOSE
  6. INDIRECT
  7. ADDRESS

Financial Functions: 77. PMT

  1. PV
  2. FV
  3. RATE
  4. NPV
  5. IRR

Statistical Functions: 83. STDEV

  1. STDEVP
  2. VAR
  3. VARP
  4. CORREL
  5. COVAR
  6. AVEDEV
  7. MEDIAN
  8. MODE
  9. PERCENTILE
  10. QUARTILE
  11. RANK

Database Functions: 95. DSUM

  1. DAVERAGE
  2. DCOUNT
  3. DMAX
  4. DMIN

Array Functions: 100. ARRAYFORMULA (Google Sheets)

  1. TRANSPOSE
  2. MMULT
  3. SUMPRODUCT

Engineering Functions: 104. BIN2DEC

  1. DEC2BIN
  2. HEX2DEC
  3. DEC2HEX
  4. OCT2DEC
  5. DEC2OCT
  6. BITAND
  7. BITOR
  8. BITXOR
  9. BITLSHIFT
  10. BITRSHIFT
on
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)