Microsoft Excel is a spreadsheet program included in the Microsoft Office suite of applications. ... Spreadsheets present tables of values arranged in rows and columns to perform arithmetic operations and functions.
Excel has over 475 formulas in its Functions Library, from simple mathematics to very complex statistical, logical, and engineering tasks.
In Microsoft Excel, a formula is an expression that operates on values in a range of cells.
These formulas return a result, even when it is an error. Excel formulas enable you to perform calculations such as addition, subtraction, multiplication, and division.
Most useful Excel Formulas : -
1. SUM - Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)
It allows you to add 2 or more numbers together. You can use cell references as well in this formula.
2. COUNT – Formula: =COUNT(A1:A10)
The count formula counts the number of cells in a range that have numbers in them.
3. COUNTA - Formula: =COUNTA(A1:A10)
It counts the number of non-empty cells no matter the data type.
4. LEN - Formula: =LEN(A1)
The LEN formula counts the number of characters in a cell. Be careful though! This includes spaces.
5. TRIM - Formula: =TRIM(A1)
Gets rid of any space in a cell, except for single spaces between words. I’ve found this formula to be extremely useful because I’ve often run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP’s.
6. RIGHT, LEFT, MID - Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).
These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.
7. VLOOKUP - Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Vlookup is used for find value in table array. It is very useful function for matching two data base on common value. Suppose you have thousand of order details in which you have to find few hundred order in master data weather these are exit in your master order data or not
8. IF Statements - Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.
9. MATCH - Formula: =MATCH (lookup_value, lookup_array, [match_type])
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table.
10. INDEX - Formula: =INDEX (array, row_num, [col_num], [area_num])
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.
You must be logged in to post a comment.