Excel Formulas and Functions (An introduction)
The power of Excel is its ability to allow the user to manipulate or analyze groups of numbers or text. Whether the user needs a column of numbers added up to give a final sum, or the count of items in a row or column that fit a certain criteria, Excel gives the user that power and much more. There are 9 categories of functions, including hundreds of functions in Excel. Today's "Tip" will both provide steps to using formulas and functions, but also use videos to show the process in an Excel spreadsheet. Remember, if you have ideas for the Computing Tip of the Week,please send them to Pat Fellows.
Definitions
FORMULA: An equations that performs a calculation on data in a worksheet.
FUNCTION: A pre written formula that will perform calculations automatically.
To understand functions the following terminology will be helpful:
* Function name: identifies the type of calculation
* Argument is the data the function uses to perform the calculation
Excel Spreadsheet & Functions and Formulas Button
The items indicated on the image above will be referenced in the text below.
Formulas
In its basic form, a formula adds, subtracts, multiplies, or divides two numbers, displaying the answer in a cell that the user chooses. Most formulas contain at least 4 separate parts: an equal sign (=), a reference to a cell containing a value, a math symbol and another cell reference. Example: =F4+G4
In the example above, the formula that is in cell H4 is shown in the Formula Bar.
Use cell references instead of numbers when building formulas. When a cell value that's referenced by a formula changes, a new answer is automatically calculated.
Users can also enter formulas by the pointing method. Choose the cell into which the formula will be entered. Press the Equal (=) key. Click in the cell that contains the first item to include in the formula. Excel transfers the cell reference into the Formula Bar. Press the math symbol that corresponds to the math operation to be performed. Click in the cell that contains the second item to include in the formula. When the formula is complete, press Enter.
Order of Operators
When users combine 2 or more math operators in the same formula, Excel uses the “order of operators” rule to decide which math function to perform first. The order of operators is:
• Anything inside ( )
• Raising to powers ^
• Multiplication or division * /
• Addition or Subtraction + -
When a formula is entered with more than one operator, Excel looks through the formula and does the calculation with the highest priority first. If the two operators are on the same level of priority (+ and -), it then works left to right.
Example: Excel calculates the formula 2+1*3 and comes up with the answer 5 . It multiplies 1*3=3 then adds 3+2=5 .
If the same formula is written as (2+1)*3 , the calculated answer will be 9 . Excel adds 2+1=3 , then 3*3=9 .
Practice with Formulas
For a more complete explanation of how to work with formulas, Microsoft Office Online has the following lesson available:
* Get to Know Excel: Enter formulas - this is an audio and self paced lesson with a practice exercise at the end.
Functions
Functions perform a variety of special calculations and tasks that would be too difficult or too cumbersome for users to do using the basic formulas. Functions can be used to average a range of numbers, to perform statistical actions on values, to count the number of entries in a list, to sum a range of numbers and much more.
Excel offers nine categories of functions with hundreds of different functions available.
The nine categories are: Financial, Time & Date, Math & Trig, Statistical, Lookup & Reference, Database, Text, Logical, and Information.
Only a few will be immediately useful until users become experienced. The most common Functions are listed in the following table:
FUNCTION/DESCRIPTION
=Average(range) / Averages the values appearing in the cell range.
=NOW() / Displays the current date and time in the active cell.
=SUM(range) / Sums the values appearing in a cell range.
=SQRT(value) / Displays the square root of a value.
When using a function, don't enter any extra spaces between the equal (=) sign and the function name and the arguments. Upper or lower case characters can be used when entering a function. The Arguments must be enclosed in Parentheses.
When indicating a range of numbers in the argument of a function, the colon (:), indicates "through". For example to sum all numbers between cells B5 and B39, the formula would be written as: =SUM(B5:B39).
Here is an example of how the Sum function looks in the formula bar and on the spreadsheet as it is created.
For more information and helpful techniques on how to use functions in Excel, use the Find Functions and enter arguments training at Microsoft Office Online.
There are also many other training sessions available at Microsoft Office Online.