Fundamental Built In Functions

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

Fundamental Built-In Functions

The Sum Function

The SUM function is used to get the addition of various numbers or the contents of various cells. The result can be displayed in another cell or used in an expression.

On the Ribbon, in the Home tab, the Editing section is equipped with a button called the AutoSum

AutoSum

There are two most primary ways of using the AutoSum. You can click an empty contiguous cell, and then click the AutoSum button . Before performing the SUM function, the computer will ask whether it found the right cells that you want to get the sum of. If the computer found the right cells, you can press Enter; otherwise use your mouse or your keyboard to select the cells you want to consider. You can also select the cells involved in a sum plus an empty cell that will be used to display the result, and then click the AUTOSUM button.

Practical Learning: Using AutoSum

  1. Start Microsoft Excel
  2. Open the CPAR Accounting Records2.xlsx workbook and, if necessary, click the Transaction Analysis sheet
  3. Based on the descriptions of the transactions we saw in Lesson 9, enter the values as follows.
    Click Cell C6 and type +18,000
  4. Click Cell M6 and type +18,000
  5. Click Cell C7 and type +745
  6. Click Cell M7 and type +745
  7. Click Cell I8 and type +1,250
  8. Click Cell K8 and type +1,250
  9. Click Cell C9 and type (1,850)
  10. Click Cell I9 and type 1,850
  11. Click Cell K10 and type 120
  12. Click Cell M10 and type (120)
  13. Click Cell C11 and type 1,150
  14. Click Cell M11 and type 1,150
  15. Click Cell C12 and type 650
  16. Click Cell E12 and type 400
  17. Click Cell M12 and type 1,050
  18. Click Cell C13 and type (350)
  19. Click Cell M13 and type (350)
  20. Click Cell C14 and type (320)
  21. Click Cell G14 and type 320
  22. Click Cell C15 and type (120)
  23. Click Cell K15 and type (120)
  24. Click Cell C16 and type 400
  25. Click Cell E16 and type (400)
  26. Click Cell C17 and type (2,820)
  27. Click Cell M17 and type (2,250)
  28. Click Cell M18 and type (350)
  29. Click Cell M19 and type (220) and press Ctrl + Home
     
    Functions
  30. Click Cell C20 to give it focus
  31. On the Ribbon, click Home if necessary.
    In the Editing section, click the AutoSum button AutoSum  
  32. Select Cells C6:C19
     
    Function
  33. On the Formula Bar, click the Enter button Enter
  34. Click Cell E20
  35. On the Ribbon, click Formulas
  36. In the Function Library section, click the AutoSum button
  37. Select Cells E6:E19 and press Enter
  38. Right-click Cell C20 and click Copy
  39. Click Cell G20 and press Enter
  40. Click Cell I20
  41. In the Function Library section of the Ribbon, click the Insert Function button 
  42. In the Insert Function dialog box, in the top text box, type Add cells values and click Go
  43. In the list of sought functions, click SUM
     
    Insert Function
  44. Click OK
  45. In the Function Arguments dialog box, delete the content of the first box and click the selection button 
  46. On the worksheet, click Cell I8
  47. In the Function Arguments dialog box, click the stop selection button 
  48. In the Function Arguments dialog box, click the second box and type I9
  49. In the Function Arguments dialog box, click OK
  50. Click Cell K20
  51. In the Function Library section of the Ribbon, click the Math & Trig button
  52. Scroll down in the list of functions and click SUM
  53. In the Function Arguments dialog box, delete the content of the top text box
  54. On the worksheet, click Cell K8
  55. In the Function Arguments dialog box, click the second text box
  56. On the worksheet, click Cell K10
  57. In the Function Arguments dialog box, click the third text box
  58. On the worksheet, click Cell K15
     
    Function
  59. In the Function Arguments dialog box, click OK
  60. Click Cell M20
  61. In the Function Library section of the Ribbon, click the Recently Used button and click SUM
  62. In the Function Arguments dialog box, click the selection button Selection
  63. On the worksheet, select Cells M6:M19
     
    Function
  64. On the Function Arguments dialog box, click the stop selection button Stop Selection
  65. In the Function Arguments dialog box, click OK
  66. Click Cell F22 and type =SUM
     
    Functions
  67. Add the opening parenthesis (
     
    Function
  68. On the worksheet, click Cell C20 and type ,
  69. On the worksheet, click Cell E20 and type ,
  70. On the worksheet, click Cell G20 and type ,
  71. On the worksheet, click Cell I20
     
    Function
  72. Press Enter
  73. On the worksheet, click Cell L22 and =SUM(
     
  74. On the worksheet, click Cell K20
  75. Press and hold Ctrl
  76. Click Cell M20 and release Ctrl
  77. Type ) and press Enter
     
    Function
  78. Save the work book

The Absolute Value

The decimal numeric system counts from minus infinity (-∞) to infinity (+∞). This means that a  number can be usually negative or positive, depending on its position from 0, which is considered as neutral. In some operations, the number considered will need to be only positive even if it is provided in a negative format.

The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of –12 is 12.

To get the absolute value of a number, you can use one of the ABS() function. Its syntax is:

Function ABS(number) As Number

This function takes one argument. The argument must be a number or an expression convertible to a number:

  • If the argument is a positive number, the function returns it
  • If the argument is zero, the function returns 0
  • If the argument is a negative number, the function is returns its equivalent positive value

Practical Learning: Using the Absolute Value

  1. The CPAR - Accounting Records2.xlsx workbook should still be opened.
    Click the Income Statement tab
  2. Click Cell J7 and type =
  3. Click the Transaction Analysis tab
  4. Click Cell M11 and type +
  5. Still in the Transaction Analysis tab, click Cell M12 and press Enter
  6. Back in the Income Statement sheet, click Cell H9 and type =ABS(
  7. Click the Transaction Analysis tab
  8. Click Cell M17 and press Enter
  9. Back in the Income Statement sheet, click Cell H10 and type =ABS(
  10. Click the Transaction Analysis tab
  11. Click Cell M18 and press Enter
  12. Back in the Income Statement sheet, click Cell H11 and type =ABS(
  13. Click the Transaction Analysis tab
  14. Click Cell M19 and press Enter
  15. Back in the Income Statement sheet, click Cell H12 and type =ABS(
  16. Click the Transaction Analysis tab
  17. Click Cell M10 and press Enter
  18. Back in the Income Statement sheet
  19. Click Cell J13 and type =SUM(
  20. Select Cells H9:H12 and press Enter
  21. Click Cell J14 and type =
  22. Click Cell J7 and type -
  23. Click Cell J13 and, on the Formula Bar, click the Enter button Enter
  24. Click Cell C14, type Net Loss and press Enter
     
    Net Loss
  25. Save the workbook