Multi-Input Functions: From Text Extraction to Rounding

The main purpose of this section is to show you how to use functions that may take more than just one input. Earlier on in our Excel exercises, when we used functions, we only selected the text and then pressed enter and we got a result. Now we're going to take a look at a type of function that requires more than just one value or input.

We'll start with the LEFT and RIGHT functions. What do they do? They return a specified number of characters from either the left or right edge of a cell. Let's say I have asked someone to go buy me apples and I don't want them to buy me too many or too little.

Now if I just tell someone, please go to the store and get me some apples. That's not providing them enough information for them to be able to successfully get me the right amount. I have to say how many I want.

So in that same way, when you use the LEFT and RIGHT functions, not only do you specify what you want, but also how many characters you need to ensure a successful result. So for the first exercise, I want to use the LEFT function to return the state. So I'm going to go into this cell, I'll type in equal, then I'll type in LEFT.

I'll press TAB and then now I'll select the cell. Now I'm not going to enter anything else. I'll just press ENTER and I get the letter N. Well, that’s not enough characters to give me the state.

So, like with a lot of things in life, if you don’t say exactly what you want, you might not get it. So here I'm going to go back to the formula and type equal LEFT. And then when I select the cell, I'll need to enter a comma.

Currently text is in bold. When I enter a comma, the next thing I need to provide is the number of characters. So this is where I say how many apples I want.

I want two characters or apples from the left. If I enter the number two, I'll press TAB. That gives me New York.

Now I would need to pull the zip code from the right side of that cell. So for this in exercise two, I'm told to use the RIGHT function. That’s actually the RIGHT function for the job.

I'm going to go over to the original source cell that contains the values that I want to extract. And if you're using the RIGHT function, you're going to go to where the cell is. You notice that I did not go to the right.

I went to the left to select that cell. Now I'll enter a comma. And then I have to say how many digits are in the zip code.

Because this will tell me what number to put for number of characters. There are five digits in a zip code. I'll enter the number five.

And when I press ENTER, I get the zip code. And if I was doing this for multiple states, multiple zip codes, I can simply use autofill and drag the formula down and get all the results for state and zip. Here are two quick exercises that we have in the class where we want to be able to pull in the area code and the last four digits of the routing number.

So what I'm going to do here is type equal. Then the area code is located on the left. So LEFT or LEF and I'll just press TAB left arrow to select the cell or you can use your mouse enter a comma.

And then I'll tell myself there are three digits in an area code. So I'm going to enter three. I'll press ENTER.

And now I can use the autofill handle to populate the other values. Now here I need to pull the last four digits of the routing number. Maybe I work in a call center and I want to be able to pull the last four digits to provide this to the customer service representatives because I don't necessarily want to give them all the digits in the routing number for security reasons.

So I'm going to select that initial cell, enter a comma and then say how many digits I want from the right. Now, there's a spoiler alert directly above the cell. You're actually being told what the answer is.

You're looking for four digits from the right. I'll enter four, close parentheses, press ENTER. And now I can autofill this down.

Our next multi input function is some product. Now for some product, we want to, let's say, get a certain result. Let's say I'm being asked to multiply these two columns of numbers and add them up.

I don't know the reason why I'm just being told to do so. So I will do that. I'll do as I'm told.

I'll go over to the left. I'll type in equal. Actually, let me go over to the right.

So I'll go over to the right. I'll type in equal. Then I'm going to take 22 and I'm going to multiply it by three.

I'll press ENTER. Now I won't have to rewrite that formula because I'll use autofill that speeds things up. Then I'll go underneath all the values.

And let's say I'm being instructed to add them all up. I can go to the formulas tab and click autosum once and press ENTER. I have 165.

Now while I'm doing this, somebody's looking over my shoulder and says, hey, Garfield, I think I have a much easier method that you can use to get the result. And I say, yeah, so how would you get the same answer? And they say, this is pretty cool because this does not require you going to another column, typing in four different formulas, and then typing a fifth formula to get the result. You only need one cell to get the same answer.

So they show me how to do this. And they say, what you'll do is you'll type equal and then S-U-M-P. And then they let me know, unless I like typing, what I should do next is press TAB.

Then they say, just select all the points. That is one grouping of like values. Enter a comma and then make sure you select the same amount of values for the other column.

So those are equal pairs of numbers. I'll put in the close parentheses and press ENTER. I get the exact same result.

So they were actually right. This is a quicker way to get the same result. And I don't have to have multiple formulas to do that.

That's because the sum product function gives you the product and the sum. So what is a way that you could use this? Well, this is similar to an earlier exercise. I want to come up with the total price of 10 apples at 50 cents, 15 oranges at a dollar and 20 bananas at 25 cents.

So what I can do now is type equal S-U-M-P tab. I'm going to select all of the quantity, enter a comma and then select all of the price. When I press ENTER, the total amount for those products are $25.

Much simpler than creating a third column of values that I need to multiply and then add up. Finally, we'll take a look at the round function. Now, when it comes to round, the rules are a digit to be rounded.

If a digit to be rounded is five or larger, that value is rounded up. If a digit to be rounded is four or smaller, that value is rounded down. Let's test this out.

1.5,1.4. Now I'm going to go over to 1.5 and on the home tab, I'm going to decrease the decimal because I want a whole number. I don't want any decimals. Let's see if this stays at one or goes to two.

It goes to two. So this is accurate. Now, according to this, it says if the digit to be rounded is four or smaller, that value is rounded down.

I'm going to go over here and I'm going to decrease that. It stayed at one. So that was also accurate.

So how does this work in real life? Well, here's a situation. I'm going to type in 0.5, press ENTER. I'm going to type in 0.5. Now I'm going to do a little bit of a magic trick.

First, I want to add up these values. So I'll go to auto sum and add them up. It's expected that 50 cents and 50 cents will get you a dollar.

But I wonder what would happen if I rounded these values. So I'll go over to the home tab and I'll decrease the decimal, just like I did with the other values. Wait a minute.

One plus one equals one. That's a weird kind of calculation. Well, this is not really one.

It's still 0.5. When you apply formatting, it just changes the cosmetic appearance of the value. It doesn't actually change the value. If you really want to change the value, then you'll use the round function.

So I'm going to type round and I'm going to select this value. And here's the multi input part. I need to say how many digits I want when it comes to the decimal, like are we evaluating decimals when we are rounding this number? And I'm going to say I don't want to see any decimals.

So I'll type zero. It’s not so much that I don’t want to see them, but I don’t want their value being added to the rounding of this number.

When I press ENTER, I get one. I'll auto fill this down. And then now let me try to add these two numbers and see if there's a difference.

I'll go over and choose auto sum from the formulas tab. When I press ENTER, I get two. So that is different because we use the round function.

If I select both sets of values here and go over and increase the decimal, you'll actually see what's really going on. How can you use rounding in this situation? Well, a lot of times accountants need to add money and they only want to go out to two decimal places because that's all they're interested in when it comes to money. They don't want to have all those extra decimal places influencing the total amount that they get because they may get something that's known as a rounding error where they'll be off by a couple of pennies when they add their money together.

So they will use the round function and they will select the value and only go out to two decimal places. That means I will not get the influence of 27 in 5.6927. When I press ENTER, I can see that's the case. I also want to do the same for the rest of the values.

And there we go. If I were looking at these values and I were to put them on a scale, I would say one row would be heavier than the other. That's because I've removed the influence of the third and fourth decimal place.

You can also use this to round numbers to the nearest thousands. So you'll use a negative number in this case. So equal round because we want to go to the left of the decimal place.

I'm going to select this initial value, enter a comma, then enter negative three. Close parentheses. And I've rounded that number to the nearest thousands.

I'll move it over to the right. And there we go. So in this section, we learned how to extract text or numbers from the left or the right of a cell, how to use some product to come up with a total without using an extra column and also how to round numbers so we don't get rounding errors and we're able to round numbers to the nearest thousands or hundreds if we need to.