Top 10 Excel Function and Formulas for Data Analysis

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.

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.

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”.

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.

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.

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.

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.
�

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.
 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.
 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.
Pingback: Top 10 Excel Function and Formulas for Data Ana...