Some Useful Functions in Excel
Soumen Prasad Mandal[i]
In the uniform curriculum structure of M.Ed. as per NCTE new regulations 2014, use of Excel has
been included. Microsoft Excel is one of the most used software
applications of all time. Hundreds of millions of people around the world use
Microsoft Excel. You can use Excel to enter all sorts of data and perform
financial, mathematical or statistical calculations.
Objective of this article is to
help those Excel users who wants to extract more from Excel beyond performing
simple calculations and making Charts
etc. In Excel, formula is an expression which
calculates the value of a cell. Functions are predefined formulas and
are already available in Excel and help you save time. Here are some useful functions for data
analysis.
·
Count and Sum:
The most used functions in Excel are the functions that count and sum. To count
cells based on one criteria (for example, higher than 9), use COUNTIF
function. To sum cells based on one criteria (for example, green), use the
following SUMIF function.
·
Random numbers : RAND()
function calculates a random number between 0 and 1 every time the cell is
refreshed. RANDBETWEEN function is used for any range.
o Example
: To generate a random array between 10 to 90 in A1:E5, Enter
=RANDBETWEEN (10,90) at cell A1 and drag it to copy to E5
o
CHOOSE function returns a value from a
list of values, based on a position number
o
INDEX function returns a specific value
in a two or one-dimensional range.
o
VLOOKUP function looks for a value in the
leftmost column of a range and returns the value in the same row from a
selected column. The example gives the corresponding value of column C if value
from column A2:A4 is given
.
o
HLOOKUP function looks for a value in the
topmost row of a table, and then returns a value in the same column from
another row you specify. The example gives the corresponding value of row 3 if
value from B1:D1 is given.
·
Statistical:
To calculate the mean, median, mode and
standard deviation, we use the AVERAGE, MEDIAN, MODE
and STEDV function. AVERAGEA and STDEVA includes
text values as 0 while calculating.
o
Example 1: To get the sum of the squares of the range
A1:D5,
without array formulas, we make a column of squares and then use SUM.
Single cell array formulas can perform multiple calculations in one cell. using array à SUM(A1:D5* A1:D5) and press Ctrl+Shift+Enter
without array formulas, we make a column of squares and then use SUM.
Single cell array formulas can perform multiple calculations in one cell. using array à SUM(A1:D5* A1:D5) and press Ctrl+Shift+Enter
o
Example 2: To get the frequencies of the range A1:E5 with
given array of cut points G1:G6. Here answer is an array H1:H6.
We select the answer range H1:H6. Use the array formula =FREQUENCY(A1:E5,G1:G6) and press Ctrl+Shift+Enter.
We select the answer range H1:H6. Use the array formula =FREQUENCY(A1:E5,G1:G6) and press Ctrl+Shift+Enter.
Ms-Excel has a lot of functions, but you can learn only a few.
Inclusion of Ms-Excel in the M.Ed. curriculum will be effective if teacher
educators love to use it and explore its functionality to solve various
problems.
Thank you sir... this post will help me any time.
ReplyDelete