In this video tutorial, we'll cover multi-input functions in Excel including SUMPRODUCT, ROUND, and more with step-by-step exercises.
Multi-Input Functions
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. So 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 a request of 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 are 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 you're gonna say what you want but how many texts or characters you need to make sure that your result is successful.
Exercise 1
For the first exercise, I want to use the left function to return the state. I'm gonna 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 this cell. Now I'm not gonna enter anything else I'll just press ENTER and I get the letter n well that's not enough letters to give me the state. Like a lot of things in life, if you don't say exactly what you want you might not necessarily get it. So here I'm gonna go back to the formula and type equal left and then when I select the cell I'll need to enter a comma. Currently, the text is in bold so when I enter a comma the next thing I need to provide is the number of characters. 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. Mow I will need to pull the zip code from the right side of that cell for this.
Exercise 2
In exercise 2 I'm told to use the right function and that's actually the right function for the job. I'm gonna 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 gonna 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 a 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. Now if I was doing this for multiple states and multiple zip codes I can simply use autofill and drag the formula down and get all the results for state and zip.
More Exercises
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 gonna do here is type equal then the area code is located on the left so LEFT or L E F and I'll just press tab. Left arrow to select the cell or you could use your mouse. Enter a comma and then I'll tell myself there are three digits in an area code so I'm gonna 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 gonna 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 are actually being told what the answer is you're looking for. four digits from the right I'll enter four, closed parenthesis, press enter and now I can autofill this down.
SUMPRODUCT
Our next multi-input function is SUMPRODUCT. Now for SUMPRODUCT, we want to 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 I'll 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 gonna take 22 and I'm gonna multiply it by 3. 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 a hundred and sixty-five. 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 for different formulas and then typing the 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 SUMP 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 are 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 closed parenthesis and press I get the same exact 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 SUMPRODUCT function gives you the product and the sum.
Fruit Exercise
This is similar to an earlier exercise. I want to come up with the total price of ten apples at 50 Cent's 15 oranges at $1 and 20 bananas at 25 cents so what I can do now is type equal SUMP tab. I'm going to select all of the quantities, enter a comma and then select all of the prices. when I press ENTER the total amount for those products is $25. much simpler than creating a third column of values that I need to multiply and then add up.
ROUND
Finally, we'll take a look at the round function. When it comes to round the rules are a digit to be rounded if a digit to be around it is 5 or larger than values rounded up they have a digit to be rounded is 4 or smaller that value is rounded down let's test this out. 1.5, 1.4. I'm gonna go over to 1.5 and on the Home tab, I'm gonna decrease the decimal because I want a whole number. I don't want any decimals. let's see if this stays at 1 or goes to 2. it goes to 2 so this is accurate. Now according to this, it says if the digit to be rounded is 4 or smaller that value is rounded down. I'm gonna go over here and I'm gonna decrease them. It stayed at 1 so that was also accurate. so how does this work in real life well here's a situation I'm gonna type in 0.5 press Enter I'm going to type in 0.5. Now I'm gonna 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 Cent's 50 Cent's 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 gonna type round and I'm gonna select this value and though here's the multi-input part I need to say how many digits I want when it comes to the decimal. We are evaluating decimals when we are rounding this number and I'm gonna 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 as I don't want their value to be added to the rounding of this number. when I press ENTER I get one I'll fill this down. Now let me try to add these two numbers and see if there's a difference. I'll go over and click AutoSUM 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 2/7 in five point six nine to seven. 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. now 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 because I've removed the influence of the third and fourth decimal place.
Rounding to Thousands
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 gonna 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.
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. We're also able to round numbers to the nearest thousands or hundreds if we need to.