Microsoft Excel is a spreadsheet program that allows users to organize and analyze data. One of the key features of Excel is its ability to use formulas, which are equations that perform calculations on the data in the spreadsheet. Formulas are an essential part of working with Excel, as they allow you to quickly and easily perform complex calculations on your data.
We’ve listed the most crucial Excel formulas that any expert financial analyst needs to be familiar with based on our countless years of experience.
1. AVERAGE
The objective of the AVERAGE() function is to determine the average value across the selected range of cell values. As can be seen from the example below, all you need to do is put “AVERAGE(E2:E10)” to determine the average of all Ages which is 27.77778 .
2. SUM
The SUM() function, as its name implies, returns the sum of the values in the selected range of cells. It performs the addition mathematical operation. Here is an illustration of it:
As you can see above, all we had to do was enter the function “=SUM(E2:E10)” to determine the total ages for each unit. The sum of the Ages is 250. The outcome is displayed in E11
3. COUNT
The function COUNT() gives the total number of cells in a range containing a number. It excludes the cells that are blank and those that contain data in any other format than numeric.
As shown above, we’re counting from E1 to E10 which are ten cells. However, because the COUNT function only considers cells with numerical values, the answer displayed at E11 is 9 because the cell holding “AGE” is excluded here.
You must use the function ‘COUNTA()’ to count all the cells with numerical values, text, or any other data type. COUNTA(), on the other hand, does not count any blank cells.
COUNTBLANK() is used to count the number of blank cells in a range of cells.
4. NOW()
In Excel, the NOW() function returns the current system date and time.
In the Image above the Formula is displayed in A2 and the outcome in B2.
The NOW() function’s output will solely depends on your system’s date and time.
5. TODAY()
The TODAY() formula is a built-in function in Microsoft Excel that returns the current date. It does not take any arguments, so the syntax for the formula is simply:
syntax =TODAY()
When you enter this formula into a cell in an Excel spreadsheet, it will automatically return the current date, as determined by the computer’s internal clock. This can be useful for a variety of purposes, such as tracking the passage of time, calculating deadlines, or creating dynamic date-based formulas.
6. DATEDIF
The DATEDIF date function computes the difference between two dates. The differences may now be measured in years, months, or days. It accepts two dates as input and one keyword to describe the type of difference needed in the output.
The DATEDIF() function returns the difference in years, months, or days between the two dates.
Syntax
DATEDIF(start_date, end_date, unit)
Start_date – the first date of the period to be calculated.
End_date – the period’s end date.
Unit: The time unit to be used for calculating the difference between two dates.
These are Units available.
D (Days): D is used in a formula to calculate the number of days between two dates.
M (Months): used in a formula to calculate the number of months between two dates.
Y (Years): Y is used in a formula to calculate the number of full years between two dates.
In the image below we used “Y” as a unit in a formula to find the number of YEARS between the start date and and the end date.
In the image below we used “M” as a unit in a formula to find the number of MONTHS between the start date and and the end date.
In the image below we used “D” as a unit in a formula to find the number of DAYS between the start date and and the end date.
7. IF Formula
The IF Function determines whether or not a condition is met. If TRUE, return a particular value ; if FALSE, return another value.
In the example given below, we wish to determine whether the values in the C column are larger than 50. If it is more than 50, the method returns “PASS” else it returns “FAIL”. This means that all grades less than 50 would have remarks as FAIL.
8. TRIM
Except for single spaces between words, the TRIM function removes all spaces from text. TRIM is frequently used on text imported from another program with inconsistent spacing.
Follow this simple format: = TRIM(test)
TRIM can be applied to a text string enclosed by double quotes or a single cell reference.
9. CONCAT
This function combines the values of many cells into a single value. This is handy for piecing together various portions of text, such as combining a first and last name to get a whole name.
To insert a space, dash, or comma just enter the text within double quotes as an argument.
syntax : CONCAT(B2,” “,C2) will copy the value from cells A2 and B2 and also a space between them.
10. CONCATENATE
In Microsoft Excel, you can use the CONCATENATE function to join together two or more strings. The syntax for the CONCATENATE function is:
CONCATENATE(string1, string2, ...)
Here, string1, string2, etc. are the strings that you want to join together. You can include as many strings as you want, separated by commas.
For example, if you have two strings in cells A1 and B1, and you want to concatenate them in cell C1, you could use the following formula:
=CONCATENATE(A1," ", B1)
This would concatenate the strings in cells A1 and B1, and display the result in cell C1.
In conclusion, Microsoft Excel is a powerful tool that allows users to easily perform calculations and manipulate data. Its wide range of functions and formulas makes it a versatile program that can be used for a wide variety of tasks. Whether you are a business professional, a student, or a hobbyist, Excel can help you organize, analyze, and present your data in a clear and efficient manner. With a little practice and understanding of its basic principles, you can unlock the full potential of Excel and use it to your advantage.
