Excel Formulas

Top excel formulas & functions

1. CONCATENATE
Formulae: CONCATENATE(text1,text2,…)
Concatenate formulae is used for join several text in different cells.

2.Floor
Formulae: =FLOOR(number,significance)
Rounds a number down, toward zero

3.Exact
Formulae: =EXACT(text1,text2)
Checks to see if two text values are identical

4.LOWER
Formulae: =LOWER(text)
Converts text to lowercase

5.PROPER
Formulae: =PROPER(text)
Capitalizes the first letter in each word of a text value /br>

Excel Basic Math Formulae

Function Formula Example
To add up the total =SUM(cell range) =SUM(B2:B9)
To add individual items =Value1 + Value 2 =B2+C2
Subtract =Value1 - Value 2 =B2-C2
Multiply =Value1 * Value2 =B2*C2
Divide =Value1 / Value2 =B2/C2
Exponents =Value1 ^ Value2 =B2^C2
Average =AVERAGE(cell range) =AVERAGE(B2:B9)
Median =MEDIAN(cell range) =MEDIAN(B2:B9)
Max =MAX(cell range) =MAX(B2:B9)

Excel Tips & Tricks - Make your work lot easier

Microsoft excel have it's own world and it's not easy to know all the detail of this world, but tricks and trips help you to become a pro of this world.

If you just start learning Microsoft excel then you must start with basic first instead of go to the vast formula's. If you understand the logic's then you can easily make your own formula's as per your requirement.

In this post we will discuss about the Excel tips & tricks, which will make your work lot easier.

Statistical functions

Excel has a wide variety of built-in statistics functions that give, for instance, the slope and y-intercept
of a line, the standard deviation of a data sample, and the mean, median and mode of a set of values.
Here, we will cover a few of the more useful and popular statistics functions from which you can easily
obtain summary statistics directly; else you can use the Analysis Tool available from the Tools menu.

Basic Statistical built-in functions-:

Function	What it Does
AVERAGE(range)	Returns the average of its arguments
AVERAGEA(range)	Returns the average of its arguments, including numbers, text, and logical values
MEDIAN(range)	Returns the number in the middle of a range of data
MODE(range)	Returns the most frequently occurring or repetitive value in a range of data
COUNT(range)	Counts how many numbers are in a range of data
COUNTA(range)	Counts how many values are in a range of data
MAX(range)	Returns the maximum value of a range
MIN(range)	Returns the minimum value of a range
LARGE(range, n)	Returns the k-th largest value in a data set
SMALL(range, n)	Returns the k-th smallest value in a data set

                                                                              
functions, like AVERAGE( ), MODE( ) AND MAX( ). Assume a class’s grade distribution is as follows: 3, 0,4, 4, 4, 2, 4, 1, 4, 0, 3, 3, 1, 1, 3, 4, 2, 4, 0, 3, 3, 1, 3. These grades are based on a 4-point scale with 4=A and 0=F. Using the AVERAGE( ) function, we find the class's average (or arithmetic mean) grade is a
disappointing 2.48, or a mid-C. The syntax for this common function is =AVERAGE(number1,
number2, ...). However, we don't get a clear picture of the class’s performance by simply looking at its
average. We can further analyze the data using the MEDIAN( ) function. The median gives the middle
number in a set of numbers and its syntax is =MEDIAN(number1, number2,...). When the median grade
is calculated, it is 3.0, meaning that half of the grades are higher than 3.0, and half are lower. Therefore,
despite the low class average, more students scored 3's and 4's than 2's, 1's and 0's.



Additionally, we can also analyze the grade distribution by using the MODE( ) function. The mode gives
the most frequently occurring value of a set of numbers and its syntax is=MODE (number1,number2,...). Excel's built-in functions to determine the number of grades entered, and the maximum
and minimum grades of the distribution.

AVERAGE and AVERAGEA
The difference between AVERAGE and AVERAGEA becomes evident when one of the cells contains a
text OR A SPACE and don't forget the SPACE. A cell containing a space is NOT empty.

COUNT and COUNTA
If you want to count the number of cells that are not blank COUNT and COUNTA will return a different
result if in one of the cells there is a text or a space
=COUNT(B2:B7) will return 6 is only numbers are present in cells B2 to B7 and 5 if there is a letter,
an empty cell OR A SPACE in one of the cells.
=COUNTA(B2:B7) will return 6 unless one of the cells is empty. If all the cells contain numbers,
letters OR SPACES the result will be 6.

LARGE and SMALL
The MAX and MIN functions would give the largest and smallest value from a list of values. But what if
you want the second or third largest value or the second smallest value, use LARGE and SMALL
functions as follows:
=LARGE(A1:A5,2), =LARGE(A1:A5,3), =SMALL(A1:A5,2)

As a matter of factsyou can also get the MIN and MAX values using these functions.
=LARGE(A1:A5,1), =SMALL(A1:A5,1)


Linear regression functions
Excel has some built-in functions that allow a method for determining the slope, y-intercept,
correlation coefficient, and R-squared values of a set of data. The functions are SLOPE(), INTERCEPT(),
and CORREL(). These functions are easier and faster to compute than plotting the data. However, a
visual    graph    shows    trends    in    the    data    better    than    any    other    tool.

Function	What it Does
SLOPE	Returns the slope of the regression line through the given data points =SLOPE( y cell range, x cell range) =SLOPE(C2:C6,A2:A6)
INTERCEPT	Calculates the point at which a line will intersect the y- axis using a best-fit regression line plotted through the known x values and y values =INTERCEPT( y cell range, x cell range) =INTERCEPT(C2:C6,A2:A6)
CORREL	Return the correlation coefficient between two data sets. =CORREL( y cell range, x cell range)

 
acceleration and initial velocity. Then, we can determine the car's acceleration and its initial velocity
with the help of the SLOPE( ) and INTERCEPT( ) functions. Hence, the y-axis values represent the square
of the car's velocity and the x-axis values represent the car’s position or distance travelled. In order to
find the acceleration, we divide the slope by 2 and to find the initial velocity, we take the square root of
the y-intercept.

Distance travelled (in m)	Velocity (in m/s)	Square of Velocity 2      2                                                in m /s
2.00	6.90	47.61
4.00	6.00	36.00
6.00	4.90	24.01
8.00	3.40	11.56
10.00	0.00	0.00

                                                                              
Additionally, a plot of the data allows us to visualize the data and gross blunders and errant
data points are easily detected.

STDEV
For error analysis, we use the STDEV function. When we carry out a number of repetitive
measurements of one quantity, we find the average value. This does not however tell us anything of
the precision of our measurement. The standard deviation of the measured values will give a measure
of the precision. To quickly determine the standard deviation of any measurement, use Excel's built-in
STDEV( ) function.
=STDEV(A2:A6)
 
Statistical Analysis 
Microsoft Excel has numerous Add-in features that support statistical analysis. Statistical data analysis
in Excel is not recommended for analysing datasets with a large sample size or a large number of
variables, performing advanced statistical analyses, or for projects in which a number of procedures
need to be performed. Excel is a useful tool for answering basic analysis. The primary reason to use
Excel for statistical data analysis is because it is so widely available.

There are a number of disadvantages worth considering before using Excel for statistical analysis:
    Missing values are handled inconsistently, and sometimes incorrectly.    

• Data has to be organised differently according to the analysis you wish to perform.

• Most analyses can only be done on one column at a time. This makes it inconvenient to do the same analysis on many columns.
  

• There is no log or record of how an analysis was accomplished.

• It also lacks many important features for advanced analyses.

Using the Analysis ToolPak
Two specific tools are useful in generating descriptive statistics and histograms of grade distributions.
To access these features, the Analysis ToolPak must be loaded.

Check to see if the Data Analysis command is available in the Analysis group on the Data tab. If the
Data Analysis menu is available, then it indicates that the Analysis ToolPak is already loaded.

If the menu is not visible, then
1. Choose Add-Ins from the Excel Options found through the Microsoft Office Button.
 
2. In the Manage drop-down menu, select Excel Add-ins, and click Go.
  
3. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.

4. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click
Yes to install it.

5. A configuration progress screen for Microsoft Office appears and once completed, the Data
Analysis menu should appear on the Data menu in the Analysis group.