Home > Excel formulas and functions

1. SUM Formula:

• Description: The SUM Excel formulas and functions calculate the total of a range of numbers.
• Example: =SUM(A1:A5) adds the values in cells A1 to A5.

2. AVERAGE Formula:

• Description: The AVERAGE formula computes the mean value of a set of numbers.
• Example: =AVERAGE(B1:B4) finds the average of the numbers in cells B1 to B4.

3. MAX and MIN Formulas:

• Description: MAX returns the largest value, while MIN returns the smallest value within a range.
• Examples: =MAX(C1:C7) finds the highest value in cells C1 to C7, and =MIN(D1:D6) identifies the lowest value in cells D1 to D6.

4. COUNT Formula:

• Description: The COUNT formula tallies the number of cells in a range that contain numbers.
• Example: =COUNT(E1:E10) counts how many cells in E1 to E10 contain numeric values.

5. IF Formula:

• Description: The IF formula allows you to make decisions based on specified conditions.
• Example: =IF(F1>10,"Above 10", "Below or Equal to 10") checks if the value in cell F1 is greater than 10 and provides a corresponding result.

6. CONCATENATE Formula:

• Description: CONCATENATE combines text or values from different cells into a single cell.
• Example: =CONCATENATE(G1, "", G2) combines the content of cells G1 and G2 with a space in between.

7. VLOOKUP Formula:

• Description: VLOOKUP searches for a value in a table and returns a corresponding value from the same row.
• Example: =VLOOKUP(H1, A1:B10, 2,FALSE) looks for the value in H1 within the range A1 to B10 and returns the value from the second column.

8. HLOOKUP Formula:

• Description: HLOOKUP functions similarly to VLOOKUP but searches horizontally in a table.
• Example: =HLOOKUP(I1, A1:F5, 4, FALSE) searches for I1 in the range A1 to F5 and returns a value from the fourth row.

9. SUMIF Formula:

• Description: SUMIF adds up values based on a specified condition.
• Example: =SUMIF(J1:J10,"Apples", K1:K10) sums the values in K1 to K10 where the      corresponding cell in J1 to J10 is "Apples."

10. AVERAGEIF Formula:

• Description: AVERAGEIF calculates the average of values that meet a particular condition.
• Example: =AVERAGEIF(L1:L8,">50", M1:M8) finds the average of values in M1 to M8      where the corresponding cell in L1 to L8 is greater than 50.

11. COUNTIF Formula:

• Description: COUNTIF counts the number of cells in a range that meet specific criteria.
• Example: =COUNTIF(N1:N10,"High") counts how many cells in N1 to N10 contain the text "High."

12. IFERROR Formula:

• Description: IFERROR allows you to handle errors and return a specific value when an error occurs.
• Example: =IFERROR(O1/P1,"Error") divides the value in cell O1 by P1 and returns      "Error" if an error occurs.

13. DATE Formula:

• Description: DATE creates a date by specifying the year, month, and day.
• Example: =DATE(2023, 11, 1)generates the date November 1, 2023.

14. TODAY and NOW Formulas:

• Description: TODAY returns the current date, while NOW provides the current date and time.
• Examples: =TODAY() returns today's date, and =NOW() returns the current date and time.

15. PMT Formula:

• Description: PMT calculates the periodic payment for a loan, investment, or annuity.
• Example: =PMT(0.05/12, 12*5,-5000) calculates the monthly payment for a $5,000 loan with a 5% annual interest rate over 5 years.

16. SUMPRODUCT Formula:

• Description: SUMPRODUCT multiplies corresponding values in multiple arrays and sums the results.
• Example: =SUMPRODUCT(P1:P5,Q1:Q5) multiplies the values in cells P1 to P5 with those in Q1 to Q5 and provides the sum of the products.

17. RANK Formula:

• Description: RANK assigns a rank to a value in a list, based on its position relative to other values.
• Example: =RANK(R1, R1:R10) calculates the rank of the value in cell R1 within the range R1 to R10.

18. TEXT Formula:

• Description: TEXT allows you to format a numeric value as text with a specified format.
• Example: =TEXT(S1, "mm/dd/yyyy") formats the date in cell S1 as "mm/dd/yyyy."

19. INDEX and MATCH Formulas:

• Description: INDEX returns a value from a specified location in a table, while MATCH searches for a value in a range and returns its relative position.
• Example: =INDEX(A1:A10, MATCH("Apple", B1:B10, 0)) retrieves a value from column A corresponding to "Apple" in column B.

20. CONCATENATE and IF Formulas Combined:

• Description: Combine CONCATENATE and IF to create custom text based on conditions.
• Example: =CONCATENATE("Status:", IF(T1>0, "Positive", "Negative"))generates text based on the value in cell T1.