Basic Excel Formulas Guide
Mastering the basic Excel formulas is critical for beginners to become highly proficient in financial analysis. Microsoft Excel is considered the industry standard piece of software for data analysis. Microsoft’s spreadsheet program also happens to be one of the most preferred software by investment bankers and financial analyst in data processing, financial modeling, and presentation. This guide will provide an overview.
1) SUM
In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =B1+B2+B3, which finds the sum of the range of values from cell B1 to Cell B3.
Functions are predefined formulas in Excel. They eliminate laborious manual entry of formulas while giving them human-friendly names. For example: =SUM(B1:B3). The function sums all the values from B1 to B3.
Shortcut Key for SUM = Alt + = (please try)
Shortcut Keys Can save your time & effort.
2) AVERAGE
The AVERAGE function should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool.
=AVERAGE(number1, [number2], …)
Example:
=AVERAGE(B1: B10) – Shows a simple average
2,4,8,6,10
1st we have to add them
=2+4+8+6+10
=30
2nd we have to divide by number, there are 5 number so we will divide by 5 if there is 4 number we will divide by 4 so on and so for.
if you are using AVERAGE Function you don't need to all these simply you can add function and you will get the results.
3) COUNT
The COUNT function counts all cells in a given range that contains only numeric values.
=COUNT(value1, [value2], …)
Example:
COUNT(A:A) – Counts all values that are numerical in A column. However, it doesn’t use the same formula to count rows.
COUNT(A1: C1) – Now it can count rows.
You can count rows & column but that's should be number count never count TEXT.
4) COUNTA
Like the COUNT function, COUNTA counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that relies on only numerics, it also counts dates, times, strings, logical values, errors, empty string, or text.
=COUNTA(value1, [value2], …)
Example:
COUNTA(A:A) – Counts all cells in column A regardless of type. However, like COUNT, you can’t use the same formula to count rows.
if the cell is blank it will not include that.
5) TRIM
The TRIM function makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data in your spreadsheet.
=TRIM (text)
Example:
TRIM (A4) – Removes empty spaces in the value in cell A4.
