Docstoc

calculations

Document Sample
calculations Powered By Docstoc
					                    A                        B             C            D           E                       F                   G   H   I   J
   Calculations are performed by typing in a formula beginning with an equals sign. The terms in the calculations are either
   values (eg 4 or 2.5) or the contents of cells whose address is given in the formula. The address of a cell is given by the
   column letter, followed by the row number (eg A2). A range of cells is given by specifying the first (top left) and last
   (bottom right) cell separated by a colon, for example B2:D2 or B2:B4 or B2:C3. The calculations on this sheet are carried
   out in column E. (Because Excel displays the result of the calculation, not the formula, we have repeated in column F the
   formula used in column E.)
 1
 2 1. Simple addition                              13           7           9          29   =B2+C2+D2
 3
 4 2. Using the SUM function                       13           7           9          29   =SUM(B4:D4)
   The sum function takes a single argument: the range of cells whose value is to be
   added up.Here the range is B4 to D4. This is written as B4:D4. The argument of the
 5 function is placed in brackets.
 6
 7 3. Calculate an average                         13           7           9         9.7   =(B7+C7+D7)/3
   Note the brackets. They force Excel to do the addition before dividing by 3. See the
 8 different result below if the brackets are missing.
 9
10 4. Without the brackets                         13           7           9          23   =B10+C10+D10/3
11 Without the brackets Excel divides d10 by 3 and adds the result to b10+c10
12
13 5. The AVERAGE function                         13           7           9         9.7   =AVERAGE(B13:D13)
   Like SUM the AVERAGE function takes as its argument a range of cells whose
14 values are to be averaged.
15
16 6. Some more calculations                        2           5                      11   =B16*3+C16
17 Note the effects of the brackets                 2           5                      16   =B17*(3+C17)
18                                                  2           5                    7.25   =B18*3+C18/4
19                                                  2           5                       4   =(B19*(3+C19))/4
20                                                  2           5           9           6   =B20-C20+D20
21                                                  2           5           9         -12   =B21-C21-D21
22                                                  2           5           9           6   =B22-(C22-D22)
23
                   A                       B               C           D          E                            F   G   H   I   J
24 7. Copying calculations. Copy cell E25 and paste it into cells E26:E27
25                                              13                7         9        29         =B25+C25+D25
26                                               2                5         9        16         =B26+C26+D26
27                                               4              17          3        24         =B27+C27+D27
28
   8. Copying calculations with absolute addresses. Copy cell E30 and paste it into
29 cells E31:E32
30                                               4                3       2.5       17.5        =(B30+C30)*$D$30
31                                               5                8                 32.5        =(B31+C31)*$D$30
32                                               6                2                  20         =(B32+C32)*$D$30

   When E30 is copied down into the next row the calculation uses B31 and C31, but it
   still uses D30. This is because the dollar signs tell Excel that the address $D$30 is
   an absolute address. Without the dollar signs the addresses are treated as relative,
33 so they change when they are copied into another row.
34

   9. Calculations using cells on other sheets. Copy cell E36 and paste it into cells
35 E37:E38. On Sheet2, cell A1 contains the value 2, A2 contains 4 and A3 contains 6.
36                                               13                                    26 =B36*Sheet2!A1
37                                                5                                    20 =B37*Sheet2!A2
38                                               23                                   138 =B38*Sheet2!A3
   To refer to a cell on another sheet, give the name of the sheet, followed by an
   exclamation mark and then the address of the cell. Note that in the above formulas
   the addresses of the cells on Sheet2 are relative addresses (no dollars) so when
   you copy the formula into another row the formula refers to the corresponding
39 address on Sheet2.
   To refer to a cell in a different Excel file enclose the filename in square brackets - for
   example [filename.xls]sheet3!A1:B4 - It is best to keep both files in the same
40 directory, as this makes it easier to cross-refer.
                      A                      B            C            D           E                   F                          G          H   I   J
     10. The SUMIF function adds the numbers in a range of cells, depending on whether
41   a given condition is satisfied.
42                                                 9
43                                                10
44                                                 4
45                                                16
46                                                 3
47                                                12                                  16 =SUMIF(B42:B47,"<10")
48                                                                                    38 =SUMIF(B42:B47,">=10")
     In E47 the formula adds up the numbers from the range B42:B47 which are less
     than 10 (<10). In E48 the formula adds the numbers that are greater than or equal to
49   10.
50
51 11. The IF function                        4 Fail                                  =IF(B51>=5,"Pass","Fail")
52                                              Blank                                 =IF(ISBLANK(B52),"Blank",9)
53                                  elephant    is not camel                          =IF((B53 = "camel"),"Yes","is not camel")
54                                  xxxxx                  5                          =IF(LEN(B54)>4,LEN(B54),"short")
55                                  xxx         short                                 =IF(LEN(B55)>4,LEN(B55),"short")
56
57 12. The VLOOKUP function: choose a value from a lookup table                                                                       1   Fail
58 Copy E53 and paste it into E54 and E55.                                                                                            2   Fail
59                                            3                            Fail       =VLOOKUP(B59,$G$57:$H$63,2)                     3   Fail
60                                            7                            Pass                                                       4   Fail
61                                            2                            Fail                                                       5   Pass
62                                            6                            Pass                                                       6   Pass
63                                                                                                                                    7   Pass
                  A                      B            C           D          E                      F                     G         H          I        J
   13. Date functions and the
64 OFFSET function
65 Sunday                          Friday                   6   4-Jan-08             column C=WEEKDAY(D65) - a number corresponding to the day of the week
66 Monday                          Saturday                 7   5-Jan-08             column B = OFFSET(A$64,C66,0)
67 Tuesday                         Sunday                   1   6-Jan-08             column B uses a number to calculate the OFFSET from a starting cell
68 Wednesday
69 Thursday                        Sunday            1/4/2009   4-Jan-08             column C=EDATE(D69,12)
70 Friday                          Monday            1/5/2009   5-Jan-08             column B=OFFSET(A$64,WEEKDAY(C70),0)
71 Saturday                        Tuesday           1/6/2009   6-Jan-08
72
73 14. String functions            Su 4/1/2009                                       =CONCATENATE(LEFT(B69,2)," ",DAY(C69),"/",MONTH(C69),"/",YEAR(C69))

   CONCATENATE is used to combine strings in a long string. Note that if you want
   spaces between the elements you must include a space as one of the arguments to
   the CONCATENATE function. The LEFT function extracts characters from the
   beginning of a string. There is a corresponding RIGHT function. The DAY, MONTH,
74 and YEAR functions extract the elements from a date.
75
76 Example files: www.st-andrews.ac.uk/its/training/training_materials/
2
4
6

				
DOCUMENT INFO
Categories:
Tags:
Stats:
views:13
posted:7/30/2012
language:English
pages:5