If you have a large spreadsheet model, you might find it helpful to monitor the values in a few key cells as you change various input cells. The Excel Watch Window feature makes this task very simple. Using the Watch Window, you can keep an eye on any number of cells, regardless of which worksheet or workbook is active. Using this feature can save time and eliminate scrolling and switching among worksheet tabs and workbook windows.
About the Excel Watch Window
To display the Watch Window, choose Formulas➜Formula Auditing➜Watch Window. To watch a cell, click the Add Watch button in the Watch Window and then specify the cell in the Add Watch dialog box. When the Add Watch dialog box opens, you can add multiple cells by selecting a range or by pressing Ctrl and clicking individual cells.
For each cell, the Watch Window displays the workbook name, the worksheet name, the cell name (if it has one), the cell address, the current value, and the formula (if it has one).
Excel remembers the cells in a Watch Window, even between sessions. If you close a workbook that contains cells being monitored in the Watch Window, those cells are removed from the Watch Window. But if you reopen that workbook, the cells are displayed again.
The image below shows the Watch Window, with several cells being monitored.
Customizing the Watch Window
The Watch Window is a task pane, and you can customize the display by doing any of the following:
➤ Click and drag a border to change the size of the task pane.
➤ Drag the task pane to an edge of an Excel workbook window, and it becomes docked rather than free-floating.
➤ Click and drag the borders in the header to change the width of the columns displayed. By dragging a column border all the way to the left, you can hide the column.
➤ Clickone of the headers to sort the contents by that column.
Navigating with the Watch Window
You can also use the Watch Window as a navigational aid. If you find that you often need to switch among worksheets, add a cell for each worksheet to the Watch Window. To activate a cell displayed in the Watch Window, double-click it in the Watch Window.
Note:Unfortunately, in Excel 2013, this navigational technique works only with the active workbook. In other words, double-clicking a Watch Window item that points to a cell in a different workbook will not activate the workbook.
You can retrieve just the month, day, or year portion from a date by using the MONTH, DAY, and YEAR functions.
The syntax for the MONTH, DAY and YEAR functions are:
Retrieving the month, day or a year from a date is done by using the corresponding function along with a cell address of a date or a date itself for the argument. The Table below shows the results of using the DAY, YEAR, and MONTH functions in Excel.
Value in Cell A1
You can grab the current month, day, or year from the TODAY or NOW functions as shown in Table below.
Result if today was 11/16/2015
Returns the current month
Returns the current day
Returns the current year
■ Note You can’t use a date directly in the formula such as =MONTH(03/15/2010) because the month hasn’t been stored as a serial date and the MONTH, DAY, and YEAR functions can only use dates stored as serial dates.
Values can be added or subtracted from the MONTH, DAY, and YEAR functions. Let’s say that our business tries to fill its orders within three months of the order date. We can do this by adding 3 to the month in the order date. The day and year for the required date will be the same as those from the order date. We will use the data in the image below.
The function that follows will be entered in cell C2 and then copied down through cell C5.
=DATE(YEAR(B2), MONTH(B2)+3, DAY(B2))
The image below shows the result. The month in cell B4 is 10. Adding 3 to 10 should give us a month of 13, but Excel is smart enough to know there isn’t any month 13 and automatically adjusts the date so that the month becomes January. Adding 3 to the month in cell B5 automatically adjusted the month to February.
Excel will also automatically adjust the date if there ends up being more days than there are in a month.
DAYS Function – Number of Days Between Two Dates in Excel
The DAYS function is used to find the number of days between two dates. The syntax for the DAYS function is:
Argument descriptions are: end_date and start_date are the two dates between which you want to know the number of days. If the date 1/5/1994 was in cell A1 and you wanted to know how many days were between that date and today’s date, you would use today’s date as the end date and 1/5/1994 as the start date. You would enter the following formula in a cell.
EXERCISE: Use The DAYS Function
In this exercise, we will use the DAYS function to determine how many days fall between a series of start and end dates.
1. Create a new worksheet named “DAYS.”
2.Enter the data as shown in the image below:
⇒ Enter and format the column heads.
⇒ Enter the date in cell A2 and then hold down the Ctrl key while you use the AutoFill Handle to copy the date down through cell A12. Remember holding down the Ctrl Key copies the cell rather than creating a series.
⇒ Enter the date in cell B2 and drag the AutoFill Handle from cell B2 to B5.
⇒ Enter the date in cell B6and drag the AutoFillHandle from cell B6 to B10. See image below.
3. In cell C2 enter =DAYS(
4. Select cell B2 for the end_date argument. Enter a comma
5. Select cell A2 for the start_date argument. See image below.
6. Press Ctrl + Enter. Use the AutoFill Handle to copy the formula down through cell C10. The image below shows the result.
Now that you have experience using the DAYS function try the following exercises:
Find out how many days you have been alive. 1. In cell A11 enter your birthday. 2. In cell B11 enter =TODAY() 3. In cell C11 enter =DAYS(B11,A11)
Find out how many days till Christmas 1. In cell A12 enter =TODAY() 2. In cell B12 enter the date for Christmas for the current year 3. In cell C12 enter the DAYS function to compute the number of days between the current date and Christmas.
The Excel NOW function is similar to the TODAY function except that the NOW function includes the time along with the current date. Another difference is that the NOW function is not dynamic. It does not update to the current date when you reopen the worksheet. The syntax for the NOW function is
The table below shows an example of the NOW function applied on October 20, 2015 at 3:18 PM.
Displays current date and time
Notice that the excel NOW function displays time in a 24-hour format. You can change the Time format so that it will display in a 12-hour format using AM or PM. Changing the time to a 12-hour format requires a custom format. This date and time 6/8/2016 16:16 is the result of entering =NOW()in a cell. To change to a 12 hour format that displays AM or PM you would right-click the cell and select Format. This would bring up the Format Cells dialog box. See image below. Make sure that Custom is selected for the Category and m/d/yyyy h:mm is selected for the Type.
You would then add AM/PM to the end of the type. See image below. The result is shown in the Sample area. You would then click the OK button and the cell would display the same value as what appears in the Sample area.
If you just wanted to display the time without the date you could use the following formula:
=NOW() – TODAY()
DATE Function—Returns the Serial Number of the DATE
The DATE function returns the serial number of the date provided in the DATE arguments. The syntax for the DATE function is:
=DATE(year, month, day)
All of the arguments in the DATE function are required. When you create a new workbook all of the cells are formatted as General by default. If you enter the formula =DATE(2015, 10, 20) Excel will automatically change the formatting of the cell to the date type and display 10/20/2015. The date is actually stored as a serial number so that it can be used in formulas. If you format the cell as something other than a date or time, Excel will display the date as a serial number. Remember, the serial number is the number of days that have passed since January 1, 1900.
The Excel DATE function is most useful in situations in which you are getting the year, month, and day from other cells or when you want to use the date as part of a formula. You can’t enter a date directly in a formula. If you enter a formula as =09/07/2015–09/05/2015Excel treats the forward slashes as divide signs. If you want to use a date in a formula you should use the DATE function. If you wanted to know the number of days between two dates you could enter a formula such as =DATE(2015,4,25) – DATE(2015,2,20) or let’s say that you have the formula =DATE(2015, 4, 25) in cell A2 and the formula =DATE(2015,2,20) in cell B2. Entering the formula =A2-B2 will return a result of 64. The result is the number of days between the two dates .
■ Note: Excel returns a number rather than a date when you enter the DATE function as a part of a formula such as =Date(2015,5,13) – Date(2015,2,15) because it formats it as the General type. If you just enter =Date(2011,5,13) in a cell Excel will display it as a date because Excel formats it as a Date type.
Excel Date Functions allows you to use dates and times in formulas. Excel can do this because it actually stores dates and times as numbers. Excel stores date as serial numbers. A serial number treats January 1, 1900, as the first day of the calendar. It is considered as day 1. January 2, 1900 is considered day 2, January 3, 1900 is considered day 3, etc. Because Excel treats January 1, 1900 as the first day you can’t use any dates earlier than this. If you do, Excel will treat the date as text.
You can see how Excel treats dates and times by formatting them as General. In the example shown in the image below, 1/1/1900 was entered in both cells A1 and B1. The date 3/2/1010 was entered in both cells A2 and B2. Cells B1 and B2 were formatted as General.
Cell B2 displays 40239, which is the number of days between 1/1/1900 and 3/2/2010. Excel stores times as a value between 0 and 1. The time 12:00 AM would be stored as a value of 0. The time 11:59:59 PM would be stored as a value of .9999.
Excel TODAY Function
The TODAY()function returns the current date, which it gets from your computer’s internal clock. The TODAY() function doesn’t use any arguments. Even though the TODAY function doesn’t use any arguments you must still enter parentheses after the function name so that Excel recognizes it as a function. The syntax for the Excel TODAY function is:
See Table below for some examples.
Result if today was 10/11/2015
Returns the current date
=TODAY() + 10
Returns the date ten days into the future
=TODAY() – 5
Returns the date five days ago
The TODAY() function is recalculated every time you open the worksheet or refresh it. In other words, if you enter the formula =TODAY() in a worksheet on May 01, 2016 and then reopen that same worksheet on June 30, 2016 the date will be updated to 06/30/2016. You can prevent this dynamic updating by making the date static. To make the date returned from the TODAY() function static
1. Double-click the cell that contains the Today() function. This places the cell in edit mode.
2. Press the refresh key (F9). This changes the date to serial date. See the image below.
3. Press Enterto commit the entry. The serial number then reverts back to a date format. The date will then always show as the date you made the entry.
EXERCISE: Using Today Function With INT Function
In this exercise, you will use the Excel TODAY function and a person’s birthdate to determine his or her age.
1. Enter the data as shown in the image below in a new worksheet. Name the worksheet “Age”. We want to determine the age for those whose birthdate appears in column B. Add your name and birthdate to the list.
2. Click inside cell C2. The formula for determining the age is today’s date minus the birthdate divided by 365.25. We divide by 365.25 to accommodate leap year which happens every four years.
We place a pair of parentheses around TODAY()-B2 because we need to perform this calculation before doing the division. If we didn’t place it in parentheses Excel would first divide B2 by 365.25.
4. Press Enter. The result is a decimal amount. We usually display someone’s age as an integer.
We will use the INT function to remove the decimal portion of the age. The INT function rounds a value down to its nearest whole number.
5. Double-Click cell C2.
6. Place the cursor after the equal sign in the Formula bar. Type INT(.
7. Press the End keyon the keyboard to place the cursor at the end of the function.
8. Type the closing right parenthesis. Your formula should now be
9. Press Ctrl + Enter.
10. Drag the AutoFill Handle down through cell C5. If this was run in February 2016 the results would be as shown in the image below.
In our previous tutorial, you were learned how to create Excel nested function withing OR function. Now in this tutorial you should learn how to create Excel nested Or function within IF function. So Let’s create another IF function, but this time let’s nest an OR function within it that will test if the student is a female or if the student is 21 or more. If a student meets either one of these conditions, he or she will be placed in Section B.
1. In the cell, E2 enter the formula =IF(OR (.
2. Press F3 to bring up the range names. Select Sex and then click the OK button.
3. Type Sex = “F”, .
4. Press F3 to bring up the range names. Select Age and then click the OK button.
5. Type Age >= 21), . The function is now =IF(OR(Sex=”F”,Age>=21), .
The OR function is finished and now you are back to the IF function and ready to enter the data for the argument [value_if_true]
6. Enter “B”,””).
The full formula is = IF(OR(Sex=”F”,Age>=21),”B”,””).
7. As with the nested AND function, clicking the word IF in the formula and then clicking the Insert Function or clicking the word OR in the formula will display that portion of the formula in the Function Arguments window.
8. Use the Autofill Handle to copy the formula from E2 to E11. Select the cell range B2:E11. Center the data of the selected range. Figure 8-43 shows the results.
Let’s make a change to the application by placing a checkmark in column D if the student meets the conditions of being a Male, a History major, and under 21. The check mark is part of the Wingding font Set. A check mark is created when you hold your Alt key and then on the numeric keypad type 0252 (it only works if you use the numeric keypad). The Wingding font must then be selected for the cell.
9. Delete column E.
10. Change the text in cell D1 to Section A.
11. Click cell D2. In the Formula bar select A in the true part of the IF formula.
12. Hold down the Alt key and type 0252 on the numeric keypad. The formula should be IF(AND(Subject=”Math”,Sex=”M”,Age<21),”ü”,””)
13. Select the Autofill handle on cell D2 and drag it down through cell D11.
14. With the range D2:D11 selected, change the Font typeface to Wingdings. The image below shows the result.
You created nested AND and OR functions. You can nest about any function within another. This gives your functions a lot more flexibility.
The Excel AND function return either a TRUE or a FALSE. It returns a TRUE if all of its arguments are true and it returns a FALSE if any of its arguments are not true. The syntax for the AND function is:
AND(logical1, [logical2], . . .)
The Excel AND function can be used to evaluate up to 255 logical conditions. The image below shows the AND function with three logical conditions. All three of these conditions are true therefore the function returns a TRUE.
The last condition in the image below is false. The value in cell D1 is not equal to the value in cell F1. The Excel AND function, therefore, return a FALSE because even if only one of the logical conditions is false it will return a FALSE.
Excel OR Function
The Excel OR function returns a TRUE if any of its arguments are true and it returns a FALSE if all of its arguments are false.
The syntax for the Excel OR function is:
OR(logical1,[logical2],. . .)
The image below shows the OR function with three logical conditions. All three of these conditions are false. If and only if all of the arguments are false will the OR function return a FALSE.
The first logical condition in the image below is true. The value in cell D1 is less than the value in cell E1. The other conditions are false. The OR function returns a TRUE because even if only one logical condition is true the function returns a TRUE.
Excel Nested Functions
Sometimes a single function will not give you the results you need. In these cases, you will need to create a nested function. Excel Nested functions combine the operations of two or more functions into a single function. A nested function is used as an argument in the other function. Excel allows a maximum of 64 functions nested within each other. That should be more than you will ever need.
EXERCISE: Creating a Nested Function
In this exercise, you create functions nesting different operators.
Nesting an AND function Within an IF Function:
1. Enter the data as shown in the image below, into a worksheet. Name the worksheet “Nested”
Students who are in Math, are males, and are less than 21 years old will be placed in Section A. This problem will require an AND function nested within an IF function. The AND function will be the condition to be tested. If the condition is true we will display an A in the cell. If the condition is false we will display a blank.
2. Select the cell range A1:C11. On the Ribbon’s Formula tab in the Defined Names, group click Create from Selection. In the Create Names from Selection window, only Top row is to be selected. Click the OK button.
3. Click the down arrow of the name box to see your named ranges.
4. Click inside cell D2. Start the formula by entering =IF(AND( The Excel tooltip shows the needed arguments for the AND function. See Image below.
5. We will now enter the first of the three conditions we will be testing for.
a. Press F3 to bring up the range names. Select Subject and then click the OK button.
b. Enter =”Math” followed by a comma. Entering the comma highlights the second argument [logical2]. See the image below.
6. Press F3 to bring up the range names. Select Sex and then click the OK button.
7. Type =”M”, . The formula should now be:
8. Press F3 to bring up the range names. Select Age and then click the OK button.
9. Enter <21), The formula should now be:
The AND function is finished and now you are back to the IF function and ready to enter the data for the argument [value_if_true]. See image below.
10. Enter “A“, for the true argument. Enter “”) for the false argument. This will display a blank. The complete formula is:
If you didn’t enter “” for the false part Excel would by default display the word False.
11. If you wanted to see or make a change to your formula you could click the word IF and then click the Insert Function button (Formula tab | Function Library).
You can see that the AND condition is false; therefore our result is a blank. See image below.
12. Click the Cancel button.
13. If you just wanted to see or make a change to the AND function you could click the word AND and then click the Ribbon’s Formula tab. In the Functions, Library group click the Insert Function button. See image below. You can see that the Age is not less than 21 for the first cell in the range.
14. Click the Cancel button.
15. Press Ctrl + Enterto accept the formula. Cell D2 is blank.
16. Use the Autofill Handle to copy the formula from D2 to D11.
[value_if_true] if the condition being tested is true, then perform this step.
[value_if_false] if the condition being tested is false, then perform this step.
If the condition being tested is true, what is in the value_if_true argument will be used; otherwise what is in the value_if_false argument will be used. Let’s say that the value in cell A2 is 20. If you place the following formula in cell A1.
=IF(A2=20,”Equal 20″,”Not equal 20“)
then cell A1 will display Equal 20. If you change the value in cell A2 to 19, then the value in cell A1 will change to Not equal 20.
EXERCISE: Using The Excel IF Function
We need to determine the Gross Pay for each of our employees. If an employee works over 40 hours he will receive overtime pay at 1.5 times the normal rate. The Gross Pay will be computed by adding an employee’s regular pay and overtime pay. An IF function will be used to compute the employee’s regular pay; another IF function will be used to compute his or her overtime pay. If an employee works 40 hours or less, his regular pay and his gross pay would be the hours he worked times his pay rate. If an employee works 40 hours or more, then his regular pay will be 40 hours times the pay rate. The hours the employee worked in addition to the 40 hours will be used for determining overtime pay. If we assign names to our ranges, the formula needed to compute regular pay is:
=IF(hours>40, 40*rate, hours*rate).
We take 40 * rate if the condition (hours >40) is true and hours * rate if the condition is false.
To compute the OT (overtime pay) we will again need to test if the hours worked is greater than 40. If the hours are greater than 40 we will need to take the number of hours worked minus 40 to get the overtime hours. The overtime hours will be multiplied by the pay rate times 1.5. If the hours worked are 40 or less, then the overtime will be 0.
The figure below shows the formula to compute the overtime pay.
1. Enter the data as shown in the image below in a new worksheet. Name the worksheet “Payroll.”
2. Select the range B1:E8, as shown in the image below.
3. On the Ribbon click the Formula tab. In the Defined Names, group clicks the Create from Selection button. Select Top row from the Create Names from Selection dialog box. See Image below.
4. Type this formula in cell D2: =IF(hours >=40,40*rate,hours*rate). Press the Tab key.
5.Type this formula in cell E2: =IF(hours > 40,(hours – 40)*rate* 1.5,0). Press the Tab key.
6. Type this formula in cell F2: =Regular_Pay+OT. Press the Enter key.
7.Select the range D2:F2. Drag the AutoFill Handle down through row 8. The image below shows the result.
8.We need to change the Regular Pay, OT, and Gross Pay columns so that they display with two decimal positions. We also want to show dashes instead of zeroes for the overtime pay. Formatting the cells as the Accounting type will do this for us.
a. Select cells D2:F8.
b. Right-click the cells and then select Format Cells.
c. Select the Number tab.
d. Select Accounting for the Category. Click the down arrow for the Symbol and then select None.
e. Click the OK button. The Image below shows the results.
You have used the IF statement to test a condition; the function does one thing if the condition is true and something else if the condition is false. Next, you will look at the AND function and later the OR function. These functions don’t do much on their own. They only return either a TRUE or FALSE. Their strength is shown when they are combined with other functions. The exercise following the OR function demonstrates how to combine these functions within an Excel IF function.
The Excel SUMIFS function is similar to the SUMIF function except that the SUMIFS function is used to sum values only if multiple conditions are met rather than a single condition. The syntax for the SUMIFS function is:
sum_range is the cells that you are basing your criteria on. The range contains the values that will be checked by the criteria to determine if they are to be included in the sum.
criteria_range1 is the first range in which to evaluate the associated criteria.
criteria1 is the condition that defines which cells in the criteria_range1 argument will be added.
Criteria_range2, Criteria2,. . . You can add as many as 126 additional ranges and their associated criteria.
The image below shows a company’s bank deposits along with the date of each deposit for its different branches. We want to sum the bank deposits in column B but only if the following conditions are met:
• It is not a Hobart office.
• The amount of the deposit has to be at least 20,000.
•The date of the deposit has to be 03/12/2010 or later.
The formula that will add the deposits that meet our criteria is:
The arguments used in the formula for summing the bank deposits are as follows:
B2:B11 is the sum_range. This range contains the values we want to sum if they meet our criteria
We only want the branches that are not Hobart.
•A2:A11 is the criteria_range1.
•“<>Hobart” is criteria1.
The amount of the deposit has to be at least 20,000.
•B2:B11 is the criteria_range2.
•“>=20000” is criteria2.
The date of the deposit has been 3/12/2010 or later.
•C2:C11 is the criteria_range3.
•“>=03/12/2010” is criteria3.
The image below highlights the rows that meet all the criteria. They are the only deposits that meet all three criteria of (1) not being a Hobart branch, (2) having a deposit greater than or equal to 20000, and (3) having been deposited on or after 3/12/2010. The result of adding the deposit for these three rows is 83562. 79
Using a Cell Address Rather Than a Cell Value for a Criteria
If you are checking if an item is equal to a value in the criteria range you can specify a cell address that contains the value of that item for the criteria. In the image below, we want to sum the attendees if they took an Excel class in Fort Wayne. Cells E2 and F2 can be used for the criteria because they contain the values to be checked against the criteria range.
The image below shows the formula entered in cell G2.
Handling Empty Cells in SUMIFS Functions
The worksheet in the image below totals the values for items that have been completed but haven’t yet shipped. The two criteria that will be used for the SUMIFS arguments will be those items whose Date Completed is not blank and whose Date Shipped is blank. The figure highlights those items that meet both criteria.
The criteria are as follows:
⇒ The criteria for the Date Completed cells is “<>”
⇒ The criteria for the Date Shipped is “” (two quotes together test for a blank)
The formula entered in cell E11 is =SUMIFS(E2:E10,C2:C10,”<>”,D2:D10,””).
The SUMIF Excel function is used for summing a range of values provided that they meet a condition that you specify in the criteria argument. The SUMIF function only works if you are basing your summing on a single criterion. If you need to base it on more than one criterion you will need to use the SUMIFS function, which is covered in the next section. The syntax for the SUMIF Excel function is:
SUMIF(range, criteria, [sum_range])
Argument descriptions are: a range is the cells that you are basing your criterion on. The range contains the values that will be checked by the criteria to determine if they are to be included in the sum.
criteria is the condition that must be met if a value is to be included in the sum. If the condition contains any text, logical or mathematical symbols it must be enclosed in double quotation marks. Quotation marks are not necessary if the criterion is just a number.
[sum_range]is optional. If this optional range is used then it will be this range that contains the cells to be summed rather than the range in the first argument. The criteria will still be based on the first range argument.
Using The SUMIF Excel Function
The Image shown Below contains a range of values that need to be summed, but we only want to sum those items whose value is greater than 900. There are only two values greater than 900 in the range B1:B6: 1100 and 1500.
The arguments used in the formula for summing the values greater than 900 are:
B1:B6 is the range of values that the criteria will be based on.
“>900” is the criteria. Since the criterion contains something other than just a number it must be enclosed in double quotes.
The complete formula is;
We will place this formula in cell B7. The result of adding 1100 + 1500 is 2600. (See the image below).
The Image below shows a list of items and their prices. We only want to sum those items whose price is exactly 99 cents. The formula entered into cell B10 is;
Since we are only summing those values which are exactly .99, there is no need for any additional logical symbols because the equal sign is optional. Because the criterion is only a number, it does not need to be placed within double quotes. (See the image below)
When using the optional sum_range argument you base your criteria on a different range of cells than those that you are summing.
We want to find the total number of males and females in high school. The image below shows the worksheet for this. The worksheet uses separate formulas for determining the number of males and females.
The arguments used in the formula for determining the number of males are:
B2:B9 is the range that the criteria will be based on.
““M” is the criterion
C2:C9 is the sum_range. The range of the cells that will be summed, if the criterion is met. (See the image below)
The formula entered in cell C11 is:
The only difference in the formula for computing females is the criterion which is “F“. The formula entered in cell C12 is:
There are three Excel Sum functions for summing cell values. The table given below describes them.
Sums a series of values without testing for any conditions.
Used to sum values provided that they meet a condition that you specify.
Used to sum values only if multiple conditions are met.
Working With Excel SUM Function
We will look at how an Excel SUM function is constructed. The SUM function is one of the Math & Trig functions. The Excel SUM function, used for adding a group of values, is the most used function. The SUM functions should be familiar to you because when you use AutoSum you are running the SUM function.
The Syntax for the Sum function is:
=SUM(number1, [number2], [number3]. . .)
Function names are written in all capital letters; however, they are not case sensitive. You could enter the function name in small letters. If you enter a function in small letters Excel will change it to all caps. Function names are followed by a set of parenthesis. If the function requires arguments, they are placed within the parenthesis. The SUM function has the arguments number1, number2, and number3. The arguments number2 and number3 are placed within square brackets. Arguments placed within square brackets are optional. The three dots (ellipsis) after the last argument mean that this is not the end of the list. You could actually place any number of arguments within the parenthesis of the SUM function.
Excel is very helpful when entering a function. Entering a function in a cell starts by entering a = sign followed by the name of the function. As you type the letters of the function, Excel narrows down the function choices based on the letters you have entered so far. (See Image below).
Clicking a function name brings up a description of what that function does. After you have entered enough characters to narrow down the function to the one you want, press the Tab key. Excel will place a left parenthesis after the function name and it will bring up a tooltip to help you with entering the function. The first argument is highlighted in the tooltip. (See Image below).
After entering the first argument and a comma ( 23, in this example), the second argument becomes highlighted in the tooltip and a third argument option is displayed. (See Image below).
Every time you enter another argument, an additional argument appears in the tooltip. (See Image below).
If you need to change one of the arguments you entered, click the argument in the tooltip. This will select the argument in the function and then you can make your changes. (See Image below).
You do not need to enter a comma after the last argument. It also is not necessary to enter the closing parenthesis. When you accept the entry Excel will automatically enter the closing parenthesis for you. The result returned by the function will display in the cell where you entered the function. The complete function will display in the function box. (See Image below).
Single values entered in a formula are called constants. A constant is a value that does not change. The entry for an argument doesn’t have to be individual values like those we have entered in the SUM function. The SUM function can also use range names, individual cell references, and cell ranges for its arguments. What can be entered for each argument depends upon the function used. The table given below shows several examples of the SUM function with different arguments.
Adds the individual values 3, 5 and 8 giving a result of 16
Adds the values in the range named Sales
Adds the values in the range B3 to B9
If you wanted to sum the cells in the range C5 to C10 on a different worksheet named “Purchases,” you would enter the formula as shown in the image below.
You could type Purchases! or you could click the worksheet named Purchases and Excel would enter the name along with the exclamation point in the formula for you. If you only wanted to add the values in column C, then you could use the shortcut method C:C. The formula could be written as = SUM ( Purchases ! C:C ).
EXERCISE: USING THE SUM FUNCTION
We will add three columns of numbers using the SUM function.
1. Create a new workbook. Enter the data into a worksheet as shown in the image below. Name the worksheet “Sum.”
2. Click inside cell B8. On the Formulas tab in the Function Library group click the Insert Function button.
3. Enter add numbers in the Search for a function box. Press Enter or click the Go button. Click SUM in the Select a function list box. Click the OK button. (See Image below).
4. The Function Arguments dialog box opens. Move the dialog box if it is covering the numbers in your worksheet. An entry box appears for each required argument. Click inside the Number1 box. Required arguments are bolded. Number1 is the only required argument for the Sum function. (See Image below).
Argument Number1 contains the range B3: B7because it is attempting to add the values above it. The values being summed are displayed to the right of the Number1 box. It includes the 0 for cell B7. This doesn’t affect the current total, which as you can see is 1950.
5. Drag across cells A3: A6. The values from this range are displayed as well as the current sum. (See Image below).
6. The Collapse Dialog buttons can be used to collapse the window when it is blocking the data you need to use. Click inside the Number2 box. Click the button to the right of the Number2 box. The window collapses to a single box for the current argument. Drag across cells C3:C6. See Image below. When you have finished your selection click the Expand Dialog button to expand the window back. (See Image below)
7. The Number3 argument has been added to the window. Click inside the Number3 box. The Number4 argument has been added. Drag across cells E3:E6. The image below displays the result. Click the OKbutton.
The result is placed in cell B8.
Using the same steps see if you can get the answer for cell B9.
Sign up for new Howexcel Latest Post & Training,
Subscribe To Our Newsletter
Join our mailing list to receive the latest Tutorials From Howexcel.