Excel, is a powerful software, that makes any official work easier. But doing it manually can be time-consuming. Excel was thus incorporated into formulas and functions to make our work easier. What this means is that now there is no need to do any calculations in excel manually instead Excel can do it for us if we tell it what formula we need to use. Here we will see different functions that one can use in MS Excel
1. Automatically calculating the number of days
Suppose if we have two different dates in two different cells in an Excel we can use a formula to calculate the number of days in between these two dates. For example, you can use “ =days360( start date, end date, method )” to calculate the number days instead of manually counting them.
2. Percent rank
Percent Rank is another function that is helpful her. It helps us to find the percent rank of any number on a narrow range and also authorizes us to determine the number of leading digits to return the rank for. For this function, you need to enter ‘=PERCENTRANK(array,x,significance)’ in the cell where you want the answer. This array defines the specified range on which we are going to rank our number, x is the number for which we want to get a rank, and finally, the significance is defined as the number of meaningful digits to which we wish our rank to be returned.
3. Automatically calculating the working days
This formula is like the first formula but is useful when we want to get the number of working days between two different dates. We can do so by entering the formula ‘=NetworkDays(start date, end date, holidays)’. Entering a start date and the end date will improve the system to remove all the Saturdays and Sundays and extra leaves if come from the total number of days between those two dates and give the total number of working days.
While copying data from places or while importing data from the web, we don’t notice the spaces around the data in the individual data cells. Excel provides a formula to remove these areas, and this formula or function is known as TRIM. So to trim data, we enter ‘=TRIM(cell’s address)’ into the cell where we want our result and then press enter.
This function helps to find the nth percentile from a given set of values. Here, we need to enter ‘=PERCENTILE(array, nth_percentile)’ into the cell where we want the output. Here array is the range from where our function will return the nth percentile and nth percentile is the percentile that we want. The value of percentile can be anyplace between 0 and 1.
Do you want to combine the contents of the different cell into one cell? Then the concatenate function is just what you need and to use it you just need to type ‘=Concatenate(Cell 1’s address, Cell 2’s address, Cell 3’s address,.. Cell n’s address)’ and on pressing enter after this you will see all your data from the specified cells coming into the cell where you opened the concatenation function.
7. Date value
Every date has a value with it in Excel, and these values can be calculated if it is required. To get the value of a date, all you need to do is to enter ‘=DATEVALUE(“Date”)’ into the the cell, and on pressing enter, you will get the value corresponding to that particular date.
As shown in above example, we have taken the date to be ‘June 14, 2015’, and on using the DATEVALUE function on it, we get a value of 42169.
We have found the slope of a line or a set of points or some data, using different formulas in maths and physics. In Excel to find the slope we need to enter ‘=SLOPE (known y’s, known x’s)’. Where x and known y are nothing but the x and y coordinates, using which we need to find our slope. So to find the slope we take some x values and the same number of y values for this formula, and get our answer easily.
For instance, if we have a stack of data with entries in two different columns like for example we can take the value of shares on any particular day, but here shares are marked with numbers alternatively of their names. So if you want to find the value of a share at a given day, you can use LOOKUP to provide straightaway us a result, instead of searching for it for hours manually. Here you enter ‘=LOOKUP(lookup value, lookup vector, result vector)’, lookup value is the value that we must look up against( the name of the share in our example), and the lookup vector is the vector in that our lookup value exists ( lookup vector is the vector carrying name of the shares, but we need to remember that numbers represent here shares and not by words or alphabets). The resulting vector is the one in which we intend to locate the result (result vector comprises the value of shares on a given day). It is a simple way of looking for data from a whole stack of it.
For using this function, all you need to do is enter ‘=MATCH(value, array, [match_type])’ in the cell wherever you want to get the result. Here value signifies the value you are looking for in the array, array signifies the range of cells that include the value that you are looking for and finally match_type is an elective thing that works by having match work in a way in which if match_type =1 the match function finds the greatest value less than or the equal to value, 0 finds the first value equal to the value and finally -1 will find the least value that is greater than or equal to the value.