Mastering basic Excel formulas is very important for beginners to become highly proficient in financial analysis. Microsoft Excel is considered the industry standard software in data analysis. Microsoft's spreadsheet program has also become one of the most preferred software for investment bankers and financial analysts for data processing, financial modeling, and presentation. Let's learn about basic functions in Excel with Giaiphapdonggoi.net!
1. SUM . function
The first Excel function that you should be familiar with is the one that performs the basic math of addition:
SUM( number1 , [number2],...)
In the syntax of all Excel functions, one argument enclosed in [square brackets] is optional, the other arguments are required. That is, your Sum formula must include at least 1 number, referencing a cell or a range of cells. Eg:
=SUM(B2:B6) - adds the values in cells B2 to B6.
=SUM(B2, B6) - adds the values in cells B2 and B6.
If needed, you can do other calculations in a formula, for example, add the values in cells B2 through B6, and then divide the total by 5:
=SUM(B2:B6)/5
To sum with conditions, use the SUMIF function: in the first argument, you enter the range of cells to be checked by the criteria (A2:A6), in the 2nd argument - the criteria itself (D2) and in the last argument - sum cells (B2:B6):
=SUMIF(A2:A6, D2, B2:B6)
In your Excel worksheet, the formulas might look like this:
SUM . function
Tips. The fastest way to sum a column or row of numbers is to select a cell next to the numbers you want to sum (the cell directly below the last value in the column or to the right of the last number in the row) and click the AutoSum button on Home tab, in group format. Excel will automatically insert the SUM formula for you.
2. AVERAGE . function
The AVERAGE function in Excel does exactly what its name suggests, i.e. find the mean or average of numbers. Its syntax is similar to SUM:
AVERAGE(number1, [number2],...)
Taking a closer look at the formula from the previous section ( =SUM(B2:B6)/5), what does it actually do? Sum the values in cells B2 through B6, then divide the result by 5. And what do you call add a group of numbers and then divide the sum by the count of those numbers? Yep, an average!
The Excel AVERAGE function performs these calculations. So instead of dividing the sum by the number, you can simply put this formula in a cell:
=AVERAGE(B2:B6)
To average cells based on a condition, use the following AVERAGEIF formula, where A2:A6 is the criteria range, D3 is his criteria, and B2:B6 is the averaged cells:
=AVERAGEIF(A2:A6, D3, B2:B6)
AVERAGE function
Let's refer to more cheap pp plastic belt products
3. MAX & MIN . function
The MAX and MIN formulas in Excel take the maximum and minimum values in a set of numbers, respectively. For our sample data set, the formulas will be as simple as this:
=MAX(B2:B6)
=MIN(B2:B6)
MAX & MIN . function
4. COUNT & COUNTA function
If you want to know how many cells in a certain range contain numeric values (numbers or dates), don't waste time counting them by hand. The COUNT function in Excel will give you the count in a snap:
COUNT(value1, [value2],...)
Although the COUNT function only processes cells that contain numbers, the COUNTA function counts all non-empty cells, whether they contain numbers, dates, times, text, logical values TRUE and FALSE, errors, or empty text strings. ("" ):
COUNTA(value1, [value2],...)
For example, to find out how many cells in column B contain numbers, use the following formula:
=COUNT(B:B)
To count all non-blank cells in column B, go with the following:
=COUNTA(B:B)
In both formulas, you use so-called "whole column references" (B:B) that refer to all cells in column B.
The following screenshot shows the difference: while COUNT only handles numbers, COUNTA outputs the total number of non-blank cells in column B, including the text value in the column header.
COUNT & COUNTA function
5. IF . function
Judging by the number of IF-related comments on our blog, this is the most popular function in Excel. In a word, you use the IF formula to tell Excel to check a certain condition and return a value, or perform a calculation if the condition is met and another value or calculation if Conditions not met:
IF(logic_test, [value_if_true], [value_if_false])
For example, the following IF statement checks if the order is fulfilled (i.e. has a value in column C). To check if a cell is not empty, you use the "not equal" operator (<>) in combination with an empty string (""). As a result, if cell C2 is not empty, the formula returns "Yes", otherwise "No":
=IF(C2<>"", "Yes", "No")
IF . function
6. TRIM . function
If your exact exact Excel formulas just return a bunch of errors, one of the first things to check is for extra spaces in the selected cells.
