Basic, but one of the most useful formulae of Excel. The Sum is used for addition of one or more figures entered in different cells. Once the cell reference is decided by defining a range, we can insert values in the relevant cells to ultimately get their addition/sum as the result.
As per the name Count Function in Excel counts the number of cells with values inserted in them. A drawback for using this function is that it doesn’t count any other character than numbers. So, it would not consider text characters.
To counter the problem of Count Function, Count A was introduced. With Count A, Excel even considers text characters apart from just numbers. So if we insert values in the cells, the function counts the cells. One important thing about this function is that it counts only non-empty cells, whether it is text or number.
This formula actually counts the number of character in a cell. One thing that needs to be taken care of is that LEN even includes spaces while counting.
TRIM function eradicates all the extra spaces in a cell accept the single spaces between characters. It can be of extreme use if some data is transferred from a database, as it carries extra spaces which might create chaos when the data is compared by IF statement and VLOOKUP table.
Right, Left, Mid
The formulae mentioned above returns the quantified number of characters from a text string. The Right Function counts & returns the number of characters from the right. For instance in the text “Excel is Great” if we implement the formulae Right (A1, 6), then we will get the outcome as “Great”. The same application goes for the function Left and Mid.
Applied as VLOOKUP (lookup_value, table_array, col_index_num, range_lookup).
This is one of the most used formulae in Excel. Basically, this formulae assists in looking/searching for a precise information in the spreadsheet. For instance, the price for a specific article can be found from a list of similar or distinct articles with different prices. Let’s try to comprehend the same with and example. Supposedly we have to search for the price for a specific watch among an endless list of watches. We can put the relevant values in the formulae itself.
Also Read>>7 Excel Tricks to get work done faster!
Lookup_value: This contains the name of the watch as entered in the spreadsheet.
Table_array: This contains the location of cells to be searched for. For e.g. A2:B20, which may contain the name & price for that watch.
Column_index_num: Contains the specific location denoted by a number where the information is searched for. If the prices are mentioned in the second column then, the column_index_num will be 2.
Range_lookup: This implicates whether to look for exact or approximates matches. An exact search is denoted by False and an approximate by True. In case of numbers, it should be true as the exact value is required. Finally, the result will come out as the exact price of that particular watch.
Also Read>>Why a course in Excel is worth it!!
HLOOKUP (reference, array, row_number, row)
Similar to VLOOKUP, HLOOKUP is also used to search precise information in a spreadsheet. The only difference between these two functions are the first letters of the formulae i.e. V and H. Where VLOOKUP searches information vertically, HLOOKUP looks for it horizontally.
SUMIF, COUNTIF, AVERAGEIF
Formulas: =SUMIF (range, criteria, sum_range), =COUNTIF (range, criteria), =AVERAGEIF (range, criteria, average_range)
The aforementioned formulae are of great use in Excel and can simplify work to a significant extent. These formulae perform their pertinent functions i.e. SUM, COUNT, AVERAGE when a predefined condition(s) is met. That’s why they are implemented with IF function.
Concatenate (A1, ““, B1)
As the name suggests, this formulae combines the value of two different cells and mentions them into one defined cell. For instance, given name and surname of a person are mentioned in separate cells and if they are to be combined & mentioned together in a cell, we can use concatenate formulae for this. Space between two values is given by “”.
Utilizing above stated formulae can make using MS Excel much simpler and it can enhance the productivity to a new level. If after getting aware about these functions create an interest in you to know more about Excel and get profound with more such potential formulae and tricks on Excel, you always have an option to choose from some easy, interactive and significant e-learning courses offered by Naukri FastForward.