One of the major reasons for the popularity of Excel as a spreadsheet program is the ability of the user to avail of Excel worksheet functions that are very commonly used in accounting and other mathematical procedures. There is a rich library of Excel worksheet functions for helping people easy or difficult mathematical calculations. It makes very good sense to master the ones that are applicable to your purposes in order to hasten your work.
The COUNT group of functions includes three related functions.
The COUNT function proper simply counts all the cells within a given range of cells that contain numbers. For instance if A1 contains "5" and B1 contains "end" and C1 contains "7", typing the following "=COUNT(A1:C1) into cell A2 will show the value of "2" in that cell since only two cells contain numbers in the specified range.
The COUNTA function will give you the number of cells that contain data, regardless of data type. It will also include cells which have been assigned formulae even if the result of the calculation is a blank. In the above example, the COUNTA function would give a value of "3."
The COUNTBLANK function computes for the number of cells that are blank, excluding cells which are blank because the results of their assigned formulae evaluate to null. In our example, the COUNTBLANK function would return "0."
The INDEX function of excel gives you back the value contained in one cell in a one- or two-dimensional array or list of cells. You need to supply the function with three values for it to work.
The first value it needs is the cell array designation. For one-dimensional arrays you specify the first cell and last cells in the row or column you want. For two-dimensional arrays you begin with the upper left corner of the matrix, followed by the lower right corner. For instance, you could designate the row of cells from A3 to D5 with "A3:D5" to give you a matrix of four columns and three rows.
The second value it requires is the row position of the cell whose value you are interested in. This is required for one- or two-dimensional arrays. For instance if in our matrix above we need the second row we write "2."
The third value is only needed for two-dimensional lists and is the column designation of the cell we are interested in. Still using the matrix above, if we are interested in cell C4, the INDEX function would be written as INDEX(A3:D5,2,3), C4 being in the second row/third column position.
As the name indicates the SUM function adds all the numbers, separated by commas within its pair or parentheses. Alternatively, you may use cell pointer pairs for summing ranges or individual cell designations instead of literal numbers.
The formula for literal numbers would be written as "SUM(2,7,2)" giving you the value "4" in the cell that you typed the formula in.
The syntax for cell ranges is like this "SUM(D2:F2)." If D2 contains "6" and E2 contains "1" and F2 contains "4" the result shown will be "11."
The syntax for individual cells is written as "SUM(D2,E2,F2)" and gives you the same results as the second instance.
- Photo Credit number background image by kuhar from Fotolia.com
How to Use Arrays in Microsoft Excel
Arrays in Excel are a great shortcut when performing equations on entire sets of data. Here's how to use them.