Excel Formulas, Functions, and Charts

 

 

FORMULAS

          A formula is the equation used to calculate values in a cell.

          The = (equal sign) is the first character in a formula

                    You must have the = sign for Excel to recognize the formula.

          A formula has two parts: 

                    Operand—number or cell reference in the formula

                    Operator—tells excel what to do with the operand

                             =b3+5 means b3 and 5 are operands

                             + is an operator and means to add the value in cell b3 to the number 5

                   Sample Operators:                   Order of Operation:

                   Addition +                   1.                   Contents in Parenthesis

                   Subtraction –                   2.  Priority Order**

                   Multiplication *                   3.                   Left to Right

                   Division /

                   Exponiation ^

 

                   **Priority Order

                                      First                   Exponentiation

                                      Second                   Positive/Negative

                                      Third                   Multiplication/Division

                                      Fourth                   Addition/Subtraction

 

          Cell References

1.                 Relative—adjusts to its new location when copied

                                                                                                  i.      A3

2.                 Absolute—do not change when moved

                                                                                                  i.      $a$3

3.                 Mixed—contains both relative and absolute references

                                                                                                  i.      $a3

 

          Create formulas by typing in operators and cell references or by clicking in cell references to enter. (Point & Click Method)

 

          AutoSum button is identified by Greek letter Sigma (backwards E).  Click on button to enter Sum formula.  Excel will enter most logical row or column to sum.  You can change it if needed.

          =sum(a3:b5)

 

FUNCTIONS

          Function Formulas are special formulas that do not use operators and operands to calculate a result.  The perform complex calculations in Mathematics, Statistics, Trigonometry, Accounting, Logic, and Finance.

          A function formula contains three parts

                    =Sum(d5:d10)

                    =sign

                    Sum is the function name

                    (d5:d10) is the argument--An argument is a value, cell reference, range, or text that acts as an operand.  It is always enclosed in parentheses.

          Functions can be entered into a cell by

A.               type directy in the cell

B.               Click on paste function button (Fx) on toolbar

C.               Insert, Function

 

          Some common functions include:

1.     Mathematical

                                                                          i.      Square root =Sqrt(number) ie (49)

                                                                        ii.      =Round(number, number of  digits) ie (B3, 2)

2.     Statistical

                                                                          i.      =Average (a3:a45) average of data in range

                                                                        ii.      =count(a3:a45) counts # of cells with data

                                                                      iii.      =max(a3:a45) maximum

                                                                     iv.      =min(a3:a45) minimum

                                                                       v.      =stdev(a3:a45) standard deviation

                                                                     vi.      =var(a3:a45) variance

3.     Financial

                                                                          i.      =FV(rate, pmt, pv) future value of series of regular payments at a fixed rate for a specified number of periods

                                                                        ii.      =PV(rate,nper,pmt) present value of series of equal payments at a fixed rate for a specified number of payments

                                                                      iii.      =PMT(rate,nper,pv) displays a payment per period needed to repay a loan at a specified interest rate for a specified number of periods

4.     Other

                                                                          i.      =Date—determines date format

                                                                        ii.      =Now()—displays current date/time based on computer clock


 

USEFUL FEATURES

1.     Format Painter

2.     Sorting Data

3.     Insert/Delete Worksheets

4.     Insert Pictures/Clip Art

5.     Copy from Word to Excel/Excel to Word

 

WORKSHEET CHARTS

          A Chart is a graphical representation of data contained in a worksheet.

          Types of Charts

1.     Column—uses bars of varying heights to represent data

2.     Line—similar to column but uses lines connected by a line

3.     Pie—shows the relationship of a part to a whole

4.     Scatter—XY charts show the relationship between two categories of data

 

To create a chart, choose the chart button on the toolbar or choose Insert, Chart.

1.     Highlight data you want to include in your chart

2.     Insert, Chart

3.     Select type of chart desired

4.     Click Next to follow Steps to select data, name chart titles, and other chart options

5.     Select if you want the chart in the current sheet or separate.

6.     Click Finish when done to view your chart

7.     Double-click on various parts of chart to edit or choose from chart menu

8.     Click on chart and hit delete key to delete a chart