While preparing reports or analysing data, it becomes a task to do calculations manually or separately on a calculator. Microsoft Excel enables its users to do all the calculations in the software using Excel formulas while preparing reports or analysing the data. Excel is a widely used software application used in corporate offices and businesses to help employees ease their work. Here is a list of top MS Excel Formulas that are extremely useful. Learning and applying these formulas while working on Excel will help you upskill and work efficiently.
Useful MS Excel Formula
1. SUM
This function performs the mathematical operation of addition on the selected cell values. For example: To get the sum of items mentioned in column A, you have to simply type in the function ”=SUM(A2:A5)”. This will automatically add the content fill in cell A2 to A5, and the total will be displayed in the A6 cell.
2. AVERAGE
The average function is used to find the average of the selected range of cell values. For example: if you want to find the average of total sales mentioned in column A, just type in the function “=AVERAGE(A2, A3, A4, A5)”. This will automatically find the average values filled in the cells A2 to A5 and display the result in your desired location.
3. COUNT
The count function gives you the total count of cells in a range that hold numeric values. The cells which are blank and which do not hold data in numeric format are excluded from this function. Type in the function “COUNT(A1:A5)” to get the total count of cells.
To get the count of all the cells with numeric values, texts, and data in any other format, use the function COUNTA(). To get the count of blank cells, use the function “COUNTBLANK().
4. MODULUS
The Modulus function can be used when you want to display a remainder after dividing a number by the divisor. To get better clarity of the Modulus functions, here are some examples:
· Use function “=MOD(A2,3)” in the function (fx) tab if you want to divide the data in cell A2 by 3. For instance, if the number in the A2 cell is 10, you will get 1 as a remainder using the function, and the result will be stored in the desired cell.
· Similarly, if you use function =MOD(A3,4), you have divided the number in cell A3 by 4. The remainder will be stored in cell B3 or the cell of your choice.
5. POWER
Are you finding a superscript symbol in Excel? Well, you have to use the power function for the same. The power function Power () in Excel gives the result if the number is raised to a specific power like 103, 25, etc.
To use the power function in Excel, type =Power (box number, raised to number) like =Power(A2, 3). This means the number in box A2 will be raised to 3. If the number is 10, it will be raised to 3, and the result of 1000 will be displayed in cell B2.
6. CEILING
Do you want to run all decimals figures in the Excel sheet to round the number to its nearest multiple of significance? Use function =CEILING() for the same.
For instance, select the B2 cell or any other cell of your choice where you want the answer to display and then use function =CEILING(A2,5); the number in cell A2 will turn into the nearest multiple, i.e., suppose cell A2 holds the number 35.5, on applying ceiling function it will turn into 40.
If you use =CEILING(A2,1), the number in cell A2, i.e., 35.5, will turn into 36. Excel also has another similar function =CEILING.MATH rounds the number to its nearest integer or nearest multiple of significance.
7. FLOOR
This function is the opposite of CEILING. You use the function Floor to round a number down to its nearest significance. You use the function =FLOOR(box with a number that is rounded down, places to be rounded off), i.e., =Floor (A2,5).
For example: If the number is 35.32, it will return 35. Also, if the number is 39.7, it will still return 35. If the function is =FLOOR (A2,1), the number 39.7 will return to 39.
8. CONCATENATE
CONCATENATE is used to join several text strings into one string. To understand better, here is an example:
· If cell A2 has the word Hello, B2 has the word World. Using function =CONCATENATE(A2, B2) will get HelloWorld in the selected box. You need to use function =CONCENTENATE(A2, “ “, B2) to include space. This will result in Hello World.
· If box A2 has the word ‘How is’, whereas B2 has the word ‘my friend?’ You need to use function =CONCATENATE(A2&” “&B2) for proper spacing between words.
9. LEN
Want to count the number of characters in a string? You can use the function LEN().
For instance, if you have the word Computer in cell A2, it will return the result in box B2, i.e., 8, using function =LEN(A2). The function is super easy to use. If there are two words, it will count space as a new character.
10. REPLACE
REPLACE function replaces a part of the text string with a different text string.
The syntax to use REPLACE is quite long but quite handy. To syntax =REPLACE(old_text,start_num,num_chars,new_text). The start_num is the index position of the text that is to be replaced. Num_chars is the number of characters that need to be replaced.
To understand better, here is an example:
· A2 has A101, i.e., the old _text, B2 has the result B101, the replaced number, i.e., new_text. Thus, we are replacing A101 with B101. The function will be =REPLACE(A2,1,1, “B2”).
· To replace Adam with Saam, use function =REPLACE(A2, 1, 2, “Sa”).
11. UPPER, LOWER, AND PROPER
These three functions convert a text string to uppercase, lowercase, and proper case, respectively.
For instance, to covert Apple into APPLE, use =UPPER(A2)
To convert APPLE to apple, use =LOWER(A2)
To convert aPple to Apple, use =PROPER(A2)
12. TRIM
This function ensures that the data that you have entered doesn’t give faulty results due to extra spaces in the data. Having said that, it is important to note that the TRIM function will work on a single cell only and not on the whole data.
For example, to apply the TRIM function to the data in cell A3, use
= TRIM(A3)
13. MAX & MIN
The MAX and MIN functions are used to scout for the maximum and minimum values from a range of data. This is one of the most useful excel formulas.
For example :
MIN(B2:C10) will find the minimum values in the respective data range.
MAX(B2:C10) will find the maximum value in the respective data range.
14. IF
The IF function is used for sorting the respective data according to a certain logic. This function gives values based on a true or false condition.
For example :
=IF(logical_test, value_if_true, value_if_false)
Where: the logical test is the condition that needs to be tested.
Value if true is the value that will be returned if the condition is true.
Value if false is the value that will be returned if the test is false.
15. SUMIF
This function will denote the total sum of values that fall within the respective data range. The sum total will be the data within the cells that meet certain criteria.
For example
SUMIF(C3:C12 “>70,000”) will produce a sum total of values between C3 and C12 that are more than 70,000.
16. VLOOKUP
This function is used to combine two sets of data on two different spreadsheets. It is important to remember that, in order to use this function, at least one column of data should be the same in both spreadsheets.
For example :
VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
17. FV
FV function is a built-in financial function in excel. It is used to determine the return on a specific, periodic, or lump sum investment over a period of time with respect to a fixed rate of interest.
For example :
FV(rate, per, pmt, [PV], [type]) where rate refers to the internet rate, nper signifies the total number of payments, pmt refers to the periodic payments, pv represents the present value of the payments, and type indicates when payments are due.
18. RANDBETWEEN
This function is used to deduce a random integer within a specific number range.
For example =RANDBETWEEN(bottom,top)
will specify an integer between the lowest (bottom) and the highest(top) range.
19. QUARTILE
QUARTILE function is used to segregate the data according to a specific requirement.
The formulae for this function is
=QUARTILE(array, quart) where array refers to the respective cell range and quart refers to the value that needs to be figured out.
Conclusion
These are some common MS Excel Formulas that are commonly used and extremely helpful. To learn more about such MS Excel Formulas, join Select membership today!
You can also read –
