Perform Calculations and Operations

0:00Make sure that you've got this file open from the supplemental files so that

0:04you

0:04can follow along and taking a look at for example cell F10 here well I'm sure

0:10you already know how to do adding up. We did talk about this at some point

0:13before we can say cell reference plus cell reference plus cell reference but

0:17we also already know that if you want to add up lots of cells you may as well

0:21use

0:21the SUM function. So for the January figures here let's in the total cell in

0:27cell F3 let's use the SUM function that's exactly what I want it to add up

0:31good that'll do nicely and we also already know that we can use the fill

0:35handle to repeat the formula all the way down so yeah adding up and summing I

0:39think we've kind of already done. But taking a look at our January figures here

0:44we've got some figures for Manchester so sales figures and costs figures so

0:48therefore our profit is going to be our sales minus our costs so that's how we

0:54would do subtraction again I don't think that's too tricky no great

0:58surprises let's just repeat that all the way along for our other stores so yes

1:02that's looking good. But now let's imagine we're just estimating some figures

1:06for

1:06London so let's say we're estimating that the London figure is going to be

1:11double the Manchester figure so it's the Manchester figure multiplied by so the

1:16little asterisk multiplied by two so that's double and we're going to estimate

1:20that the costs for London are going to be half of the Paris figure so there's

1:25the

1:25Paris figure divided by two so that was just quickly some examples of

1:30subtracting multiplying and dividing making sure that you're happy with the

1:34symbols that we type you know the the front slash there for the divide the

1:38little asterisk there for the multiplication good. But when we've got a

1:41combination of different mathematical operators in the same calculation we

1:46have to be aware of the order in which Excel will calculate this. Now you might

1:51be already comfortable with this you know depending on what your knowledge of

1:54mathematics is already but it's something that we do need to be aware of so we

1:57're

1:57going to do an example so if you look at this January block of data you'll see

2:01that we need to work out what the profit share is and I've just added in a

2:04little

2:04comment there how we're going to work out the profit share so the profit share

2:08is calculated by dividing the profit plus a fixed bonus of 500 by 5. So let's

2:15do

2:15that without any real thought for the order in which that's going to be

2:20calculated so it's the profit plus 500 divided by 5. So that's what the note

2:27said that's how we calculate the profit share for January but when I enter that

2:32in you'll see well hang on a minute how can the profit share possibly be bigger

2:36than the profit itself and it's simply because when you have multiple operators

2:41Excel is going to work it out in a particular order it's going to do any

2:45timesing and dividing first and then any adding or subtracting whereas for this

2:53formula here to give us the right answer we need it to do that adding up first

2:57we want the profit plus the fixed bonus of 500 to be done first and then the

3:01result of that to be divided by 5. So to force Excel to perform calculations in

3:06a

3:06particular order we need to use these little parentheses or brackets as I would

3:11say so I want it to say well do the C5 plus 500 first and then divide what you

3:16get by 5 so if I press enter now good so that's now a smaller number than the

3:20actual profit that's what I was expecting for the profit share so just be

3:24aware of that and add in those parentheses where you need to to get the

3:28multiple operators calculated in the correct order but I'm pleased to report

3:33that the profit share calculation for February is much easier the profit share

3:37for February is simply 12% of the profit so again you might already be very

3:42comfortable with working out percentages you know I'm not trying to force you

3:45to

3:45do things my way but if you're not that comfortable with percentages the way I

3:49do it so if I want to work out what is 12% of that cell where well I type in

3:5412 and then the percent signs are 12% and when you're trying to speak

4:00calculations in natural language of usually relates to a times by so 12% of

4:06or times by that and that's going to give us 12% of that figure so I can just

4:11repeat that all the way along as I said if you've got another way that you

4:14would

4:14work that out then that's fine as well so that's all well and good that's how

4:18we

4:18work out as you saw here 12% of a figure or any percentage of a figure but what

4:23about now we're saying that for these March figures here we're expecting a

4:2810% increase on the February figures so for example if the Manchester sales

4:34figures we're expecting it to be 10% more than the Manchester sales figures for

4:39February well I don't just want to know the 10% bit I want to know the 10% in

4:44addition to the whole so to do that again there would be different ways of

4:49doing it but the way I think of it is it's 110% of whatever it is I want the

4:5510% increase on so 110% of that figure is going to give me a 10% increase on

5:03that figure so you can see visually that yes that does look correct and I can

5:07just repeat that for the other ones there and again talking about percentages

5:11just take yourself to this conversion rates sheet you can see here let's say

5:15these are product trials and how many people actually purchase the product I

5:19want to know what is the conversion rate well it's going to be the number of

5:22conversions out of which is I could divide by out of the total press enter

5:28and then I format that as a percentage so number formatting remember to give me

5:33for example in this case a 13% conversion rate and I can just repeat that all

5:38the way down so that was just a quick reminder about percentages but there's

5:44one more mathematical operator that we haven't talked about and that's to the

5:48power of you know squared or cubed or to the power of 3 4 5 6 7 8 9 10 whatever

5:52I

5:53think the proper term is exponentiation but anyway if you take yourself to the

5:58manufacturing calculations sheet let's imagine we're producing some kind of

6:02widgets and they are spherical so the little balls so we want to work out the

6:07volume of these widgets that we produce and we produce our widgets in one two

6:11three four different sizes and we know the radius of the ball so we want to

6:15work

6:15out the the volume of our different widgets as per their radius measurement

6:20and I have given you in the skill the formula that we need to use in order to

6:25calculate the volume of a sphere so you can see that it's 4 divided by 3 times

6:30by pi which is 3.14 times by the radius to the power of 3 so that's a

6:36calculation we can type into Excel using the mathematical operators so let's

6:40give it a go so equals 4 divided by 3 times by pi now we could use a function

6:48for pi but I'm just going to type in 3.14 as an acceptable approximation for

6:52us today multiplied by the radius which is in that cell there to the power of 3

6:59and it's the to the power of bits that we haven't done yet so the way we type

7:03that in is with the little carrot sign so on my keyboard it's a shift 6 to give

7:07me the little hat so that's to the power of 3 so if I press enter that's going

7:12to

7:12give me the volume of our widgets that's got a 2 centimeter or a 2 inch radius

7:17whatever the units are that's the volume so let me repeat that down there we go

7:21and just to finish off this spreadsheet you can see that we've got a current

7:24price per unit of volume so let's say this is our manufacturing price so we're

7:29saying it's the volume the number of units of volume there multiplied by our

7:34current price per unit of volume remembering that we need to fix that cell

7:38absolutely do you remember we talked about absolute cell referencing and I can

7:42give you a keyboard shortcut here so it's your F4 function key that will pop in

7:46the little dollar signs you need wrapped around the cell reference in order to

7:50fix that cell reference in our formula let's just input that and fill it down

7:55perfect

0:00In this video I would like us to pick up on a couple of other quick and easy

0:04functions that you may or may not have yet come across. And you already know

0:08about the SUM function. You'll see the SUM button, the auto SUM button up here.

0:12And if you click the drop down that's a bit of a clue, a sneak preview as to

0:16some of the ones we're going to look at in this video. And you are very used to

0:20using the SUM function to add up a load of figures that are above the cell

0:25where you want the total or to the left of where you want the total to appear.

0:29But

0:29you can use that SUM function to actually see a total somewhere else on the

0:33spreadsheet if that makes sense. So looking at this information here we've

0:36got loads of data and this little yellow shaded box is kind of like a

0:39summary. So if I did want to see the total sales to date then yeah I'm going to

0:43use a SUM function but I'm summing that lot there. Nice and easy. But these

0:48are the ones you may or may not have spotted. So average age that's going to

0:51be an average function. Youngest age that's a min function min for minimum.

0:56Longest length of service that's a maximum value. So max and number of sales

1:01people that's going to be some kind of count function. So let's try them. So we

1:07could either pick them from the drop down here. So average and you can see it's

1:12typed in the word average and then it's guessing that I want to average the

1:17well

1:17that doesn't make any sense. But while this is still shaded I can click and

1:21drag to override what it is I want it to work out the average of. Well it's the

1:25average age. Thank you very much. So if I choose that. So yes you can pick some

1:29of

1:29these functions from the drop down here or you could just type them. And they

1:34're

1:34not I mean they're not difficult to type. So youngest age on joining well that

1:37's

1:37a min function. So if you type in equals min. Oops good if I typed right equals

1:42min and then open parentheses there we go. So equals min min of what? Well it's

1:48the youngest age on joining. So here's my age on joining values. So that's what

1:53I

1:53want to find the minimum of. Longest length of service. Well here are our

1:56length of service figures and the longest length of service well that's going

2:01to

2:01be a max you know short for maximum. Gosh my typing is really very poor today.

2:05So equals max open parentheses or open bracket and then choose the length of

2:10service figures there. Press enter. Great. But this next one is interesting the

2:15number of salespeople. Now we do have a count function. So let me type in

2:19equals

2:19count open bracket and let's just you know count the number of salespeople

2:23we've got here and I'm disappointed that I've gone a zero. Well you may or may

2:27not

2:27have noticed when you typed in the count function in fact any function you get

2:32these pop ups that literally pop up giving you as much help as XL can muster

2:37to help you choose the right function because there are hundreds literally

2:40hundreds and hundreds of functions in XL and there are a number of different

2:44counting functions. So straightforward count by itself as you can see from the

2:49little helper information here it counts the number of cells in the range that

2:53contain numbers. So because I asked it to count that range of cells there the

2:59first name well that was all text so it wasn't going to give me the value I was

3:03expecting. But if instead I choose count A that counts the number of cells in a

3:08range that are not empty so that's going to be more useful for me to count the

3:12number of cells people by pointing it at the list of names. So if I choose that

3:16then I can then click and drag over the names like I did before press enter and

3:20that's better I've got nine. The other count function worthy of note let's just

3:25quickly you know call up the helpers again is count blank so counts the

3:29number of empty cells in a specified range that can be useful for a variety

3:33of reasons but I pointed out in particular because it is on the exam

3:37outline for the Microsoft XL exam that accompanies this course. But we've had a

3:42go at some other functions there but noticing if you were to select a group

3:47of cells let's select these cells here look down on your status bar you may

3:51well see some information like I've got here so I have selected the age on

3:55joining and it's showing me what the averages of that selection what the

3:59count is so how many values what the numerical count is so how many numbers

4:03there are in the selection the minimum and the maximum value and the sum well

4:07if you don't have those figures showing here because I can't remember what's

4:11the

4:11default if you right click and just make sure there we go down here that all of

4:16these are checked well if you don't have to check them all but I've got them

4:18all

4:18checked that's why they're all showing there and that will mean you know

4:22sometimes you do just want to see at a glance here what is the average age well

4:25you may or may not want it displayed permanently on your spreadsheet you may

4:29just want to know so I've just selected those cells I can see oh yeah the

4:32average

4:32age is 48 or you know how many how many values are there in that selection

4:37ah there we go the count there is nine so that can be very useful as well

0:00Using named ranges and structured references in our formulas can make them much

0:05more readable

0:06and you will see what I mean by that when we try it.

0:09Let's start with named ranges and we have already looked at this before when we

0:14talked about ways of navigating a spreadsheet.

0:16So in this particular file we do have a named range, so over on the left hand

0:20side here on the name box

0:22I've just clicked the drop down, so we've got a named range called Joining

0:24Dates, which is that collection of cells there.

0:28And as I said we talked about it in the context of helping you to navigate a

0:31spreadsheet because it means you can easily hop to important groups of cells

0:36that you have named.

0:37But in terms of referencing them in a formula, it means that the formula can

0:42reference the range called Joining Dates rather than the range F2 to F10, which

0:47is how Excel will normally reference that range in the absence of a name that

0:51we've given it.

0:53Now I feel like I'm making this much more complicated than it needs to be, so

0:56let's just crack on and name this range, let's name that range there current

1:01age and let's see how we can use that in a formula.

1:04So current age, I've just typed in the name box that I want to call that

1:07particular range, that means it should be available, there we go, so current

1:10age that's a new range.

1:12So now for this average age formula here, rather than averaging E2 to E10, well

1:18let's just get rid of that and type in the average formula again, and I

1:23actually want to use the named range, so it's going to be current age, and I

1:27start typing the name of that named range and it's popped up as a suggestion,

1:32so let me choose that.

1:33So average of current age, press enter, of course it gives me the same result

1:36because it's looking at the same group of cells, it's just that, if you look at

1:41that formula in the formula bar, that I think is much more readable than

1:45average E2 to E10, so average of the current age is using the named range

1:51rather than how Excel would refer to the range.

1:54Just while we are talking about named ranges, don't forget that you find your

1:58list of named ranges on the formulas ribbon tab here in the name manager, so

2:02there's the one that was already there in the workbook and that's the one that

2:06we just created so you can edit them or delete them or even create new ranges

2:10from within here as well.

2:11But the other thing I wanted to mention in this video is the idea of a

2:14structured reference, so I'm just going to dive onto the other sheet in the

2:19same file, so the performance data sheet, and what I'm going to do is convert

2:25this block of data here to a table, and just before I do that, just to remind

2:30you that the only named ranges we've got are current age, which we created just

2:33a second ago and joining dates which was already there, they're referencing

2:37cells on the other sheet, remember, but there's nothing else in the list is

2:39there.

2:39So what I'm going to do now is format this as a table, we know how to do that,

2:42so I think my, oh yes, that's one of my favorite formats let's choose that one.

2:47Yes, my table has headers, yes it's got the right block of data selected, click

2:52OK, so that is now formatted as a table and we know about that, but look, if I

2:56click the drop down here, I now have table one identified as, well, you know,

3:01it is just like a named range, it's a block of cells that Excel knows is table

3:06one.

3:07But it doesn't have to be called table one, because look on the table design

3:11ribbon tab, the table name there, well, I could change that, I could call it

3:15whatever I like, so I'm going to call it, let's call it performance data,

3:20something like that, and that does mean when I pick the drop down here, it

3:24knows that performance data is that block of cells, it's that table.

3:27So that's interesting to know, but the next thing I'm going to do to help us

3:30see how we can use what we call structured references, I'm going to add an

3:34extra column to this data because you can see I've got the sales value of the

3:38sales that have been made by various sales people, the percentage commission

3:42that each sales person's going to get whatever it is, but I want to add another

3:46column for the actual amount of commission, so commission amount.

3:50There we go, so let's just make that a bit bigger, you'll see that that's been

3:53incorporated into the table with the formatting and whatnot, which is another

3:57nice thing about tables, but I'm going to put in a formula that's going to work

4:00out actually how much commission gets paid, so it's going to be the percentage

4:04multiplied by the sales value, and look what happens when I do that.

4:09So 4%, so I've clicked on the cell and rather than Excel putting G2 in the

4:14formula, which is what you would expect.

4:18I've got this here, this at commission percentage, it's picked up the actual

4:22heading of the column, and again that's more meaningful than just a random G2

4:26cell reference.

4:28So anyway, so commission percentage multiplied by the sales value, and again,

4:33can you see it's picked up the name of the column there, much more meaningful

4:38than F2.

4:39If I press enter, what you'll see, again, this is a lovely magical property of

4:43tables, it has assumed correctly that I do want that repeated all the way down

4:47the whole column, and it just works so nicely.

4:51I just love Excel tables, and the use of these column names in place of the

4:55usual cell references is what we call structured references, and it's yet

5:00another magical property of formatting as a table.

0:00We've already learned about the rand between function to generate random

0:04numerical data

0:04But just so that you've got all of the functions in one place and you know to

0:08recap

0:08We will just quickly do another example. So let's imagine we want to you know

0:12generate some dummy data for student schools or something

0:16So let's imagine down the side. We've got student one now

0:19Don't forget Excel is very good at continuing the pattern for you

0:22So let's imagine student one two three four five and then

0:25Assignment one and again, maybe we've got assignment one two three something

0:29like that

0:30So let's just make those columns a bit wider. So we need to populate this bunch

0:34of cells here with some example data

0:36So remember the rand between function

0:40asks us to

0:41whoops

0:42The rand between function. Let's just try that again now

0:45You'll notice that I selected all of the cells before I started typing because

0:49I want to put in a random number into all of those cells

0:52But what I was about to say is you will remember that the rand between function

0:56here

0:56It is requires to what we call arguments

0:59It needs to know well

1:00What's the bottom of the range that you want me to create a random number from

1:03and what's the top of the range and these two

1:05Arguments, you know the two inputs that it needs get separated by comma

1:09So for these student assignment scores

1:11I want a range of numbers between one and then a comma and a hundred

1:15So those are the two arguments that I'm populating there one and a hundred the

1:18top and the bottom of the range

1:19And because I want this to go into every cell I had selected

1:23Can you remember the keyboard shortcut we used to do that?

1:26Control enter will pop that into every cell

1:29Now if you click on each cell

1:31You'll see that sure enough there's that formula or the function rather in

1:35every cell

1:35And you might remember that this will update every time your spreadsheet

1:39changes

1:39So don't be distracted if you notice these numbers change as we continue

1:43But anyway, we've talked about rand between before but the other function for

1:47this video

1:47We have not seen before it's the sequence function

1:51I think it's relatively new to excel about you know 2021

1:54I think we first saw it and it's designed to enable us to create a sequence of

1:58numbers

1:59And you might be thinking well, can't we just use you know the little fill

2:02handle like we did for student one two three four or assignment one two three

2:05to create a sequence

2:06Yes, we can the trick is though if you want to create a sequence of numbers

2:11using that method

2:12You have to do the first two steps of the sequence

2:14So if I want numbers one to ten as a sequence there

2:17Then I would have to do the first two steps there and of course that does work

2:21perfectly

2:21Well, and so you might now be thinking well, why do we need a function to do

2:25that?

2:25Well, there are a couple of reasons

2:27I mean, let's imagine you wanted a sequence of numbers going and I know all the

2:32way down to row

2:332090 or something you would be scrolling out of control by you know trying to

2:38do it that method

2:39So the actual sequence function would give you a much more controlled way of

2:42doing it

2:43But also the sequence function becomes useful when you use it in conjunction

2:47with other functions as you learn more about Excel and do more sophisticated

2:51examples

2:52But also in my opinion the sequence function is especially useful if you want

2:57to create a grid of numbers a grid of

3:00Sequential numbers, so that's the type of example we will build up to in a

3:04little sort of what we're going to create a little calendar

3:06But let's build up to that

3:08So let's just first of all try and create a sequence of numbers

3:11You know just a column of numbers going from one to ten using the sequence

3:14function

3:15So start typing the word sequence and then choose it from the list with the

3:19double click and this is where you get to see the

3:21Arguments that it needs, but there's only one compulsory argument these

3:25arguments in the square brackets are optional

3:28So let's imagine we just want to a column of numbers going from one to ten

3:33Well, that's actually ten rows that we're populating because that's going to

3:38give us a list of ten numbers in the same column

3:40You know using ten rows

3:41So if that's all I want one to ten in a column then that's all I need to do

3:45Done, but let's get rid of that and try some of the other arguments

3:49So now let's see imagine so again using the same function this time

3:54Let's imagine I want a single row of numbers

3:57But this time starting at the number five and going five ten fifteen twenty

4:01twenty five

4:02So increasing by five each time up to twenty five

4:05So this time I need to use all of these arguments

4:08It's only going to be a single row because I want the numbers to go

4:11horizontally

4:11How many columns do I need well five ten fifteen twenty twenty I need five

4:16columns

4:16Where do I want it to start? I want it to start at the number five and I want

4:21it to increase by five

4:23That's what the step is. Yeah, what's the step increase? I want so let's put in

4:27five again

4:28So that's a single row with five columns starting at five and increasing by

4:32five and that should if I press enter

4:34There we go five ten fifteen twenty twenty five perfect

4:37But as I said, I think this does become most useful when you want to fill a

4:41grid of sequential numbers

4:43So let's imagine we want to put together some kind of little calendar

4:47representation

4:48So Monday Tuesday Wednesday Thursday Friday Saturday Sunday so Excel knows the

4:52days of the week and let's imagine

4:54week one week

4:56Two and again, let's just get it to fill that up to week five

5:00So let's imagine for the month of August. Let's just pull that up there

5:04So for the month of August so August starts on Friday the first

5:08But I want to fill in the dates for August in this in a sequence

5:13But this time it's filling a grid rather than just a column or just a row

5:18So what I have to do is this so equals sequence

5:22Okay

5:24How many rows do I want?

5:26Well, I want five rows one for each week on my little grid here

5:30How many columns do I want? Well, that's the days of the week. So it's seven

5:33columns

5:34Where do I want it to start now?

5:36I have to be a bit strategic here because I want the number one to be in Friday

5:40the first

5:41Don't I because that's you know, that's the date of the first of August

5:45So if that's going to be one I need that to be nought there for Thursday

5:49And we're going to get rid of these in a second

5:51So nought minus one minus two minus three

5:54So I'm going to go minus three as the starting point

5:57And I can actually leave the step value empty because the default step value is

6:01just one and that is an optional argument

6:03So I'm just going to close the bracket and press enter

6:05Fantastic. It's done. It's a yes Friday the first and then you might be

6:08thinking well these look a bit ugly

6:10Then you know, they do look a bit silly

6:12But if you were to try and delete them ah well

6:14You've actually deleted the whole function because if I undo that the way it

6:18works is we typed the function into the top left-hand corner

6:22of the grid or the array here and therefore it uses what we call spill that's

6:26the the proper term for

6:28Spilling the results over into the rows and the columns that you specified as

6:33part of the arguments in the original function

6:36You know, we told it how many rows and columns to fill

6:38So therefore the rest is automatically generated because of what we typed in

6:41originally

6:42So we can't delete that because it'll delete the whole lot

6:44But I'm a big fan of cheating so all I'm gonna do is make the text color white.

6:48There we go

6:49It's a really nasty cheat, but oh and that's also make Thursday the north white

6:53as well, but hey it works

6:55So that's the sequence function for you

0:00You can see that we're back in that sales team info file that we used earlier

0:04on in this skill.

0:05And I'd like to use this to try out a couple of examples of IF functions.

0:09Now, with an IF function, you are asking Excel to perform what is described as

0:14a logical test,

0:15and then return different values based on whether the outcome of that logical

0:19test is true or false.

0:22And I think that'll make more sense when we actually see an example.

0:25So let's imagine I'm just going to add an extra heading here for bonus with a

0:29question mark.

0:30So let's imagine we are going to pay our salespeople a bonus, but only if they

0:34have earned more than a million pounds worth of sales to date.

0:38So I want this column to say yes, if they have earned more than a million

0:41pounds in sales,

0:43so therefore yes they get a bonus, otherwise it'll display no.

0:46So we'll do this as our first example.

0:48So it's going to be an IF function, so equals IF.

0:51Let's just double click to pick it up from the list of suggestions there.

0:54As we know, it'll give us this helper to indicate what the arguments are.

0:58And the first is the logical test that I mentioned in the introduction.

1:02So we're asking Excel to perform this logical test.

1:04And the test is, is that cell there greater than a million?

1:09So a million is 1, 2, 3, 4, 5, 6, 0.

1:13So that's my logical test, is cell J2 greater than a million.

1:16So that's the first argument.

1:18So let's put the comma in to move on to the next argument.

1:21And now I'm indicating to Excel what I wanted to display in cell K2 here,

1:26IF that logical test is true.

1:28Well I want to display the word yes, they do get a bonus.

1:31And because it's text that I want to display, I have to put it in quotes.

1:35If it was just a number I wanted to display, I could just type the number, but

1:38as I say,

1:38text has to be in quotes.

1:39So that's the value of true argument.

1:41Let's type the comma to move on to the next argument and let's just make it say

1:45no if they don't get a bonus.

1:48So there we go.

1:49So if their sales to date is more than a million pounds, display the word yes,

1:53otherwise display the word no.

1:55So let me just enter in that formula.

1:57You can see for that first row that is correct because Bernie here has indeed

2:01earned more than a million pounds in sales to date.

2:04If I repeat that all the way down we should see, yeah, let's just check the

2:07nose, yeah, those are all less than a million good.

2:11If rather than just saying yes or no, we wanted to actually display the amount

2:14of bonus that they get, so this time we're saying,

2:17okay, if their sales to date is more than a million, pay them an extra thousand

2:21pounds, otherwise pay them 100 pounds.

2:24So this time it's going to be the same logical test, so equals if, and the

2:28logical test is, is that greater than a million?

2:31One, two, three, four, five, six.

2:33If so, display a value of what did I say, a thousand pounds, I've already

2:36forgot what I said.

2:37So we're going to pay them a thousand pound bonus if they have achieved more

2:40than a million pounds sales to date, if they haven't.

2:43So if the logical test comes out with a false outcome, they're only going to

2:47pay them 100 pounds.

2:48You get the idea, so again, close the brackets and just repeat that all the way

2:51down.

2:52Good.

2:53Let's just imagine though that this salesperson down here is so Vera, so you

2:58can see that she has just about got over a million pounds in sales to date.

3:02I'm going to change that so that it is exactly a million, so one, two, three,

3:05four, five, six.

3:07And you'll see therefore that her bonus has gone down to 100 pounds rather than

3:10a thousand pounds, because of course our test was if this value is greater than

3:15a million.

3:16And then we realize, actually our management has said if they've earned a

3:19million or more, they get the full bonus.

3:23So I'm just going to go back up to the top of the column and change this so

3:27that the logical test isn't just greater than, it's greater than or equals two.

3:31So that's how we would write greater than or equals two, let's just input that

3:35formula, repeat it all the way down and hope that Vera, yes, few, she's been

3:39credited with achieving the million pound sales to date, yes, she gets the full

3:43bonus.

3:44Let me just take you to this other sheet here, this performance data sheet that

3:47we have seen before and show you this column, this commission column, because

3:52that has already got an if statement in there.

3:55It's basically saying if the country sell, so if e2, but of course, because

3:59this is formatted as a table, it's using the structured reference.

4:04So if the country equals UK, so that's the logical test, if the country equals

4:09UK, and because we're using text there, we have to put it in quotes, a bit like

4:13the yes and no example we did a second ago.

4:16So if you're using text in an if function, you have to put it in quotes, so if

4:19the country field equals UK, so that's the test, if it does, then display 4%

4:23for the commission, otherwise display 2% for the commission amount.

4:28So for whatever reason, UK sales earn a bit more commission than other sales.

4:32And the reason why I point that out to you is that it's quite nice, we can see

4:35the structured reference in action there.

4:37If we were to go back to the file before we formatted it as a table there, you

4:41can see the difference in the commission if statement there.

4:45So it's if e2 equals UK, display 4%, otherwise display 2%.

4:50So with these examples that we've just looked at in this video, we are only

4:54working with two possible outcomes, aren't we?

4:57You know, is that logical test true or false? There are just two outcomes there

5:00.

5:01But we can get really clever and nest if statements within each other, and we

5:05can even have multiple logical tests.

5:08But looking at the skills measured for the Microsoft Excel exams, it appears

5:11that that more advanced fun stuff has been reserved for the Excel expert course

5:16.

5:17And so what we've just done in this video is all we need for this particular

5:20course, which accompanies the Excel specialist exam.

5:23So we're going to leave it there.

0:00In this video, we're going to look at the sort and unique functions.

0:04So make sure that you've got this workbook open to give you the same material

0:08to play with here.

0:08And the sort function at first glance doesn't seem that useful given that you

0:14already know how to sort a list, don't you?

0:16So I've got a little tiny list here and we do have the filter button switched

0:20on because this has been formatted as a table.

0:22So as you know, if I wanted to sort this list in, I don't know, reverse

0:25alphabetical order, then I could, you know, I could do exactly that.

0:27So when I show you what the sort function will do, as I said, at first it'll

0:32seem a bit redundant given that we can sort a list like this.

0:35But hang on in there. I'm going to show you another example as to where this

0:38sort function could be useful.

0:40So let's just take a slow to start with. So let's imagine we want to see a

0:44version of this list over here which is sorted alphabetically.

0:47So for that we could use the sort function. So equals sort. And then it says,

0:51well, what do you want me to sort?

0:52So the sort array, you need to point it to what you want to sort. That's any

0:56compulsory argument here.

0:57So I'm going to say, well, please sort that lot. And if I press enter, you'll

1:01see that the default sort order is it a little sort by the first column in

1:06ascending order.

1:08So it's just the same as having done a to z, you know, sorting it by the name

1:12of the item in an eight or z fashion.

1:14So that's exactly what we've got. But we do have other arguments as well.

1:18So if I go back to the sort function, and yes, I still want it to sort that lot

1:22.

1:23But if I press the comma to hop us on to the next argument here, the sort index

1:28is where I can identify which column I want to sort by.

1:32So let's imagine I want to sort by the price of the item. So that is going to

1:37be the number of the column of the array.

1:40So the default is it's just going to sort by the first column in the array.

1:44That's why the first time I did it, it just sorted it alphabetically by name.

1:48But if I specify a sort index by typing in a two, that means it's going to use

1:52the second column to do my sort.

1:55So it's effectively the same as clicking the drop down here and saying

1:57descending order by price. But I'm doing it through a function.

2:00So that's the sort index. And then the sort order, well, the default is

2:04ascending, but I want to make it descending.

2:07So that means it puts in a minus one. So one would be ascending, minus one

2:11would be descending.

2:12And then the last optional argument there is, am I sorting rows or columns? And

2:16the default is I'm sorting rows.

2:18So that's exactly what I am doing. I'm sorting rows of data here, so I don't

2:21need to put anything in for that.

2:23So I'm hoping if I put that in right, I should get a version of this list,

2:28which is sorted by price by the figure with the highest at the top.

2:32Let's see if that works. Yep, that looks like it has. So let's just confirm

2:35that by doing the same over here.

2:37So largest, a small list. Yeah, that is the same, isn't it? And as I say, you

2:42're going to be thinking to yourself, well, why would I want to do that when I

2:44can so easily sort a list using the arrows?

2:46Well, hang on in there because I want us to now look at the unique function.

2:51And then we will see how we can use sort and unique together for, you know, for

2:55quite useful purposes.

2:57Okay. So the unique function, what that will do, it'll look at a list of data.

3:01So let's look at, I don't know, this particular set of data we've got here.

3:06Let's imagine I've got a little electronic lending library and I record the

3:09books that people download from my lending library. So in January, all of these

3:13are the downloads.

3:14I've got the genre of the download and the author. So I've got the same genre

3:18repeated many times in the list.

3:20So if I just want a short list of just the genres in my list, I can use the

3:24unique function to pull out the unique entries in that list.

3:29So let's try it. So it equals unique. So there we go.

3:33Returns the unique values from a range or array. So it says, well, what is it

3:36that you want me to look at the unique entries from?

3:39So if I click that, now you could click and drag to select the, you know, all

3:42of the cells in the column.

3:44But if it's quite a long column, when you click and drag, it might get carried

3:46away with you. So I'm going to press Ctrl Shift down arrow to select all the

3:52way to the end of the active cells there.

3:55And if I press Enter, you can see that it has just pulled out the unique

3:59entries there taken from that list.

4:02And here's the clever thing. If I would like the result of that unique function

4:07to be sorted, that's where we would combine unique with sort.

4:13So very often, the sort function that we just saw a second ago is most commonly

4:18useful for sorting the results of another function rather than just sorting a

4:23random list in Excel,

4:24which you could just, you could just as easily do using sorting and filtering

4:27that we already know about.

4:29So look at what I'm doing up here in the formula bar. I am wrapping literally

4:32the sort function around that little unique function that we had in there.

4:38I had to put an extra bracket on the end because the first bracket belonged to

4:41the unique function, but because I'm wrapping the sort function around it, then

4:44that needs its own pair of brackets.

4:46If I press Enter, you can now see that that little list of genres is sorted. It

4:52's got an adventure at the top there, so it's sorted alphabetically.

4:54And the nice thing about this is because it's generated using a function if the

4:58list of genres changes.

5:00So let's say I look at this and think, "Oh, no, no, that wasn't a historical

5:02novel. That was an action novel.

5:05Now, action is not on our list of genres at the moment, but if I just change

5:09this to action, there we go, watch the blue shaded cells change.

5:15It's added action to the list, and it has sorted it into the right place."

5:19And this does become quite useful when you do have a great long list of data

5:23and you want to extract unique values and, as you can see here, sort them.

5:27So let's just practice that. Let's do the same again for authors because the

5:31author's names are repeated through here.

5:33Let's extract the unique author's names and have them sorted.

5:37So we're going to start with the sort, and then what are we sorting? Well, we

5:41're sorting the unique values. So sort, unique.

5:45And then it's the authors this time press enter. Oh, this is where it has

5:50spotted that I haven't put the extra bracket on the ends.

5:53I'm going to say, "Yes, I will accept that correction." But there we go. That

5:56should have given us the list of all of the unique entries in the author column

6:00sorted alphabetically, and you can see that is the case.

6:03And don't forget at the end of this skill you will have another chance to

6:06practice an example of every single one of these new functions that we're

6:09learning.

0:00In this video, we're going to try out some of the functions that Excel has got

0:04for formatting text in cells.

0:06And I'm not going to lie, I was mildly disappointed that these functions were

0:10included on the list for the Excel specialist exam,

0:13because as we will see, there are other, easier ways of doing what these text

0:18functions do.

0:19And I personally would have prioritized different Excel functions above these

0:22ones, but never mind, it is what it is.

0:25So let's work through an example of each one on the list.

0:28Make sure that you're in the text functions spreadsheet that I have given you,

0:32and put yourself onto the part numbers tab here for the first couple that we

0:36will try,

0:36which will be left, right and mid.

0:39Now, let's imagine looking at these part numbers, we want to extract the first

0:43two characters from these part numbers.

0:46They're all letters, aren't they? The rest are numbers, but the first two

0:48characters are letters.

0:49So I need to extract the letters, the first two characters there, of these part

0:52numbers.

0:53So basically, I want to extract the first two characters counting from the left

0:57.

0:58So that's what the left function will do. It will enable us to say, with this

1:02function, please, look at that cell there.

1:05So that's the first argument is what we want it to look at. And then it says,

1:08how many characters from the left do you want me to extract?

1:11Well, I want the first two characters, please, from the left. So if I choose

1:14that, then you'll see, sure enough, it has pulled out OP, the first two

1:17characters there,

1:19and if I just fill that all the way down, yep, it's done it.

1:22So that's what the left function is. The right function is kind of similar, but

1:26it counts from the right hand side.

1:28So if I want to extract, for example, the last five characters, so in the case

1:32of the first one there, it will be everything except the letters.

1:35So it's the same cell that we're looking at there, but counting from the right

1:38hand side, please extract for me the last five characters.

1:42And you can see it has pulled out the number. But if I just fill that all the

1:45way down, of course, that doesn't really work too well with this particular

1:48example.

1:49If I was trying to extract just the numerical part of the part number, it hasn

1:52't worked because not all of my part numbers here have got five characters.

1:57And I kind of hard coded into the function. They didn't either. I wanted to

2:00pull out the five characters from the right.

2:03So if what I was trying to do was to pull out the numbers, whether it's five

2:07numbers or four numbers, then perhaps I should have used a different function.

2:11And that's where mid can help me out.

2:14So what mid does, it returns a specific number of characters from a text string

2:18where you specify where to start the extraction.

2:22Let's try it. So if I type in equals mid, and again, where do you want me to

2:26look? It's saying, so it's that cell again I'm interested in.

2:29Where do you want me to start? Well, I want you to start from character three.

2:33That's where the numbers start in my part numbers because they've all got two

2:35letters at the beginning. I've already extracted those, but now I want to

2:38extract the numbers.

2:40So start the extraction at character position number three, please. And it's

2:44five characters that I want you to extract.

2:46Of course, if there are only four, it'll only find four. But if I choose that,

2:50then hopefully, yeah, good. That's done the job.

2:53So sometimes it's pulled out five characters. Sometimes it's pulled out four.

2:56But the point being it's done the extraction starting from the position of the

3:00third character.

3:02So these functions work perfectly well. And I did express my reluctance that

3:07these have been included in the Excel specialist exam because there are easier

3:12ways of doing exactly what we've done here.

3:14And you might already be guessing what the easier ways were. The easier ways

3:17would have been just to have used flash fill.

3:20Do you remember when we were talking about tidying up data, you could just

3:23start typing what do you wish the data looked like and Excel will carry on the

3:27pattern for you.

3:29So if I did want to extract the first couple of characters, well, I just

3:32started doing it manually and it gets the pattern.

3:35If I now want to extract the numbers, again, just do the first couple of steps

3:39of the sequence manually and then it'll spot the pattern and suggest that it

3:42could, you know, it could complete it for you.

3:45And that, of course, is much easier than messing around with left or right or

3:49mid functions.

3:51The next one on our list is Len. Now what Len does, it counts the number of

3:55characters in a particular cell. So if I, let's just do an example there.

4:00So if I type in equals Len, open bracket, well, that cell, please. And if I

4:05close the bracket, you can see it's counted that there are seven characters in

4:08that cell.

4:09So as you already know, some of our part numbers are seven characters long,

4:11some are six characters long.

4:13So that's what it's done. It's just counted the number of characters in the

4:15cell. And arguably, looking at that, it's not that useful.

4:19I mean, admittedly, Flash Fill could not have done that for us, but how useful

4:23is that?

4:24Well, let's think of a scenario where we might want to combine the number of

4:27characters with another function.

4:30So let's say in our organization, our part numbers look like this, and the ones

4:35that are seven characters long indicate that the product is stored with a

4:39distributor.

4:40If the part number is six characters long, it means it's stored in our

4:43warehouse.

4:44So actually, we could use an if function to tell us what the location of these

4:49parts are.

4:51So actually, let's add a little heading up here of location and make that bold

4:55because it's a heading.

4:57So what we're saying is if the part number is six characters long, then the

5:01product's in the warehouse, otherwise it's with a distributor.

5:04So I'm going to put in the if function, and the logical test is going to be the

5:08Len function.

5:10Wow. So we've got a function within a function here.

5:13So my logical test is the length of that, if that equals six, gosh, can't type,

5:21then the product is in our warehouse,

5:23and we know that with a if function, we have to use quotes for text-based parts

5:27of the function.

5:29Otherwise, so therefore, our seven character part numbers are stored with a

5:34distributor.

5:36Right. Make sure I get the right number of brackets here because we've got

5:40functions within functions, but I think that's looking good.

5:43So therefore, that first one, the locations with a distributor, so I'm hoping

5:47that some of them will show warehouse.

5:48Yeah, the shorter ones will show warehouse, the longer ones show distributor.

5:51So that's quite an interesting use therefore, yes, sometimes it is useful to be

5:54able to count the characters,

5:56and the chances are we'll be using the result of a Len function as part of

6:00another function like we did there.

6:03Okay, take yourself down to the next sheet along, the data sheet, and let's

6:07take a look at upper, lower and proper, the next three functions.

6:11So you can see I've got some data here. Oh, my goodness, it's a mess, isn't it?

6:15Maybe it's coming from some other system, and it's all a mixture of upper and

6:17lower case and whatnot.

6:19So if I want this data here to be presented all in upper case, all in block

6:23capitals, then that's where upper comes in.

6:26So if I type it upper of that, please close the bracket and then to fill that

6:30down, you'll see perfect.

6:32It's re-typed that whole list in upper case, but I could have used Flash Fill

6:36for this as well.

6:37And it's the same if actually I want it all in lower case, so lower of that,

6:41please, and fill it down.

6:43So you get in the idea here, and then proper, so there we go, proper would give

6:49us initial capitals, you know, capitalization for each word,

6:52which probably is actually what we might want for names.

6:55As I said though, Flash Fill would have done all of that for us without needing

6:59to use either upper or lower or proper functions.

7:02So more commonly, again, I think we're likely to use these types of functions

7:06wrapped around other functions.

7:09So for example, do you remember, yeah, it was this one here, wasn't it, where

7:13we had the unique function?

7:14It already had sort wrapped around it, but I could further wrap around the

7:18upper function.

7:20So let's say upper, and then put all of that within the brackets for the upper

7:24function, so that's going to put that whole little list in block capitals.

7:28Let's just go back to here, text functions, and then onto this last tab, this

7:33names tab, for our last two functions,

7:36Concat and TextJoin, which are all about joining cells together.

7:40So let's try the Concat one to start with to see what that does.

7:43So Concat, so Openbracket, Concat of what?

7:47Well, it's of those, please, and if I do that, you'll see it has literally

7:51glued the contents of those two cells together.

7:54That doesn't look that great though, because you'd normally have a space,

7:57wouldn't you, between the first name and surname,

7:59but you can see what it's done.

8:00It has literally glued together all into a single cell, the contents of the

8:03range that you specified in the function.

8:05So Concat does what it says on the tin, but for this particular example, I

8:09think the alternative is going to be better, and that is TextJoin,

8:14because what TextJoin will do, yes, it will join cell contents together, but

8:19you get to specify if you want to a delimiter,

8:23and a delimiter is the little character that you have as a separator.

8:27So, you know, if you talk about comma delimited values, it means that there's a

8:30delimiter, a comma, separating the values.

8:33So for us, let's specify a space as the separator, and because it's a character

8:38, we know we have to put that in quotes, don't we?

8:41So I'm just putting a little quote, and then hitting the space bar, then

8:44another quote, so that's my delimiter.

8:46The next argument is, do we want to ignore empty cells?

8:49I'm saying, yeah, okay, ignore empty cells, and then the next one, it says,

8:53well, where's the text you want me to join together,

8:55and it's going to be those cells there, so close the bracket.

8:59That's looking good, let's just repeat that all the way down.

9:02Excellent, it's done the job, but I think you know what I'm going to say, I

9:06could have done this again using FlashVille.

9:08If I wanted Bernie and Halton together in the same column, I just start typing

9:12that for the first one,

9:14so Bernie, Halton, and then Christoph Renau, and it would have done it.

9:19Nevertheless, concat and text join are different ways of joining together

9:23contents of cells into a single cell,

9:26and as I said, in the very next part of this skill, you get the opportunity to

9:30try an example of every single function that we have learned in this skill.

0:00My goal with this workbook here is to give you the opportunity to practice the

0:04functions

0:05that you need for the MO210 Excel specialist exam and also to give you

0:10something to refer

0:11to when you want to remind yourself what the functions do and what their

0:14arguments are.

0:16So looking down at the bottom you can see that there are four tabs.

0:19I've kind of grouped the functions together into these four tabs here so you

0:22need to

0:23work through all four tabs and on each tab what you'll see is a yellow box

0:27giving you

0:28the instructions those are the things I want you to do.

0:30You're going to be filling in functions into the yellow cells and then in the

0:35blue and

0:35it's the same for each of these sheets but in the blue I've given you a

0:38reminder of what

0:39the functions look like so if you flick across there you'll see that for some

0:42of them I've

0:43given you a little screen snip of the tip that pops up to show you what the

0:46arguments are.

0:48So have a go working through all of this you'll see that the text functions one

0:52well that's

0:52the meatiest one and there's even a challenge there that you may or may not

0:56feel strong

0:57enough to tackle but nevertheless give yourself a bit of time to have a go at

1:01working through

1:02as many of these functions as you can and then I will record another video

1:06working through

1:07the answers which you can watch once you've had a go yourself.

0:00Let's start on the sheet that I have labeled "quick functions".

0:04I think these should be the easiest ones for you to do.

0:06So let's put the totals into column F as per the first task in the peachy-

0:10colored box.

0:11Be a bit careful here though. If you had clicked the auto-sum button,

0:14it might have just picked up that cell immediately to the left there

0:18because there's a blank cell there for an unmarked assignment.

0:21So make sure that you do get it to include all three of the cells in that row.

0:26And then of course you can just fill that down. So the totals, hopefully that

0:28was easy.

0:29Let's fill in this little summary down here. So the average total score, so it

0:34equals average.

0:35And you'll notice in the reference, the way that I've written it in the blue

0:38box here,

0:39I've put in the equals, the actual function itself, and then the open bracket

0:44just to remind you

0:44that that's how you get it to pop up with the arguments that you need to put in

0:49.

0:49So as soon as you type the open bracket, then it knows you're ready to go.

0:52Anyway, it's the average of that lot. You should of course close the bracket or

0:55parentheses,

0:56I should say. But if you do just press enter, then it won't mind.

1:00Anyway, the next one along, the highest total score, that's a max function.

1:04So it's the max of that lot. And the next one is a min function. So the min of

1:10that lot.

1:11This time though, number of students. Well, we've got three different count

1:15functions.

1:16So the number of students, if I want to count their names, then I need to be

1:19sure to use the count A function

1:22because that'll count cells that aren't blank. So let's choose that.

1:26And then the number of scored assignments. Now that's just going to be a normal

1:29count because I do want to count

1:31where I've got numbers. And then the number of unscored assignments, well, that

1:35's the other count function,

1:36which is count blank. So count blank, so odd to know how many blanks there are

1:41in that lot.

1:42So hopefully your figures matched mine. But anyway, on to the next sheet,

1:46it's rans between and sequence. So your task. Number one, to use the sequence

1:51function to generate a list of 20 sequential

1:54invoice numbers in column A here starting at five. So equals sequence, open

1:59bracket, well, how many rows?

2:02Well, it's 20 rows. I'm not worried about how many columns. So I'm just going

2:05to type a comma again to move

2:08me on to the next argument. You see the comma separates the argument, but if

2:11there's one you don't use,

2:12then just type the comma again to move you on to where we start because it says

2:16I need to start at five,

2:17and I don't need to put in the step because that's an optional argument and the

2:21default will be,

2:22it'll step by one. So that's exactly what we need. And then here, it says task

2:26number two, use rand between

2:29to generate some random numbers between 10 and 500 for the values in column B.

2:34So it equals rand between,

2:36there it is. And the range was between 10 and what did I say, 500. Whoops. Yeah

2:44, there we go, 500. Good.

2:46And then I can just double click the fill handle to repeat that all the way

2:49down. Excellent. And then the next one,

2:52if sort and unique. So your task number one, use an if statement in column D to

3:00display yes if the rating is less than three,

3:03and no, if it is not. So it equals if open bracket. So I need the test. Now the

3:08test is, is that less than three? And if it is,

3:14I want to display yes. And if it isn't, I want to display no. So let's just pop

3:20that in and again, just repeat that all the way down.

3:23So we can see, yeah, there we go, the low scores. There we go. Do require some

3:27further action for feedback. Good.

3:30And then in cell F five, since this is task number two here, combine the sort

3:36and the unique functions to produce,

3:39whoops, a little typo there. Let me just change that to produce a list of

3:43sorted topics in the yellow box. Gosh, another typo.

3:46Let me just get that. So hopefully by the time you get to see this file, you

3:49won't have those typos.

3:50So I want something in there using sort and unique. And there's also a hint

3:54that there is a range called topics.

3:56So let's just check out what that range is, a named range. Yeah, it's got those

4:00cells already selected in the named range.

4:03So that should make it easier for me to create this function. So I'm going to

4:06start with the sort.

4:08Now, what am I sorting? Well, I'm sorting the unique values in that named range

4:12. So topics, there it is.

4:15Close the bracket and I need another pair of brackets. There we go. Yep,

4:20brilliant.

4:21I just stuck with the default sort, which is alphabetically, that looks perfect

4:24.

4:25Ont the text functions, the next cheat along. Oh, this is quite meaty. So task

4:29number one.

4:30Use the right function to extract just the rating from column D here to display

4:35in column E.

4:37So the right function, not forgetting that I do have that reference for you

4:41with all of the functions,

4:43but the right function, so it's counting from the right hand side. So I want it

4:46to look in that cell and just count one character in from the right hand side,

4:50please.

4:50Good. Let's just fill that all the way down. Task number two says use the left

4:54function to extract just the employee number from column A and put it into

5:00column F.

5:01So equals left. So, yep, there it is. Look at that cell, please, and count the

5:08first, what the first four characters, I guess, to extract just the employee

5:12number.

5:13Good. Let's repeat that all the way down. Good. Task number three here says use

5:18the mid function to extract the employee's last name from column A and put it

5:23into column B.

5:24So mid means that we specify the point at which we want it to start counting.

5:29So that's the cell to look at, please, and start counting from, I suppose, the

5:33fifth character for how many characters.

5:35Well, I don't know. I'm going to say 12 characters. That should be enough to

5:38capture everybody's surname. Everybody's last name.

5:41So let's just repeat that all the way down. Good. That looks good. And then

5:45number five here.

5:47Nope. Number four here. Experiment with the concat and text join functions to

5:52display the employee's first name.

5:55And last name in a single cell in column G using the data from columns B and C.

6:00Right. So concat.

6:01So that's one of the ways we could use to join together different cells. And

6:05this is going to be interesting because normally with concat, so that one and

6:09that one,

6:10it doesn't give you a gap. It doesn't give you a space between the cells that

6:13you're gluing together. But happily for us on this occasion,

6:17because when we did the split to split out that surname, it included the space

6:21because we did the split from the fifth character onwards.

6:24And the fifth character is actually a space. If we had done the split from the

6:27sixth character onwards, i.e. at the very beginning of the surname, we wouldn't

6:31have this bonus space.

6:33Concat wouldn't normally give us a space. So instead, we would normally in that

6:38situation want to use text join because that does give us the ability to add

6:43our choice of delimiter, our choice of space.

6:45So let's just do it properly. So the delimiter, the separator, if you like, is

6:49going to be a space which I put into quotes.

6:51Yes, I'm happy just to accept the default. I would ignore empty cells. It's

6:56that and that please that I want to join together with a space.

7:01And that's done it with a proper space this time. And then task number five

7:05says, reminds us to think about, do we want to use things like upper or lower

7:11or proper to convert how that's appeared?

7:14So I'm just wrapping the upper function around what we did with the text join

7:17to make that appear in block capitals.

7:20And then I can repeat that all the way down. Good. And then finally, your last

7:25task here says, people with a four digit employee number, which is most people

7:30here,

7:30are described as being full time employees, FTEs. And those with a three digit

7:35employee number, like Klaus here, are described as being full time contractors,

7:40FTCs.

7:41So saying therefore in column H, it's saying use the LEN function to identify

7:47who are FTEs and who are FTCs.

7:50So basically all we're saying is here, we want the number four to appear, which

7:53is our indicator that that person is an FTE.

7:56But if it's chosen three, that's our indicator that they are an FTC, a full

7:59time contractor.

8:01So the LEN function you will remember does a bit of counting for us.

8:05So it's going to return the number of characters in a text string.

8:08So it's saying, take a look at that and count the number of characters.

8:11And sure enough, for this first one, it's giving us four. It's done the count

8:14exactly right. Perfect.

8:16So let's repeat that for the whole column. And this is where you will have

8:19spotted, if you gave this a go, that it doesn't seem to have worked.

8:23You know, this is a three digit employee number. Why is it showing us four?

8:26And it's the same for all of these that are three digit employee numbers. It's

8:29still showing us four.

8:31And I don't know whether you managed to spot why that's the case, but let me

8:35tell you.

8:36So do you remember when we use the left function here in the employee number

8:41column, we use the left function to extract the employee number from column A,

8:46didn't we?

8:47And we asked that left function to look at column A and extract the first four

8:52characters.

8:54So for the four digit employee number, yeah, it extracted the first four

8:57numbers, perfect.

8:59But for the three digit employee numbers, it still took the first four

9:02characters.

9:03The first three were numbers and the fourth character was the space. Oh.

9:07So actually, all of these three digit employee numbers, they looked like three

9:12digits, but that extra space was also included.

9:15So when the then function did the count, it counted four characters. There are

9:19four characters there.

9:20So the then function got it exactly right, but it didn't quite do what we hoped

9:24.

9:25Well done. Make sure you save this workbook with all the examples so that you

9:29can use it as a resource to help you revise these functions if you are

9:32preparing for the Excel specialist exam.

9:35I hope this has been informative for you, and I'd like to thank you for viewing

9:38.