Source: Safalta.com
Here, we'll examine the top 25 Excel formulae that anybody using the programme for work should be familiar with. The following are the subjects that will be covered in this article: Download these FREE Ebooks:1. Introduction to Digital Marketing
2. Website Planning and Creation
What is a formula in Excel?
A formula in Microsoft Excel is an expression that modifies values in a set of cells. Even if the result is incorrect, these formulae nonetheless yield a result. You may execute calculations like addition, subtraction, multiplication, and division using Excel formulae. Excel also allows you to work with date and time information, discover averages and percentages for a range of cells, and perform a lot more things.Excel Functions and Formulas
Depending on the type of operation you wish to carry out on the dataset, there are a variety of Excel formulae and functions available. We'll examine mathematical operation formulas and functions, character-text formulae, data and time formulas, sumif-countif formulas, and a few lookup formulas.1. SUM
The SUM() function, as its name implies, returns the sum of the values in the chosen 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(C2:C4)" to determine the total amount of sales for each unit. The sum of these three numbers is 300, 385, and 480. The outcome is kept in C5.
2. LEN
The method LEN() returns a string's overall character count.
The total number of characters, including spaces and special characters, will thus be counted.
An illustration of the Len function is shown below.
Let's move on to the following Excel feature in our article's list.
3.
CONCATENATE
This function merges or joins several text strings into one text string.
The many approaches of carrying out this duty are listed below.
In this instance, we used the expression =CONCATENATE (A25, " ", B25)
These two approaches were used to develop Excel's concatenation feature.
4.SUBTOTAL
Let's go and comprehend the subtotal function now. The subtotal in a database is returned by the SUBTOTAL() function. You may choose between average, count, total, minimum, maximum, and other options based on your needs. Let's look at two such instances.
\In the above example, cells A2 through A4 were used to calculate the subtotal. As you can see, the function used is "=SUBTOTAL(1, A2:A4), where "1" denotes average in the subtotal list. As a result, the aforementioned method will return A2's average as A4 and A2's response as 11, which is kept in C5.
The same is true for "=SUBTOTAL(4, A2:A4)," which chooses the cell with the largest value from A2 to A4, which is 12. The maximum result is obtained when "4" is included in the function.
5. POWER
The output of a number raised to a specific power is returned by the function "Power()". Let's look at the following examples:
As you can see above, we must type "= POWER (A2,3)" in order to find the power of 10 stored in A2 raised to 3. This is how Excel's power function operates.
6. TODAY()
The current system date is provided via Excel's TODAY() function.
The function DAY() is used to return the day of the month. It will be a number between 1 to 31. 1 is the first day of the month, 31 is the last day of the month.
The month is returned by the MONTH() method as a number between 1 and 12, with 1 being January and 12 denoting December.
The YEAR() function, as the name suggests, returns the year from a date value.
7. TIME()
Hours, minutes, and seconds are converted into an Excel serial number with a time format using the TIME() function.
8. AVERAGE
The goal of the AVERAGE() function is to determine the average value over the chosen range of cell values. As can be seen from the example below, all you need to do is put "AVERAGE(C2, C3, C4)" to determine the average of all sales.
The average is computed automatically, and you may store the outcome wherever you choose.
9. COUNT
The COUNT() method counts every cell in a range of cells that contain a number. It excludes the cells that are empty and those that contain information in a format other than numeric.
As can be seen above, we are counting from C1 to C4, which is the optimal number of cells. However, because the COUNT function only considers cells with numerical values, the correct response is 3, as the cell holding "Total Sales" has been left out in this instance. Use the function "COUNTA()" if you need to count all the cells that contain text, numeric values, or any other type of data. However, blank cells are not counted by COUNTA(). COUNTBLANK() is used to count the number of blank cells that are present in a range of cells.
10, CEILING
The ceiling function comes next. A number is rounded up to the nearest multiple of importance using the CEILING() function.
For 35.316, 40 is the biggest multiple of 5 that is close by.
