# 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

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

30. Click Cell C20 to give it focus
31. On the Ribbon, click Home if necessary.
In the Editing section, click the AutoSum button
32. Select Cells C6:C19

33. On the Formula Bar, click the Enter button
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

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

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
63. On the worksheet, select Cells M6:M19

64. On the Function Arguments dialog box, click the stop selection button
65. In the Function Arguments dialog box, click OK
66. Click Cell F22 and type =SUM

67. Add the opening parenthesis (

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

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

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
24. Click Cell C14, type Net Loss and press Enter

25. Save the workbook