First Things First

In order to manipulate data in Excel, you must understand the basic format that all formulas in Excel use. Every formula begins with an equal (=) sign. A number, cell reference, or function follows. To specify a range of cells to use in a function, enter the following: firstcell:lastcell, where firstcell represents the first cell in the range and lastcell represents the last cell in the range. To enter multiple cells that are not in a sequential range, separate each of the individual cell names with a comma. In formulas that require multiple parameters or that use nested functions, you will need to enclose a listing of multiple nonsequential cells and other operations in parentheses.

Simple Addition, Subtraction, Multiplication & Division

If all you want to do is add, subtract, multiply, or divide a few numbers, a simple formula is all you need. To add numbers, use the plus (+) sign; to subtract, use the minus (-) symbol; to multiply, use an asterisk (*); to divide, use a forward slash (/). To use a simple formula that adds three numbers, for example, you would enter =1+2+3 into the formula bar.

Use Data From A Cell

Instead of manipulating hard-coded data, you may want to perform calculations by referencing a number from a cell. To do so, use the same formula for simple calculations but replace the numbers with cell references. For example, to multiply the number in cell A2 by the number in cell D6, enter =A2*D6.

Add A Block Of Numbers

One of the functions we use most in Excel is the SUM function, which lets you add a group of numbers. You can use this function with numbers alone, but many times it’s more helpful to add all of the numbers in a range. To use the SUM function, enter =SUM(range), where range represents the group of cells you wish to add.

Average A Range Of Numbers

The AVERAGE function calculates the arithmetic mean of a group of numbers. Excel does this by adding the group of cells and then dividing by the total number of cells. To calculate the average of a group of numbers, enter =AVERAGE(range) into the Excel formula bar, where range represents the group of cells for which you wish to formulate an average.

Round Data

Excel has three functions that let you round data. To round data in a traditional sense, with numbers less than 0.5 rounding down and the rest rounding up, use the ROUND function. To round a number down to the nearest whole number, use the ROUNDDOWN function. Likewise, to round a number up to the nearest whole number, use the ROUNDUP function. All three of these commands use the same format: =ROUND(number, numberofdigits), where number is the number or cell you wish to round and numberofdigits specifies the number of decimal places to use in rounding. A negative numberofdigits rounds to the left of the decimal point, and zero rounds to the nearest integer.

Determine Minimum & Maximum

If you want to know the minimum or maximum value from a list of numbers, use the MIN and MAX functions. The format for the MIN and MAX functions is as follows: =MIN(range) or =MAX(range). Alternatively, if you want to know the nth largest or smallest number, you can use the SMALL and LARGE functions. To find the second smallest number in a range from A2 to A73, type =SMALL(A2:A73, 2). To find the fourth largest number, type =LARGE(A2:A73, 4).

Multiple Functions In One

Many times, you’ll see multiple Excel functions combined in one longer function. For example, the following statement combines multiple functions: =IF(AVERAGE(F2:F5)>50, SUM(G2:G5),0). In this IF function, the first parameter uses the AVERAGE function combined with a test to see if the average of a group of numbers is greater than 50. The second parameter uses a SUM function to add a group of cells if the first parameter is true. The final parameter sets the value of the IF statement to zero if the first parameter is false.

Count Data

Instead of counting the number of cells containing a specific value manually, use the COUNT and COUNTIF functions. The COUNT function counts the number of cells in a list that contain numbers. To use this function, type =COUNT(range). To count the number of cells based on a condition, such as the number of times an invoice value less than a specific dollar amount appears, type =COUNTIF(range, criteria), where criteria specifies which data is to be included in the count.

Convert Data From One Measurement To Another

You can use Excel to convert data from one measurement to another. To use the CONVERT function, enter =CONVERT(number, “from”, “to”), where number represents the number or cell you wish to convert, from represents the original measurement, and to represents the converted unit of measurement. For example, to convert 18 degrees Celsius to Fahrenheit, you would type =CONVERT(18,”C”,”F”).

Some of the most common values of measurement to use in the CONVERT function are listed. (NOTE: You must enter the quotation marks in the formula.)

Additional formats for other units of measurement are available from Microsoft Office Online at office.microsoft.com/enus/excel/HP100623111033.aspx.

If the CONVERT function is not available in your version of Excel, you will need to install and load the Analysis ToolPak add-in by navigating to the Tools menu, selecting Add-Ins, and then checking the box next to Analysis ToolPak. Click OK to finish.

Combine Text From Multiple Cells Into A Single Cell

Spreadsheets often contain data separated into multiple cells. Sometimes, however, we need the data to appear in the same cell. To combine data from multiple cells, use the CONCATENATE function and enter =CONCATENATE(parameter1, parameter2, parameter3, . . .) where each of the parameters represents text you want to combine into one cell. If you want a blank space or other punctuation not found in a particular cell, enter it as a parameter enclosed in quotation marks. For example, =CONCATENATE(A2,” “,B2) combines the information from A2 and B2 with a space between the data from each cell.

If This . . . Then This

Many things in life are conditional: If we purchase a plane ticket, then we can board the airplane. Likewise, Excel offers the ability to test for a condition and react according to the test results using the IF function. The IF function requires three parameters: The first tests for a condition, the second tells Excel what to do when that condition is true, and the third parameter tells Excel what to do if the first parameter is false. To use the IF function, enter =IF(parameter1, parameter2, parameter3) into the formula bar, replacing parameter values to reflect your test scenario. For example, if the number of guides required for a tour depends on the number of participants, you might use a formula such as the following: =IF(F2>10, 2, 1). In this case, the statement says if there are more than 10 people attending the tour (indicated by the number in cell F2), you’ll need two tour guides (indicated by parameter 2). Otherwise, you’ll only need to hire one tour guide (indicated by parameter 3).

Home Excel 2007 Function Fast Number-Crunching With Excel
Search MS Office A-Z   |   Search Web Pages/ Design A-Z