Mastering Excel 2019: Perform Operations Using Formulas and Functions

hello YouTube I hope you're in a fantastic day in today's video I want to talk about the Excel 2019 exam and I

want to look at the domain called perform operations by using formulas and functions overall this accommodates for

20 to 25 percent of the overall exam I'll go ahead and throw up a graphic so you can see what this domain covers

I apologize for this video getting long but I wanted to make sure that I covered this domain carefully and clearly as we

went through each of the formulas and functions found in this domain in today's video we're gonna look at insert

references calculate and transform data and format and modify text this is the domain that causes people

the most heartache and grief I want to give you some advice the first thing is take a deep breath the worst thing that

you could do on this exam is write off all the formulas and functions just say I'm not going to do that and I've had

people do that and it really hurts them on this exam you don't have to get all the formulas and functions right just

most of them the second thing that I want to encourage you to do is to take an inventory of all the formulas of

functions that are found on this exam and not only know what they are but what they carry out on the exam that way when

you're taking the exam you know in the back of your mind you have a drop-down list of the formulas and functions you

could be asked to carry out and you don't want to choose anything outside of that area because the test tells you

specifically the functions of formulas that you could be asked to carry out so don't panic and make sure you have that

list let's go ahead and jump into Excel [Music] we're talking about the Excel 2019 exam

and we're looking at the domain called perform operations by using formulas and functions we're looking at subdomain

insert references the first thing that this subdomain tells us that we need to be able to do is to insert relative

absolute and mixed references it's going to look at using a relative reference I'm gonna put my cursor in j5 I'm gonna

go to the insert function button and then to select some you could have manually typed this out but I prefer on

exams like this to use the function builder when I can because it gives me helps at the bottom the sum function is

fairly simple so I won't need the helps but it can definitely help you out if you're not sure what to do my number one

is gonna be e5 to i-5 and then I'm gonna click OK now that I've put that in I'm gonna go ahead and use the autofill or

the drag fill to copy that formula down and I'm gonna put my cursor back in j5 so that we can look at that reference

notice that it says some 'if I've to I 5 but watch what happens when I click on j6 notice that the reference updated to

e6 to i6 same thing when I go to j7 the reference is e 7 to I 7 when I copy that formula down Excel was able to just move

my reference down 1 because it was a relative reference most of the formulas that use in Excel are probably relative

reference let's go and look at using an absolute reference I'm gonna put my cursor in a 11 and I'm gonna do the

equal sign and I'm gonna select on a 5 and I'm gonna hit enter notice that Hollies name appears this cell just

directly links a 5 and if I click and drag this down using the autofill there drag fill notice it just continues

copying the next line down but let's say I don't want that I'm gonna put my cursor back in a 11 I'm gonna go to the

formula bar I'm gonna select my reference and what I'm gonna do is hit the f4 key on my keyboard and notice

that dollar signs were placed in front of the column and the row this makes this an absolute reference

watch what happens when I hit enter nothing out of the ordinary happens it just brings back in Holly's name but

when I try to use drag fil this time notice it's always Holly and if I try and drag right it's always Holly and if

I click in any one of these cells the reference is a five that's because this is absolute it's locked it's not gonna

change because we put dollar signs in front of the column and the row the last point of this is that we need to be able

to use mixed referencing I'm gonna go ahead and do a quick sum of the numbers that I just added we have a total of 134

hours worked I want to find out what percentage of each employee represents that hundred and thirty-four hours so

I'm gonna put my cursor in k5 I'm gonna do equals j5 I'm gonna do / and I'm going to select my

j10 cell I'm gonna go to my reference at the top with J 10 selected which is our cell that we're not gonna want moved

around we don't want this cell to slide as we drag fill our reference I'm gonna hit the f4 key hitting f4 once makes

this a full absolute reference but if I can't f4 again notice that the dollar sign was removed from the J column what

that means is the GA can change so if I drag my reference right it'll go from J to K - L show you what I mean hit enter

and if I click and drag to the right notice I'm getting an error message and that's because it can't divide by zero

but notice my reference here is j5 but when I click on L it's now showing K it's referencing this self I click here

we can see it's looking to those I'm gonna go ahead and undo what I've done if I drag down though I still get the

same answers because J is not moving when we move down let me just show you one other thing and this so mark this

wrong but if I hit the f4 button again it changes to just the column being locked and when I hit enter notice I get

an error message and that's because it's changing to j xi j 12 and you can't divide by that this part of the sub

domain can be difficult it's important that you play around with your different referencing options for this the next

thing that this subdomain says that we should be able to do is to reference name ranges and name tables in formulas

if I click my name box you can see that I have a named range named Monday and we're gonna use that what we're about to

do I'm gonna put my cursor in eat n I'm gonna do equals sum and I type in Monday I'm going to divide by the number 5

because I have 5 employees and I'm gonna hit enter and notice that it tells me that the average for my employees is 4.2

hours worked this subdomain while it might not seem like such a difficult section of this domain can be quite

difficult if you're not familiar with working with relative absolute mixed referencing but also if you're not used

to using named ranges in your functions and I want to encourage you to practice manually hankie and in different types

of formulas and functions because oftentimes people don't use it as much as they use the built-in functions and

on something like the certification exam while the task is not difficult to carry out they might stumble or waste a lot of

time on the exam because they're just not used to handing in all the parts of the function so what encourage you to

practice on this we're looking at the second subdomain calculate and transform data there are eight different functions

within this section the most difficult of course being the if function will say that for last

this subdomain tells us that we should be able to perform calculations by using the average max min in some functions

the first one we'll look at is using the average I'm gonna put my cursor here in b12

these are simple functions and you're probably familiar hand keying in this function when you can I want to

encourage you use the function builder and that's what we'll do today just because it gives you some helps down

here at the bottom as well as walks you through the function and we'll see that more as some of the more complex

formulas like the if function the average function returns the average arithmetic mean of its argument which

can be numbers or names arrays or references that contain numbers with average selected we'll click OK and our

number one will go ahead and just select the entire range of hours want to be careful on something like this and on

the certification exam that you don't select the total row because that's gonna skew your data and on the

certification exam you're probably gonna get that task wrong so just my our selected for all the employees I'll

click OK when it comes back as the average being 7.4 for the next function that I want to

look at is called max will click insert function I'll go ahead and type in max the max function returns the largest

value and a set of values it ignores logical values and texts will click OK our number one range we'll go ahead and

just select all of our hours again and what this function is going to do is return back the largest number we'll

click ok the largest number in that range is 10 but notice if I click here on Ryan Kaiser and I type in 11 that

function changes as well as my average function the opposite of the max function is the min function so we'll

put our cursor in B 14 I'll click insert function will type in min the min function returns the smallest number and

a set of values it ignores logical values and texts we'll click OK and we'll select our range again for hours

we'll click OK the smallest number returned is 0 then the final function that we need to look at is called sum

with our cursor and B 15 I'll click insert function I'll type in sum the sum function adds all the numbers in a range

of cells so we'll click OK and we'll select our range we'll click ok the total numbers work by

all employees that week is 135 hours the second point of this subdomain says that we should be able to count cells by

using the count count a and count blank functions I'm going to put my cursor in E 12 I'll go to insert function will

type in cow the count function counts the number of cells in a range that contains numbers

that's important contains numbers so we'll click OK and for this I'm only going to select them

one day range and click OK and notice that it returns back that value of 3 that's because we have two empty cells

here I'm going to do this function again but this time notice what happens when I choose the a column

notice that I get a zero and that's because this function is only looking for numbers it doesn't look at texts

let's look at the count a function will click insert function count eight counts the number of cells

in a range that are not empty so we'll click OK for this will select Tuesday's numbers and we'll click OK it returns

back the value of four of course we have Ryan not working on Tuesday so out of the five available cells only four of

them had numbers in it let's try doing this again but with the name column again

notice this time that even though the range that we selected was text it still brought back that there were five names

in that range let's look at using account blank so we'll put our cursor in E 14 click insert function will type in

count blank the count blank function counts the number of empty cells and a specified range of cells and we'll click

OK for this will select the Thursday through Friday range and we'll click OK and notice that it found three empty

cells here let's go ahead and delete out Susan's last name and we'll do this function

again and this time we'll do that last name column and it returned the value of one there was one empty cell so keep in

mind when you're working with the count functions for this exam the count function specifically is looking at

numbers but with count a and count blink you can use the function to look for texts the last point in this subdomain

says that we should be able to perform conditional operations by using the if function the if function can be

intimidating if you've never used it before we have limited usage but let's go ahead and look at using that today

I'm gonna put my cursor in K 5 will click insert function we'll type in if the if function checks whether a

condition is met and returns one value if true and another value if false we'll click OK

so what we're gonna do is we're gonna look to see if the total hours worked is greater than 30 if it is we're gonna

just leave the cell blank if it's false we're gonna put in increase hours I have my cursor and the logical test Excel is

telling you that the logical test is any value or expression that can be evaluated to true or false our logical

test for this is gonna be to look at Holly's hours total hours worked so we'll click on j5 you could also type

that out what we're gonna do is is j5 greater than 30 notice that Excel is already telling me that this is true if

it were not true it would say false and if it's having a problem calculating what we're asking it to do you would get

an error message over here so pay attention to what's going on over here on the right I'm gonna hit tab on my

keyboard to go to the value if true so we'll leave that blank just to test this out and see what happens

my value if false is gonna be what's return if this argument is not true for this we said that we're gonna use the

text increase hours and I'm gonna hit tab and notice if you look very carefully quotation marks were added

around my text this is a benefit of using the function builder to build your functions had you left this out and you

are manually hanging this function in you're gonna come back with an error message so when you can I want to

encourage you to use the function builder so I just want to do a quick review of this function because this is

the one that gives people the most difficulties our logical test is telling Excel to look for something for this

it's looking to the total hours for Holly and saying is the hours worked greater than 30 you need to determine

Excel if that's true or false our value of true is what do you want Excel to do if this is true we left it blank for

this you could add text like we did for false or you can actually have a calculation if you chose to but we're

just gonna leave it blank for now and then the value of false what do you want Excel to do if this is not correct and

we put put the text increase hours so let's see what happens click OK notice that it put in 0 and if I click and drag

this down notice that some of them say increase hours and some of them say 0 let me undo what I did and let's go back

to this function and then click the insert function with my cursor in that cell and this time want to show you this

if I don't want any text if I put quotation mark quotation mark it's gonna leave that cell empty watch what happens

when I hit OK this time notice it's blank and now when I click and drag the cells where that criteria was true is

left blank in the cells that that was false return the value of increase hours just for the example of this video let

me undo what I did we'll go back to that cell will click insert function to bring back up this dialog box I want to show

you that you can do calculations for if true I'm gonna select J I what I'm gonna do is minus 5 this is just a manual

calculation but you can use other functions like some you could put another if function in that's called

nesting we're not gonna do that for this but just know that in addition to just this

simple calculation you can actually do more advanced calculations in the if function so we'll click OK notice this

time that it brought back 35 so it's all that it was true that they had worked more than 30 hours and it subtracted the

value that was in J five by five and if I click and drag that down notice it did the same for the other ones

we're looking at this sub-domain call format and modifying text the first thing this subdomain says that we should

be able to do is to format text by using the right left in mid functions I'm going to part my cursor in i-5 and what

we're going to do is we're going to look at the social number and we're gonna use the write function to pull data from

these cells I'm going to click on insert function the function that I want is called right

so we type that in the right function returns the specified number of characters from the end of the text

string so we'll click OK the text is the text string that contains the characters you want to extract for this we want to

choose e5 so we're telling Excel to look at E 5 and from the right side we need to tell excel in the number characters

how many characters we want to extract says 1 if a minute so for this what we're gonna do is we're gonna pull the

last four numbers in the social so we'll go ahead and type in 4 notice that it gives me what's going to populate here 6

7 8 9 is correct we'll click OK and if I click and drag that down because this is a relative reference we went ahead and

it did it for the remaining cells let's look at the left function which is the opposite of the right function we put

our cursor in J 5 we'll go to insert function type in left left returns the specified number of characters from the

start of a text string our text for this is going to be e 5 we'll hit tab and the number of characters are what we want to

extract we'll do the first 3 for this one so we'll type in 3 and notice that shows that it's going to produce 1 2 3

which is what we want and again because this is a relative reference if we click and drag that down it's able to do the

same thing for the other employees now let's look at the mid because it's just a little bit more complicated than what

we've looked at with my cursor and kaif I will click insert function I'll type in mid the mid function returns two

characters for the middle of a text string giving a starting position in length we'll click OK our text for this

is going to be our social again and this time what we're going to do is extract the middle two numbers our start number

is the position of the first character you want to extract the first character and the text is 1 so for this all I want

to pull is the full 5 so let's count 1 2 3 4 the fifth spot is where we want to extract from so we're gonna go ahead and

type in 5 I'll hit tab to go to the number of characters and we're gonna tell Excel that we just want to pull

those two numbers so we'll click - and it tells me that 45 is gonna get pulled that's

exactly what I want so we'll click OK and we'll drag this down and notice it's able to pull all of those this subdomain

says that we should be able to format text by using upper lower and Lin functions so I'm going to put my cursor

in in high and if we look at the B column for the last name so we can see that some of the spelling is all over

the place so we're going to use the upper function to change the text there with my cursor and m5 I'm going to click

insert function the first function that I want is upper the upper function converts a texturing to all uppercase

letters this function is fairly simple all we need to do is select the cell that we want to have the text all

uppercase for this is going to be b5 and it shows me it's gonna be all caps Feltner that's exactly what we want

we'll click OK and notice Feltner went from all lowercase to all uppercase and if I drag this down notice that Kaiser

was spelled a little bit weird and that's all uppercase now as well as the other names that were spelling the

proper case let's look at the lower function with our cursor in 5 I'll click insert function I'll type in lower look

like okay it's asking for the text that we want to select again we're gonna choose b5 and nothing's gonna change

because the name Felder was already typed in lower case but we'll see what happens to the other names after click

OK we drag it down notice it lowercased every single text spelling from the B

column let's go to look at the lin function which is a little bit different we'll put our cursor an oaf i will click

insert function type in lin the lin function returns the number of characters in the text string okay so

for this all we want to do is select the cell again but notice what it's looking for in this text is the text whose

length you want to find spaces count as characters so that's important in those spaces count as characters for this

we'll select B 5 Google click OK and if I click and drag that down notice that when I had any counted each

character in that cell I'm gonna go ahead and put my cursor in front of Feltner and I'm gonna hit space and then

hit enter to set that text and notice that all of the functions they are updated but the main thing I wanted you

to see is that the lin number went from 6 to 7 and it's because it's counting that space in front of film the last

thing that this subdomain tells us that we should be able to do is to format text using the concatenate functions in

this group these are definitely the two functions that are most difficult to perform let's look at using context

I'm going to put my cursor in c5 and what we're going to do is use the concatenate to gather in one cell so

with my cursor and c5 I'm going to click insert function will type in concur now if you look here can cat and

concatenate are listed that can cat function replace concatenate so you want to make sure you select the right one

might be familiar with using concatenate but for this exam you want to use King cat that can cat function concatenates

and list or range of text drinks so we'll click OK our text 1 is gonna be the first thing here and that's gonna be

hot I'll hit tab on my keyboard and what I want to do is have a space between the first name and the last name

so I'm just gonna go ahead and hit the spacebar on my keyboard and I'm going to hit tab now if you look carefully the

text line to notice that I put quotation marks all around that space that's a benefit of using the function builder to

build your functions had you been manually typing this out and you forgot those quotation marks you're going to

come back with an anonymous and my text 3 is going to be b5 and we can see here is gonna say Holley space

Feltner will click ok because this section has a table attached to it went ahead and filled that down for me if I

go back to the last name here and I delete the space before Feltner notice that they can cat function

populate as well as the functions over here now I want to look at the cat in a different way let me go ahead and and

add a couple of columns here put my cursor in each v and what I'm going to do is I'm going to make a sentence using

the employee and the number of hours work so let's go back to the insert function I'm gonna select can can and

this is what I want my sentence to say Holly Feltner worked 40 hours this week what I'm gonna do is populate parts of

the sentence with information from this table so the first thing that I want to select is c5 I'll hit space on my

keyboard I'm gonna hit the space key on my keyboard and type in worked I don't it's just gonna be a bunch of text

squished together I'll hit space again hit tab now I'm gonna select g5 so that 40 displays I'll

hit tab I'll hit space again and I'm gonna go ahead and type in hours this week I'll hit period so that becomes a

sentence and this is what's gonna produce Holly Feltner work 40 hours this week watch what happens when we click OK

Excel went ahead and predicted that I was going to carry this through and added it to the table

and now we have a sentence for each employee with their name and how many hours they worked this week

the last function that we want to look at here is text Julie right here I have a few addresses and what I want to do is

combine this all into one line separated by commas and we can use the text joint function to do this for us

I'm gonna put my cursor in 13 I'm going to click on insert function I'll type in text join

the textual function concatenates a list or range of text strings using a delimiter so we'll click OK for this our

delimiter is character or string to insert between each text item so what do we want to put between each cell I'm

going to add a comma space I'll hit tab notice that the function builder put quotation marks around that that's

important the next part of this is ignore empty we're either gonna put true or false it says if we put true it

ignores empty cells for this we'll go ahead and type in true even though we do not have any empty cells and then it

says our text 1 is going to be a string of text that we want to bring together for this it's gonna be the range a 13 to

D 13 and it will click OK let's see what happens notice that went ahead and it put all four of those

together and added a comma between each one of those cells let's go ahead and drag that down

and went ahead and populated that now what I'm going to do is I'm actually going to delete the Miami Garden City

let's see what happens notice that Excel went ahead and updated that reference it did not add a space

comma twice you just ignore that cell I'm gonna go back to the cell and for this instead of having true I'm actually

them delete room on a pudding notice that when I added false that added a space comma and that's because

it's no longer ignoring that empty cell or this subdomain that can cap function and the textual functions are definitely

the most difficult for people to understand practice is going to be key for this to make sure you put some time

into practicing using these functions [Music] [Music]

[Music]