Functions (continued)

We continue our look at functions with nesting.

Nested Functions

One function can be used inside another. This is called nesting.

For example, in the last assignment we used NOT to reverse the values in the adjacent column.
We could have, instead, nested the OR function inside the NOT function and completed the
reversal in the same column F by using ‘=NOT(OR(B2="Liz", C2="Food"))’. Let’s work
through an example – how do teachers calculate grades?

1. Open the grades.xlsx file, found in Canvas.

2. In column C, let’s calculate what the letter grade would be based on the numeric grade
in column B, using an A-F scale, without plus or minus. (90-100=A, 80-89.99=B, 70-
79.99=C, 65-69.99=D and below 65=F).
3. Start with the first IF by typing ‘=IF(“ in cell C2.
4. You’ll see that the formula tells you what category to add next – for the logical_test add
‘B2 > 89.99’ and add a comma.
5. The value_if_true should be “A” and then add another comma (make sure to leave the
quotes on the A).
6. Now, before we add the value_if_false, we’ll add the next IF function, for B grades.
7. With A and B grades, the formula should read: =IF(B2>89.99, “A”,IF(B2>79.99, “B”,
8. Continue completing the formula for C and D grades.
9. The very last IF statement (for D grades) is the only one that gets a ‘value_if_false’ and
it should be “F”.
10. End with the correct number of closing parentheses (they are color coded and black will
be the last color) and press [Enter].
11. Use the autofill to enter grades for all of column C.

Click on the File tab, then choose Save As. Save the file to your computer as “Assignment
8.[Your Last Name].xlsx”, but do not upload it yet!

FYI - You can't always nest one function inside another - for example, you can't use the logical
functions in SUMIF() or COUNTIF().
In the assignment 7 file, you could not type ‘=countif(C2:C61,NOT("food"))’ for you would
get an incorrect result, but you could use the not equal (<>) mathematical operator; you could
type ‘=countif(C2:C61,<>("food"))’ and that would return a valid answer.
To learn more about nesting IF with Logical Functions, watch the first half of the
lecture video.
In cases where nesting is not allowed (or becomes too complex so that it's difficult to
understand what's going on), you may need to carry out the calculations in two stages.

Filtering and the SUBTOTAL Function

We learned how to get answers based on multiple conditions using SUMIFS and COUNTIFS.
Another way to get answers based on multiple conditions is to use filters and the SUBTOTAL
function.
1. Open the exceldata.xlsx file, found in Canvas.
2. Click on the Accounts tab.
3. Freeze the top row.
4. Click on cell A2.
5. From the Data tab, in the Sort & Filter area, choose Filter.
6. Using the pulldown in cell C1, make the only selected choice ‘Stationery’ and click [OK].
7. Click in cell D62.
8. Click on the [AutoSum] button in the Home tab.
9. Notice that it does not enter a SUM calculation, but a SUBTOTAL. Press [Enter] to
accept the SUBTOTAL function.
10. Using the pulldown in cell B1, make the only selected choice ‘Sarah’ and click [OK].
11. Open your Assignment 8.[Your Last Name].xlsx file that you saved earlier.
12. Going back to the exceldata.xlsx file, right click on the Accounts tab and choose ‘Move
or Copy’.
13. In the Move or Copy dialog box, use the pulldown at the top for “To Book:” and select
the Assignment 8 file from the list.
14. Make sure the ‘Create a Copy’ box is checked and highlight ‘(move to end)’.
15. Click [OK].

The SUBTOTAL function can also give you other measurements (e.g. AVERAGE):
In your exceldata.xlsx worksheet:
 With D62 the active cell, click on [Insert Function]
 You see the listed Function_num is 9. Click on the ‘Help on this function’ hyperlink.
 You'll discover that if the Function_num is 1 (or 101) then you get AVERAGE. A value
of 2 (or 102) is COUNT, 3 is COUNTA, 4 is MAX and 5 is MIN, while the remaining
values are for more complicated statistical functions. Click [Cancel].

Close your exceldata.xlsx file without saving any changes.

Save your file, but leave it open – we still haven’t finished with it yet!
Mathematical Functions

All the functions you met in your math and statistics classes are provided in Excel – SQRT(),
LOG(), EXP(), SIN() etc. - plus many more.

 ABS makes a number positive if it is negative

 INT rounds a figure down to the nearest whole number (ie removes decimal places) -
other similar functions are CEILING and FLOOR, which round up/down to a multiple
such as the nearest 10 or 100
 ROUND rounds a figure to a specified number of digits - also ROUNDUP,
ROUNDDOWN and TRUNC
 RAND gives a random value between 0 and 1 (also RANDBETWEEN for values
between other ranges)
 PI gives the value of pi (π)

1. Open a new, blank Excel workbook.

2. In A1 type =pi() then press [Enter] - since pi is a function it must have parentheses,
even though there is no argument
3. In cell A2 type =rand() then press [Enter]
4. In cell A3 type =rand()*100 then press [Enter] - this gives numbers between 0.0 and
100.0
5. In cell A4 type =int(rand()*100) then press [Enter] - this nested function produces
whole numbers between 0 and 99 as INT rounds down
6. In cell A5 type =randbetween(0,99) then press [Enter] - this gives numbers
equivalent to step 5 but is much easier to use

You may have noticed that each time you add a new calculation, the random numbers change.
The same happens if you press [F9] - try it, if you like.

This is because, as an Excel default, automatic calculation is in operation.

Note** You can change automatic to manual calculation in the Calculation Options area of the
Formulas settings in the Options menu from the File tab.

Date and Time Functions

Dates and times are stored simply as numbers in Excel. The date or time actually appears only
because a special display format is used.
We discussed dates and times, but here’s a reminder of a few examples:
 TODAY gives you the current date
 NOW gives you the current date and time
  YEAR, MONTH, DAY, HOUR, MINUTE and SECOND give you the current
equivalent values

7. Click in cell B1 and type =today() then press [Enter] - today's date appears.
8. In B2 type =now() then press [Enter] - you get both the date and time.
9. To get just the time, in B3 type =now() and press [Ctrl Enter] then click on the
[Number Format] button in the Number group on the Home tab and choose Time –
use the default type selection.
10. In cell B4 press [Ctrl-;].
11. In cell B5 press [Ctrl-:] (aka Ctrl-Shift-;).

Note** The easiest way to enter today's date into a cell is to press [Ctrl-;]. Similarly, [Ctrl-:]
gives you the current time. Note, however, that when you use these, the values are fixed and
are not recalculated when you open the file.

12. Move to B6 and type =year(now()) then press [Enter].

13. To find out how long you've lived, in B7 type =now()-"your_date_of_birth" - note
that your date of birth must be in quotes (e.g. “12/25/1990” ). Press [Ctrl Enter] then
click on the [Number Format] button and choose Number.
14. Move to B8 and enter a function to translate the figure in cell B7 from days into years.
(Hint: how many days in a year?)

Text Functions

There are also several useful functions for use with text:
 LEN counts the number of characters (including spaces) in the text
 FIND gives the position of the specified text in the text being searched
 LEFT, MID, RIGHT let you select part of the text from the left, middle or right
 LOWER, UPPER, PROPER change case (lowercase, uppercase, mixed case)
 EXACT compares the contents of two cells to see if they are exactly the same

15. In cell C1 type your name (eg John Smith) then press [Enter]
16. In C2 type =len(c1) then press [Enter] - this counts the letters (+ spaces) in your
name
17. In C3 type =find(" ",c1,1) then press [Enter] - this works out where the space is in
your name.
18. In C4 type =left(c1,c3-1) then press [Enter] - this gives you just your first name
19. In C5 type =right(c1,c2-c3) then press [Enter] - this gives you the rest of your
name
 20. In C6 type =upper(c1) then press [Enter] - your name is in capitals
21. In C7 type =proper(c6) then press [Enter] - your name is back to normal
22. In C8 type =exact(c7,c6) then press [Enter] - the cells do not match
23. In C9 type =exact(c7,c1) then press [Enter] - this time they do match

To learn how to convert date formats with text functions, watch the second
half of the lecture video.

The following two functions are useful for removing extra spaces from text:

 TRIM leaves just a single space between words

 SUBSTITUTE lets you replace one or more characters with others (or none)

24. In C10 type =substitute(c1," ","") then press [Enter] - your names merge, leaving
no space.

Sometimes you need to change text into numbers, and vice-versa. You may also need to be
able to specify a particular character by its number in the character set:

 CHAR gives you the character from the number specified

 CODE gives you the number from the character specified
 TEXT changes a number into text
 VALUE changes text into a number

25. In D1 type =code("a") then press [Enter] - the letter a is number 97 in the character
set
26. In D2 type =char(98) then press [Enter] - guess what's the letter following a 
27. In D3 type =32.5&char(176) then press [Enter] - a degree sign is added to the
figure (note that this has been stored not as a number but as text, hence it appears on
the left of the cell)
28. In D4 press [Alt =] for [AutoSum] and press [Enter] - the answer doesn't include the
value in D2 orD3
29. Replace the formula in D4 with =TEXT(CODE("a"),"#") and press [Enter]
30. In D5 type =SUM(D1:D4).
31. In cell D6 type =VALUE(D4) and press [Enter].

The TEXT function can be a very powerful function if you know how it works:

32. Move to cell B12 and type =TEXT( then press [Ctrl-A] or click on the [Insert
Function] button.
33. For the Value type now() then press [Tab] to move to Format_text
The Format_text argument is a set of characters which determines the output display for the
number generated by the TEXT function. This could be a number, time or date. The characters
used match those used in the Format Cells window. The # sign represents any number, without
specifying a format. A 0 is used where a number must be shown – so #.00 will display the
number to 2 decimal places (even when it's a whole number). In dates, d is used for days, m
for months and y for years. For times, h, m and s are used for hours, minutes and seconds.

34. For Format_text type "dddd" then press [Ctrl-Enter] - the result shows what day of
the week it is.
35. Copy cell B12 and paste in cell B13.
36. In cell B13, replace NOW() with your date of birth (in quotes, as before) and press
[Enter] to discover what day of the week you were born on.
37. Right click on the Sheet1 tab in your Book1 worksheet and choose ‘Move or Copy’.
38. In the Move or Copy dialog box, use the pulldown at the top for “To Book:” and select
your Assignment 8 file from the list.
39. Make sure the ‘Create a Copy’ box is checked and highlight ‘(move to end)’.
40. Click [OK].

Close your Book1 workbook without saving any changes.

Click on the File tab, save the file to your computer, then answer the functions B assessment
based on your file and/or comprehension of the lecture.