Basic Excel Business Analytics #11: Dynamic Grading Model: Excel Table feature & VLOOKUP

Basic Excel Business Analytics #11: Dynamic Grading Model: Excel Table feature & VLOOKUP


Welcome to Highline BI348
class video number 11. Hey, if you want to download
this file, BI348 chapter 7, the second file, and follow
along, click on the link below the video. Hey, we’re still in chapter 7
talking about model building. But guess what? I’m going to show you how to
calculate your current grade, except for we want
it totally dynamic. So as soon as you
add any new scores, your grade,
percentage, and decimal will update automatically. And we’re going to use an
awesome dynamic feature called Excel table. Now, think about
this– we need to know our score for the
assignment, the actual total points possible
for the assignment. Then we add those when
we calculate our percent and decimal rate. I’m going to start off by typing
assignment Tab points possible Tab my points Tab. That’s all we’re going to
need for our field name. So every time we have
a new assignment, we come put the name, the
actual total points possible, and then your points. I’m going to add
some formatting, because this is going to be
the field names at the top. And all I’m going
to use is Control B. Assignment– we’re going to say
homework, homework number 1, Tab. Points possible on this is 50. And I got 43 points on that. Now, check this out. This is a proper table. Field name is at the top,
and we have one record. But we’re going to use
the Excel table feature. Because as I add new
assignments or records, I want all my
formulas to update. So watch this. I’m going to click in a
single cell in our table, go up to Insert, and Table. I’m going to use the
keyboard Control T. Now, the Table feature is amazing. When I click OK,
it’ll format it. It will add these dropdowns that
allow us to sort and filter. And there’s a bunch
of other cool things. But the main cool
thing is watch this. I’m going to type my next
assignment directly below. And watch this. As soon as I hit Tab,
the table expands. Now I’m going to type the points
for that, 40, and I got 37. Control Enter. Now let’s come
over here, and I’m going to type total possible,
my current points, Tab, percentage grade, Tab,
current decimal grade. I’m going to highlight
all of these columns and then double click
to instantly best fit all of those. Now, these we do not
need to be dynamic. We’re only going to
have formulas below. So I’m just going to add some
formatting for our formulas. Now, before we
create our formula, I actually want to come
back over to the table and click inside
the table, and you get a context-sensitive
Table Tools Design. I’m going to click on that. And in properties, I do
not like the name Table 1, so I’m going to click there. And you can call it
whatever you want. I’m going to call
it My Class Scores. Now, the reason that
that’s so important to have a name for
this table is now when we start building
formulas and point to this, it’s not going to show
us cell references. It’s going to give us
the name of the table and the name of the field. That’s how it knows as we
expand to automatically update. You can actually use
references, also, but I’m not. I’m going to use
Alt equals– that’s the keyboard for the
AutoSum– and highlight total possible points. And check that out. It put the table name
and the field name. Tab, Alt Equals, highlight
My Points column, table name, column name, Tab. Now I can do my
percentage grade equals– it’s always the part divided
by the whole or the total. Tab. Now, before we can calculate
current decimal grade, we actually need the grading
table from our syllabus. Now, I’ve already
opened up our website. This is a PDF, and it’s
actually not as polite as if we had it in an
Excel spreadsheet or even a Word table. Because those would
automatically paste into Excel. But check this out. I’m going to highlight all
this Control C, Alt Tab to come over here, and I’m
going to come over to the side. I’m just going to try
and see what happens if I Control V to paste it. Oh, that does not look good. Copying from a PDF. Control Z– there’s
a great trick. This trick doesn’t always
work, but sometimes it does. I’m going to come up to
Paste and Paste Special, and I’m going to try
to paste it as text. And when I click
OK, well, almost. What it did was it pasted it
at least as a series of cells. I still need to
split this Apart Now, I can already see that I only
want to highlight the grade, so Control Shift down arrow. And notice if I
want to split this, if I’m going to use
text to columns– I have some text
in a single column, and I want to split it off
into multiple columns– it’s a space that separates the
percentage from the number. That space is
called a delimiter. It is the character that
separates the two fields– percent and decimal. So now I’m going to go up
to the Data, ribbon tab, text to columns, or I’m in
use the keyboard Alt A E. The delimited or fixed width–
sometimes you split columns if it has a fixed number of
characters, but we don’t. We want to use delimited. Next. It is a space. And watch the preview. You can uncheck Tab. And as soon as I click a
space, wow, that is cool. I’m going to click Next. We could preview
each one of these. It’s looking fine. The destination– I’m actually
going to let it replace J2. Sometimes, you want
to keep the original and paste it into a
different starting cell so you could change that. But I’m going to leave it and
click Finish, just like that. Boom. I have what I want. Now I’m curious. I’m going to go over to the Home
Ribbon tab and check that out. Text to column made
that percentage and made that general. Now, our label needs
to change, so I’m going to highlight
the decimal grade and control x, backspace to
get rid of that space tab, Control V. Now, that is a
nasty little thing that happens after you do text to column. Notice I had two words. And normally, if you paste
it into a single cell, it puts it all into the cell. But it thought I wanted
to continue that delimiter splitting, so
Control Z. I’m going to trick it by hitting
F2 and then Control V. By the way, to
turn that off, you have to save, close it,
and open it back up, and then it will
no longer do that. So look at that. It automatically took
the format from there. I’m going to right click and
point to the format painter to copy the format,
and then I’m going to click on Percentage Grade,
double click over here. Click in a single cell, Control
asterisk on the number pad to highlight the whole table. And I’m going to go ahead
and add some borders. Now, for v lookup right
here, this won’t work. I need to sort it from
smallest to biggest. So I’m simply going to
right click, sort, smallest to largest. And as we know, sort
will work just perfect. Now I can do my V
lookup equals VL Tab. For the lookup
value, I’m definitely looking at my
unformatted percentage grade– that’s a decimal
there– comma, and the table. Click in the top two cells. Control, Shift, Down
Arrow, F4 to lock it. Table– that’s the whole table. First column has the value
that it will try and match. Of course, it’s going to
take that 0.888888 and race through until it finds the
first bigger one, which would be 0.89, or 89%, and jump back. So the grade should be 3.4. But notice the 3.4 is
in the second column. So I’m going to comma 2
for column index, comma. And we’re doing
approximate match, so we do not need
to put true or 1. It is the default. So
I’m going to backspace. Remember, if you see square
brackets in your screen tip, it means if you know the
default, you can leave it out. We’re doing approximate
as the default. Close parentheses, Control
Enter, and there is our grade. Now, the amazing thing about
this whole setup– and you can format this if you want. And remember that that
formula will always look underneath at the
unformatted number in the cell, and then use that to look up. But here’s the amazing thing. And I’m going to type homework
number 350 points tab. And I got 42 on that. And watch– I’m entering
in multiple records. So since I’m in the last cell
in the last record in my table and I hit Tab, it automatically
adds a new record. Test number 1, it’s worth
100, and I got 87 points. Control Enter. And you’ve got to be kidding me. Look at that. It is absolutely
dynamically keeping my total points, my current
points, the percentage for both of those. And it’s always looking
up the current grade. So there you go. That’s a little model building. But for calculating our grade,
we’ll see you next video.

You May Also Like

About the Author: Oren Garnes

3 Comments

  1. Today I used =left(right(…. formula to get rid of the space and get my numbers into for separate columns. I must have forgotten convert text to columns option. Thank you for reminding of it.

Leave a Reply

Your email address will not be published. Required fields are marked *