Some Useful excel formulas

Excel has a large collection of formulas that can be used to perform various types of calculations and operations on data. Here are some of the most commonly used Excel formulas with a brief explanation:

SUM: Adds up a range of cells. The syntax is =SUM(range of cells).

• SUM: =SUM(B2:B7) – Adds up the values in cells B2 to B7 =SUM(B2, B4, B6) – Adds up the values in cells B2, B4, and B6

AVERAGE: Calculates the average of a range of cells. The syntax is =AVERAGE(range of cells).

• AVERAGE: =AVERAGE(B2:B7) – Calculates the average of the values in cells B2 to B7 =AVERAGE(B2, B4, B6) – Calculates the average of the values in cells B2, B4, and B6

COUNT: Counts the number of cells in a range that contain numbers. The syntax is =COUNT(range of cells).

• COUNT: =COUNT(B2:B7) – Counts the number of cells in the range B2 to B7 that contain numbers =COUNT(B2, B4, B6) – Counts the number of cells B2, B4, and B6 that contain numbers

MIN: Returns the smallest value in a range of cells. The syntax is =MIN(range of cells).

• MIN: =MIN(B2:B7) – Returns the smallest value in the range B2 to B7 =MIN(B2, B4, B6) – Returns the smallest value in cells B2, B4, and B6

MAX: Returns the largest value in a range of cells. The syntax is =MAX(range of cells).

• MAX: =MAX(B2:B7) – Returns the largest value in the range B2 to B7 =MAX(B2, B4, B6) – Returns the largest value in cells B2, B4, and B6

IF: Tests a condition and returns one value if the condition is true and another value if it is false. The syntax is =IF(condition, value if true, value if false).

• IF: =IF(B2>50, “Greater Than 50”, “Less Than or Equal to 50”) – Tests if the value in cell B2 is greater than 50. If it is, it returns “Greater Than 50”; otherwise, it returns “Less Than or Equal to 50”.

VLOOKUP: Searches for a value in the first column of a range of cells and returns a value in the same row from a specified column. The syntax is =VLOOKUP(value to search for, range of cells, column number to return, [approximate match]). 12 Best Microsoft Office Alternatives

• VLOOKUP: =VLOOKUP(A2, B2:C7, 2, TRUE) – Searches for the value in cell A2 in the first column of the range B2:C7 and returns the value in the same row from the second column (column C)

INDEX/MATCH: Searches for a value in a range of cells and returns a value in the same row or column. The syntax is =INDEX(array, MATCH(value to search for, range of cells, [match type])

• INDEX/MATCH: =INDEX(B2:C7, MATCH(A2, B2:B7, 0)) – Searches for the value in cell A2 in the range B2:B7 and returns the value in the same row from the second column (column C) of the range B2:C7 Career and education in UK

LEFT/RIGHT/MID: Extracts characters from the left, right, or middle of a text string. The syntax for LEFT is =LEFT(text, number of characters), the syntax for RIGHT is =RIGHT(text, number of characters), and the syntax for MID is =MID(text, start position, number of characters).

• LEFT/RIGHT/MID: =LEFT(A2, 3) – Extracts the first 3 characters from the text in cell A2 =RIGHT(A2, 4) – Extracts the last 4 characters from the text in cell A2 =MID(A2, 2, 5) – Extracts 5 characters from the text in cell A2 starting from the 2nd character

CONCATENATE: Combines two or more text strings into one string. The syntax is =CONCATENATE(text1, text2, …).

• ONCATENATE: =CONCATENATE(A2, ” “, B2) – Combines the text in cell A2 and the text ” ” (a space) and the text in cell B2 into one string.

These are just a few examples of the many formulas that Excel has to offer. It’s recommended to practice and experiment with different formulas to gain a better understanding of their capabilities.