04 January 2016

MS Excel's Top 10 Formulae to utilize at corporate job!

Posted by NcrJobPortal.in  |  at   Monday, January 04, 2016

MS Excel's Top 10 Formulae to utilize at corporate job!
SUM
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.
 
COUNT
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.


COUNT A
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.

LEN
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
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.


VLOOKUP
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.


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.


HLOOKUP : 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 : 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.
Source: Naukri.com


Get Latest Freshers Jobs Alerts
Register Here, It's FREE

www.ncrjobportal.in

Freshers Jobs in Delhi/NCR 2015, Noida, Gurgaon, Latest Today Freshers Jobs, Walkins, Recruitment, Vacancies For B.Tech, M.C.A Freshers 2015, Freshers 2016 Job Openings in Delhi/NCR. Today Walkins, Latest and Upto date Freshers Jobs in Delhi/NCR This Week and IT Freshers Jobs in Delhi/NCR.

PHP DEVELOPER JOBS | MBA JOBS | PHP JOBS

Subscribe For e-Jobs

Subscribe To Our E-mail Newsletter To Receive Latest Career Updates and Job Information

Share This Job

Related Jobs

 

TOP JOBS OF THE WEEK

Let's Help Someone to Get a First JOB TODAY!

Looking to KickStart Career ? Like Us to Get Job Updates!

Receive All NCR Jobs Updates via FB, Join Us

Powered By NCRJOBPORTAL

back to top