There are many ways to calculate values in Excel. A formula is an equation designed by the user. These are useful to perform simple computations on data. A function is a predefined operation that exists within Excel. Excel has a variety of functions that cover basic mathematics, trigonometry, statistical, finance, logical, and engineering formulas.
To create a formula in Excel:
Excel functions perform calculations or analyses on data in a spreadsheet. These functions allow you to manipulate different data types, such as text, numbers, and dates. You can also use if-else statements to capture certain conditions, find and replace data, match data based on values, and much more. This sounds complicated, but Excel has function builders to help. All you need to do is find the right formula and identify the data that you want to work with.
To specify a function, click on the cell in which you want the value to be displayed. Then, write the = (equals sign) and the function name. You can also use the Function Builder to complete the formula.
SUM: The SUM() formula performs addition on selected cells. It works on cells containing numerical values and requires two or more cells. For example, =SUM(A2:A57) will return the sum of all of the values from cells A2 through A57.
MIN and MAX: The MIN() formula requires a range of cells, and it returns the minimum value. The MAX() formula is the opposite of MIN(). It will return the maximum value from the selected range of cells. For example, =MAX(A2:A57) will return the largest value from the cells A2 through A57.
AVERAGE: The AVERAGE() formula calculates the average of selected cells. You can provide a range of cells (C2:C5) or select individual cells (C2, C3, C5).
COUNT: The COUNT() formula counts the total number of selected cells. It will not count the blank cells and different data formats other than numeric.
COUNTIF: The COUNTIF() formula counts the total number of selected cells that meet certain criteria (e.g., all the cells with values greater than 4, or that contain the text "undergraduate"). For example, =COUNTIF(A2:A57, ">4") identifies the number of cells in the range of A2 to A57 that contain values greater than 4.
TRIM: TRIM is used to remove extra spaces from the start, middle, and end. It is commonly used to identify duplicate values in cells, and for some reason, extra space makes it unique.
REPLACE: REPLACE is used for replacing part of the string with a new string. =REPLACE(old_text, start_num, num_chars, new_text)
old_text is the original text or cell containing the text.
start_num is the index position that you want to start replacing the character.
num_chars refers to the number of characters you want to replace.
new_text indicates the new text that you want to replace with old text.
LEFT, RIGHT, and MID: The LEFT returns the number of characters from the start of the string or text. The MID formula requires starting position and length to extract the characters from the middle. The RIGHT will return the number of characters from the end. You just need to provide the number of characters. =RIGHT(A2,7) will return the last seven characters from cell A2.
UPPER, LOWER, and PROPER: The UPPER, LOWER, and PROPER are basic text operations. UPPER will convert all the letters in the text to uppercase. LOWER will convert the selected text lower lowercase. PROPER will convert the string to the proper case. For example, the first letter in each word will be capitalized, and the rest of them will be lowercase.
NOW and TODAY: NOW returns the current time and date, and TODAY returns only the current date. =NOW() will display the current time and date, and will update each time you reopen or refresh the spreadsheet.
