Top 10 Excel Function and Formulas for Data Analysis

 

1. Mathematical Formulas:

SUM:

• Definition: Adds up a range of numbers.
• Example: =SUM(A1:A10)
• Explanation: This formula calculates the sum of values in cells A1 through A10.

AVERAGE:

• Definition: Calculates the mean (average) of a range of numbers.
• Example: =AVERAGE(B2:B20)
• Explanation: This formula computes the average of values in cells B2 through B20.

COUNT:

• Definition: Counts the number of cells that contain numeric values in a range.
• Example: =COUNT(C1:C100)
• Explanation: This formula counts the cells in the range C1 to C100 that contain numeric values.

MAX:

• Definition: Returns the maximum value in a range.
• Example: =MAX(D2:D15)
• Explanation: This formula finds the maximum value in the range D2 to D15.

MIN:

• Definition: Returns the minimum value in a range.
• Example: =MIN(E5:E20)
• Explanation: This formula finds the minimum value in the range E5 to E20.

PRODUCT:

• Definition: Multiplies numbers in a range.
• Example: =PRODUCT(F2:F10)
• Explanation: This formula multiplies values in the range F2 to F10.

ROUND:

• Definition: Rounds a number to a specified number of digits.
• Example: =ROUND(G3, 2)
• Explanation: This formula rounds the value in cell G3 to two decimal places.

SUMIFS:

• Definition: Adds up numbers based on multiple criteria.
• Example: =SUMIFS(A2:A100, B2:B100, “Category1”, C2:C100, “>100”)
• Explanation: This formula sums values in column A based on criteria in columns B and C.

2. Statistical Analysis Formulas:

STDEV:

• Definition: Calculates the standard deviation of a sample.
• Example: =STDEV(D2:D20)
• Explanation: This formula computes the standard deviation of values in cells D2 through D20.

VAR:

• Definition: Calculates the variance of a sample.
• Example: =VAR(E3:E15)
• Explanation: This formula calculates the variance of values in cells E3 through E15.

CORREL:

• Definition: Calculates the correlation coefficient between two ranges of values.
• Example: =CORREL(F2:F20, G2:G20)
• Explanation: This formula gives the correlation between values in columns F and G.

COUNTIF:

• Definition: Counts cells based on a single criterion.
• Example: =COUNTIF(H2:H50, “>50”)
• Explanation: This formula counts cells in the range H2 to H50 that are greater than 50.

COUNTIFS:

• Definition: Counts cells based on multiple criteria.
• Example: =COUNTIFS(A2:A100, “Category1”, B2:B100, “>100”)
• Explanation: This formula counts cells in column A based on criteria in columns B and C.

3. Logical Formulas:

IF:

• Definition: Performs conditional logic.
• Example: =IF(A1>10, “Greater”, “Less or Equal”)
• Explanation: This formula checks if the value in cell A1 is greater than 10 and returns “Greater” if true, otherwise “Less or Equal”.

AND:

• Definition: Returns TRUE if all arguments are true, and FALSE if any argument is false.
• Example: =AND(A2>10, B2<20)
• Explanation: This formula returns TRUE if both conditions in cells A2 and B2 are true.

OR:

• Definition: Returns TRUE if any argument is true.
• Example: =OR(C2=”Category1″, C2=”Category2″)
• Explanation: This formula returns TRUE if the value in cell C2 is either “Category1” or “Category2”.

NOT:

• Definition: Returns TRUE if the argument is false and vice versa.
• Example: =NOT(D2=”Incomplete”)
• Explanation: This formula returns TRUE if the value in cell D2 is not equal to “Incomplete”.

4. Text Functions:

CONCATENATE:

• Definition: Combines two or more strings into one.
• Example: =CONCATENATE(“First”, ” “, “Last”)
• Explanation: This formula concatenates the strings “First”, a space, and “Last” into a single text string.

LEFT:

• Definition: Extracts a specified number of characters from the beginning of a text string.
• Example: =LEFT(E2, 3)
• Explanation: This formula extracts the first three characters from the text in cell E2.

RIGHT:

• Definition: Extracts a specified number of characters from the end of a text string.
• Example: =RIGHT(F3, 4)
• Explanation: This formula extracts the last four characters from the text in cell F3.

MID:

• Definition: Extracts a specified number of characters from a text string, starting at a specified position.
• Example: =MID(G4, 2, 3)
• Explanation: This formula extracts three characters from the text in cell G4, starting at the second character.

LEN:

• Definition: Returns the number of characters in a text string.
• Example: =LEN(H5)
• Explanation: This formula returns the number of characters in the text string in cell H5.

PROPER:

• Definition: Converts text to proper case (capitalizes the first letter of each word).
• Example: =PROPER(I6)
• Explanation: This formula converts the text in cell I6 to proper case.

TRIM:

• Definition: Removes leading and trailing spaces from a text string.
• Example: =TRIM(J7)
• Explanation: This formula removes extra spaces from the text in cell J7.

SUBSTITUTE:

• Definition: Replaces occurrences of a specified substring with another substring.
• Example: =SUBSTITUTE(K8, “old”, “new”)
• Explanation: This formula replaces all occurrences of “old” with “new” in the text in cell K8.

5. Date and Time Functions:

TODAY:

• Definition: Returns the current date.
• Example: =TODAY()
• Explanation: This formula returns the current date.

NOW:

• Definition: Returns the current date and time.
• Example: =NOW()
• Explanation: This formula returns the current date and time.

�DATEDIF:

• Definition: Calculates the difference between two dates in years, months, or days.
• Example: =DATEDIF(L2, M2, “d”)
• Explanation: This formula calculates the number of days between the dates in cells L2 and M2.

EOMONTH:

• Definition: Returns the last day of the month, a specified number of months before or after a given date.
• Example: =EOMONTH(N3, 2)
• Explanation: This formula returns the last day of the month that is two months after the date in cell N3.

6. Lookup and Reference Formulas:

VLOOKUP:

• Definition: Searches for a value in the first column of a table and returns a value in the same row from another column.
• Example: =VLOOKUP(O2, P2:Q100, 2, FALSE)
• Explanation: This formula looks for the value in cell O2 in the first column of the range P2:Q100 and returns the corresponding value from the second column.

HLOOKUP:

• Definition: Searches for a value in the first row of a table and returns a value in the same column from another row.
• Example: =HLOOKUP(R2, S1:U10, 3, FALSE)
• Explanation: This formula looks for the value in cell R2 in the first row of the range S1:U10 and returns the corresponding value from the third row.

INDEX:

• Definition: Returns the value of a cell in a specified row and column of a range.
• Example: =INDEX(V2:Z10, 3, 4)
• Explanation: This formula returns the value in the cell at the intersection of the third row and fourth column in the range V2:Z10.

MATCH:

• Definition: Searches for a specified value in a range and returns the relative position of that item.
• Example: =MATCH(A2, A2:A100, 0)
• Explanation: This formula searches for the value in cell A2 within the range A2:A100 and returns its relative position.

INDIRECT:

• Definition: Returns the reference specified by a text string.
• Example: =INDIRECT(“Sheet2!A1”)
• Explanation: This formula indirectly refers to the cell A1 in Sheet2.

7. Array Formulas:

SUMPRODUCT:

• Definition: Multiplies corresponding components in the given arrays and returns the sum of those products.
• Example: =SUMPRODUCT(X2:X10, Y2:Y10)
• Explanation: This formula multiplies each pair of values in the ranges X2:X10 and Y2:Y10 and then sums up the products.

Array Formulas with Ctrl+Shift+Enter:

• Definition: Perform operations on arrays.
• Example: {=SUM(A1:A10*B1:B10)}
• Explanation: Array formulas are entered by pressing Ctrl+Shift+Enter. They can perform calculations on entire ranges of cells at once.

�

8. Information Functions:

ISNUMBER:

• Definition: Checks if a value is a number.
• Example: =ISNUMBER(A1)
• Explanation: This formula returns TRUE if the value in cell A1 is a number; otherwise, it returns FALSE.

ISTEXT:

• Definition: Checks if a value is text.
• Example: =ISTEXT(B2)
• Explanation: This formula returns TRUE if the value in cell B2 is text; otherwise, it returns FALSE.

ISBLANK:

• Definition: Checks if a cell is empty.
• Example: =ISBLANK(C3)
• Explanation: This formula returns TRUE if the cell in C3 is empty; otherwise, it returns FALSE.

IFERROR:

• Definition: Returns a value if a formula results in an error; otherwise, returns the result.
• Example: =IFERROR(D2/E2, “Error in division”)
• Explanation: This formula calculates D2/E2, and if an error occurs, it returns the specified error message.

9. Financial Functions:

PV:

• Definition: Calculates the present value of an investment.
• Example: =PV(0.05, 10, 1000)
• Explanation: This formula calculates the present value of a $1,000 investment over 10 years with a 5% interest rate.

FV:

• Definition: Calculates the future value of an investment.
• Example: =FV(0.03, 5, -500, 0, 1)
• Explanation: This formula calculates the future value of a $500 investment with a 3% interest rate over 5 years.

10. Database Functions:

DSUM:

• Definition: Adds the numbers in a column of a list or database that meet multiple criteria.
• Example: =DSUM(A1:C100, “Sales”, D1:E2)
• Explanation: This formula adds up the “Sales” column in the range A1:C100 that meets the criteria specified in the range D1:E2.

DGET:

• Definition: Extracts a single value from a list or database that matches the specified conditions.
• Example: =DGET(A1:C100, “Sales”, D1:E2)
• Explanation: This formula retrieves a single value from the “Sales” column in the range A1:C100 that meets the criteria specified in the range D1:E2.