MS Excel 30+ formulas easy explain.....

 MS Excel 30+  formulas easy explain.....

1. SUM: Adds up a range of cells.

Example: =SUM(A1:A5) adds up the values in cells A1 to A5.

2. AVERAGE: Calculates the average of a range of cells.

Example: =AVERAGE(A1:A5) calculates the average of the values in cells A1 to A5.

3. MAX: Returns the highest value in a range of cells.

Example: =MAX(A1:A5) returns the highest value among the values in cells A1 to A5.

4. MIN: Returns the lowest value in a range of cells.

Example: =MIN(A1:A5) returns the lowest value among the values in cells A1 to A5.

5. COUNT: Counts the number of cells that contain numbers in a range.

Example: =COUNT(A1:A5) counts the number of cells that contain numbers in cells A1 to A5.

6. COUNTA: Counts the number of non-empty cells in a range.

Example: =COUNTA(A1:A5) counts the number of non-empty cells in cells A1 to A5.

7. IF: Performs a logical test and returns different values based on the result.

Example: =IF(A1>10, "High", "Low") returns "High" if the value in cell A1 is greater than 10, otherwise "Low".

8. CONCATENATE: Joins two or more text strings together.

Example: =CONCATENATE("Hello", "World") returns "HelloWorld".

9. LEFT: Returns a specified number of characters from the start of a text string.

Example: =LEFT(A1, 3) returns the first 3 characters from the value in cell A1.

10. RIGHT: Returns a specified number of characters from the end of a text string.

Example: =RIGHT(A1, 3) returns the last 3 characters from the value in cell A1.

11. MID: Returns a specified number of characters from the middle of a text string.

Example: =MID(A1, 2, 3) returns 3 characters starting from the 2nd character of the value in cell A1.

12. LEN: Returns the length of a text string.

Example: =LEN(A1) returns the number of characters in the value of cell A1.

13. TRIM: Removes excess spaces from a text string.

Example: =TRIM(A1) removes excess spaces from the value in cell A1.

14. SUBSTITUTE: Replaces a specific portion of a text string with a different text.

Example: =SUBSTITUTE(A1, "apple", "orange") replaces "apple" with "orange" in the value of cell A1.

15. VLOOKUP: Searches for a value in the first column of a table and returns a corresponding value from another column.

Example: =VLOOKUP(A1, B1:C5, 2, FALSE) searches for the value in cell A1 in the range B1:C5, and returns the corresponding value from the second column.

16. HLOOKUP: Searches for a value in the first row of a table and returns a corresponding value from another row.

Example: =HLOOKUP(A1, B1:F5, 3, FALSE) searches for the value in cell A1 in the range B1:F5, and returns the corresponding value from the third row.

17. INDEX: Returns the value of a cell in a specified row and column.

Example: =INDEX(A1:F5, 3, 4) returns the value in the third row and fourth column of the range A1:F5.

18. MATCH: Searches for a specified value in a range and returns its relative position.

Example: =MATCH(A1, B1:B5, 0) searches for the value in cell A1 in the range B1:B5, and returns its relative position.

19. DATE: Returns the current date.

Example: =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())) returns the current date.

20. TIME: Returns the current time.

Example: =TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) returns the current time.

21. DAY: Extracts the day value from a date.

Example: =DAY(A1) extracts the day value from the date in cell A1.

22. MONTH: Extracts the month value from a date.

Example: =MONTH(A1) extracts the month value from the date in cell A1.

23. YEAR: Extracts the year value from a date.

Example: =YEAR(A1) extracts the year value from the date in cell A1.

24. TODAY: Returns the current date.

Example: =TODAY() returns the current date.

25. NOW: Returns the current date and time.

Example: =NOW() returns the current date and time.

26. ROUND: Rounds a number to a specified number of digits.

Example: =ROUND(A1, 2) rounds the value in cell A1 to 2 decimal places.

27. ROUNDUP: Rounds a number up to a specified number of digits.

Example: =ROUNDUP(A1, 2) rounds the value in cell A1 up to 2 decimal places.

28. ROUNDDOWN: Rounds a number down to a specified number of digits.

Example: =ROUNDDOWN(A1, 2) rounds the value in cell A1 down to 2 decimal places.

29. COUNTIF: Counts the number of cells that meet a specific condition.

Example: =COUNTIF(A1:A5, ">10") counts the number of cells in the range A1:A5 that are greater than 10.

30. SUMIF: Adds up the values in a range that meet a specific condition.

Example: =SUMIF(A1:A5, ">10") adds up the values in the range A1:A5 that are greater than 10.

31. AVERAGEIF: Calculates the average of the values in a range that meet a specific condition.

Example: =AVERAGEIF(A1:A5, ">10") calculates the average of the values in the range A1:A5 that are greater than 10.

32. IFERROR: Returns a specified value if a formula generates an error.

Example: =IFERROR(A1/B1, "Error") returns "Error" if the division of cell A1 by B1 generates an error.

33. AND: Performs a logical test and returns TRUE if all conditions are met.

Example: =AND(A1>10, A2<20) returns TRUE if both the value in cell A1 is greater than 10 and the value in cell A2 is less than 20.