Module 7 Project A

- [Instructor] This is a
screen cast for module seven,

SAM project 1A.

I have already downloaded the file.

I need to go ahead and save it

with the file name it
needs for submission.

So I'll click on File, Save As,

and the only thing I need
to do to change the file

is change the one at
the end of the file name

to a two and click Save.

I'll refer to the instructions now.

We're working for Valerian State College.

The first step lets us know
that we will be creating

an HLOOKUP function to determine

a student's potential base hourly rate,

which is based on a number of years

of post secondary education.

This HLOOKUP function will
include a structured reference

to look up the value in
the post secondary years.

The table array will
include range P13 to U14.

The results for what we
find on the table array

can be found in the
second row of the table.

And the last thing we will want to do

is find an approximate match.

So let's go ahead and
go to the Excel file,

the student representatives
sheet, specifically cell E2.

And let's begin our horizontal lookup.

I'll go to the Formulas
tab Lookup Reference

in the function library and
locate the HLOOKUP function.

It mentioned that the
lookup value should be

post secondary years, so
I will click on cell D2,

rather than type D2.

That way I can get a structured reference

in the lookup value.

The Table Array can be
found in range P13 to U14.

Let me move over there.

And select that range,

P13 to U14 and it says to express it

as an absolute cell reference.

So at this point is when you
type the function four key.

It also mentioned that the
results I'm looking for

are in row two of that table array.

And that it wants me to
find an approximate match.

So once you're in the range lookup area,

you can see that the closest
match is a true indicator

and an exact match would
be the false indicator.

So I'll go ahead and type
true inside the range lookup.

And click OK.

Because this is a table,

the actual function should
be copied down automatically

to the other parts of
the table in column E.

Our next step wants us to
perform an IF function to test

to see if the student's
age is greater than 23,

the return should be yes.

If the student's age
is not greater than 23

the results will be no in the cell.

And we'll create this
IF function in cell J2.

Alright so when doing the IF function

I'll go to the Formulas
tab function library,

logical category gives me my IF function.

We're testing to see if the age in C2,

I'm clicking on C2 and I get my structured

reference for age is greater than 23.

Then the text results
will be in quotes "Yes."

If the logical test is not
true, the results in the cell

will be in quotes, because it's text, no.

Alright, and the results are now showing

in column J using a structured reference.

Task number three wants
us to see who is eligible

for leadership training
offered by the office.

So what they're having us do is in cell K2

enter a formula using the
if/and/or functions as follows.

We're testing to see
if this post secondary,

post secondary years is
greater than or equal to two

and if the student's finance
or if the student's finance

certified status is yes.

So if either condition is
true, the result will be yes,

if neither one of the conditions is true,

the text results will be no.

So let's go cell K2.

This is a nested function that will

begin inside an IF function.

So we'll begin the IF function.

And if either condition is
true, means we start with the

OR function in the logical
test, open parentheses.

So the first thing we're testing to see is

if the post secondary years,
so click in that first cell

under post secondary years, is
greater than or equal to two.

If that's or, so you type a comma,

because the other condition

will be if the student's
finance certified status is yes.

So we click on cell G2 once again

to get the structured reference.

We type equals.

Yes in quotes because it's text,

and then we have to supply
closing parentheses.

If either condition is correct,

then the value of true will be yes.

If either condition is not true neither

condition is not true, then
the results will be no.

And go ahead and be sure and

type it exactly like the
instructions show it.

Just so you're sure to get
all points and then click OK.

Because it's a table
that structured reference

should a copy all the way down.

And your first no indicator should be

on row nine for Warren Stewart.

Task four would like us
to construct an IF/AND

function combination to
test to see if the student's

age is greater than or equal to 21,

and the student's post secondary years are

greater than or equal to three.

If both of these indicators are true,

the results will be yes.

If either indicator is not true,

the results in the cell will be no.

So let's go ahead and
construct this in cell L2.

All right, so once
again, it's an IF with an

AND statement, so we'll
be using the IF function.

And in the logical test,

begin the test with AND open parentheses.

Alright, the first
indicator or determination

is to see if this student age,

scroll over so you can
see the student age,

is greater than or equal to 21.

Comma for the other determining factor.

We're testing to see if the
student's post secondary years.

So click on cell D2 and get
the structured reference,

are greater than or equal
to three, close parentheses.

If the value is true for both of those

indicators, the results will be yes.

And if either one of them are not true,

the results will be no
go ahead and click OK.

And in cell L2 your first no indicators

should be on row six for Ida Smith.

Task five is asking us to
construct a nested IF function

that's going to test whether
or not the elected column

shows a value of Yes, if it
does, then it's going to let us

know that that particular
student has already been elected.

The nested part or portion
of the IF statement is going

to test to see if the financial
certified column is equal

to Yes, if it is, then it
will return a yes result.

If it is not equal to the text Yes,

then it will return a no or false result.

And this will be done in cell M2.

Alright so, I'm gonna go ahead go to

create a logical IF function.

Once again, we are testing to see

first of all if the elected column

in I2 equals Yes, in
quotes because it's text.

If that logical test is true,

then the value of true will be elected.

If it's not true, we nest our value,

our if statement, in
the value of false box.

So if the finance certified column.

So if open parentheses the
finance certified columns,

specifically G2 in this case, equals Yes.

Then the results comma then
the results will be yes.

If the finance column does not show yes,

type a comma then the result will be no,

with a close parentheses.

Be sure to have your
quotes around anything

that is going to render text in the cell.

Go ahead and click OK.

Looks like I have a period

after the second occurrence of
yes here, and I need a comma.

And I also have a comma after this first,

this first occurrence of yes
in the nested if statements.

So make sure you press the
comma instead of the period,

and then it will give you the
results you're looking for.

Tasks six and seven want us to see which

students would qualify for a tier two job.

The condition is, is if the student

if their post secondary
years are greater than

or equal to four, then they
do qualify for a tier two job.

If not then they qualify
for tier one jobs.

So we'll go to cell up here
in N1 in our Excel document

and type in the word tier capital T-I-E-R.

And then in cell N2 let's construct

an IF function to see who qualifies.

The Formulas tab logical if.

So, if a student's post secondary years

are greater than or equal to four,

then they will qualify
for a tier two position.

If not, they qualify for tier
one positions and click OK.

All right column N now shows the results

from that IF function.

Task eight.

We're going to demonstrate how
to use the IF error function.

We're checking to make sure
that no student ID is incorrect

when someone's trying to
look up a particular student.

So we're going to apply
the IF error function.

If someone types in an invalid ID,

it will give this information
in specifically cell Q3.

So let us set it up to do that.

In cell Q3 we have a V lookup function.

So just after the equals
up in the formula bar,

type in IF error, open parentheses.

So we're saying if an error
is found in the V lookup

when looking up student IDs
and finding exact matches,

go to the end of the
function, type in a comma,

and provide the the invalid ID text.

So in quotes, invalid
Student ID, close quote,

and close parentheses for
the beginning parentheses

you put right after IF
error, right click OK.

Now, to test it, go up to
the student ID cell in Q2

and type in 12, or just any random number.

Because that student ID cannot be found,

it will generate in Q3, the
invalid student ID error.

Now I'm gonna go ahead and
press Ctrl Z to undo my

invalid student ID and put K
Colbert's information back in.

Task nine wants us to use the
COUNT IF function in cell Q8

to count the number of
students who have been elected

to offices in student organizations.

So go ahead and select Q8.

COUNT IF is a statistical function.

I'll go to the Formulas
tab function library,

more functions statistical
to call up the COUNT IF.

Alright, once again, they
want to know in this step,

how many have been elected to offices.

So I've got to go find
the elect field name,

elected field name, and include
all the values in column I.

And the criteria I'm looking
for there would be yes,

so in quotes, because it's
text, the criteria will be Yes.

I can see that I'm going
to have 11 students

that are elected and I can click OK.

Task 10 wants us to use
the AVERAGE IF function

to determine the average
number of post secondary years

for students who have been elected.

Alright, so let's go to R8.

Cell R8 and the AVERAGE IF once again is

an elected or a statistical function.

So I'll go to more functions,
statistical, AVERAGE IF,

first of all, it wanted us
to go to the elected field

to see who had actually been elected.

So that's what it's asking
for in the range box.

So let's go back to elected and

include all values in elected.

The criteria should be yes, in quotes,

that's what we're looking
for in the elected field.

And then the average range is going to be

over the post secondary year's values.

And you know it's an average range.

If you're trying to find an average range,

you know there's got to
be values in that column

in order for it to qualify

for an average range entry, and click OK.

All right, looks like four
students fall into that category.

Task 11 is going to have us go to cell R9

and do a simple average
function to determine

the average number of post
secondary education the years

of post secondary
education of all students.

So it's a simple average,

let's do a one liner in
this in this instance.

So you can see that you
can actually do that

rather than using the
function argument box

whenever you want to do that.

So type in equals in
the formula bar average.

Double click average as it is presented,

and then we want the average
of post secondary years.

So back to that particular column.

Select all values in the column.

Don't really need to close parentheses.

You could press enter at this point,

but let's go ahead and supply that

at the end of the formula
in the formula bar.

Task 12 would like us to switch to

the Academic Groups worksheet.

In satellite 14, we will
be using the index function

to display the value in the first row

and first column of the
Academic Groups table.

So let's go from the student
representative sheets

to Academic Groups, click
anywhere on the data on the table,

and then activate your
table design tab to see that

over on the left side of the
ribbon, under the table name

we have a name a table name,
and its Academic Groups.

And we'll be using that as we
construct our index function.

So go ahead then and click in cell A14.

Let's click on formulas,
lookup and reference,

activate the index function.

Our instructions have told
us that it would like us

to include a row number and a
column number in our function.

So we'll choose the first
option here, click OK.

The array is the table name itself.

So let's type in Academic Groups.

We'll supply a row number of one

and a column number of
one and then click OK.

The result is computing
club because within the data

in the table, computing
club is in the first row

and the first column of data.

Task 13 then wants us
to use a SUM IF function

in cell A17 to display the
total membership in 2023

for groups with at least 40 members.

So in a cell A17 let's
type in equals SUM IF.

It's a conditional sum.

First of all, it wants to know
what range I'm looking at.

So I'm looking for
numbers or data for 2023.

So I'm going to supply that here.

And when I type a comma
it also wanted to know,

wanted to display the data for groups

with at least 40 members.

So I'm going to type in greater than

or equal to 40 in the criteria.

The sum range in this case is
not bolded and not required.

So I can type a close
parentheses and press enter.

OK, doesn't like something that I've done.

So let me see let me choose the SUM IF.

From the list.

This SUM IF function
is probably better off

if we use the function argument box.

So I'm gonna go to math and
trig and locate the SUM IF.

And the first thing it wants is

the range of membership for 2023.

So I'm going to select
the data in column G.

And the criteria needs to be
greater than or equal to 40.

Now as I tab away from the
criteria box it's going to supply

quotes that it needs for the criteria.

The sum range box is
not bold and is optional

and not needed for this
particular situation.

So click OK.

And you should now see the 2023 membership

in large groups down in cell A17.

Task 14 is going to have
us create a pivot table.

So we will be switching to
the academic pivot table

worksheet and creating
a pivot table in cell A1

based on the Academic Groups table.

And we'll just kinda walk
through the steps as we do this.

So if we go to the new spreadsheet,

academic pivot table, click cell A1.

On the ribbon, let's
click on the Insert tab.

And in the tables group, you're
looking for a pivot table.

All right, the table
range, then, in order to

get the table range, go
back to Academic Groups,

you could type the word Academic Groups

or you can select the table itself.

And then we will be generating
the pivot table in the

academic pivot table,
specifically cell A1.

So you have your placeholder
pivot table placeholder

and you have your Pivot
Table task pane on the right.

We'll change the pivot table
name to academic pivot table.

So in order to do that,
I'll activate my design tab,

actually, that's on the
Pivot Table, Analyze tab,

and then there's, if you're
in a kind of a condensed view

like I am, it's in the
pivot table drop down list.

And you can find the
box where you wanna put

the name in, Academic Pivot
Table, no spaces, and enter.

All right, the next thing
it would like us to do

is add the activities field

and the group name field to the rows area.

So if we add activities, drag
and drop to the rows area,

and the group name, in that
order, down to the rows area,

and you can see it's
building our pivot table

over here by rows.

We'll now add 2021, 2022, and 2023

to the values area in that order.

Now because they are actually numbers,

if you start clicking the checkbox,

activating the checkbox,

it will move them to the values area

or you can simply drag and drop,

whichever is easiest for you.

Task 14D wants us to display,

change the display of subtotals to show

all subtotals at the top of the group.

So in order to do that, we
want to go on the Design tab.

Specifically in the layout group

we have a subtotal feature
that will allow us to

show also subtotals at
the top of the group.

So here are your subtotals for field

and professional and service.

And then task 14E wants
to change the report

layout to show in outline form.

So in this same group on the Design tab,

in the layout group, report layout.

Once again it is shown in outline form.

Alright so it just shows it
a little bit differently.

Let me kind of scroll to show you here,

what things look like.

The last part of task
14 wants us to update

the sum of 2021 field value
in the field values area

to display the name 2021 membership.

So for these last four parts of task 14

we'll simply go over to the values area

and choose the drop down
arrow on some of 2021.

Choose the value field settings and

change the name to 2021 membership.

You can do the same for
2022 and 2023 as well.

Making sure that you spell
membership correctly,

you know, you want full credit for that.

And OK.

Let's do a quick save, it's
been a while since we've saved,

so I'm pressing Ctrl and
S to save my workbook,

and I'll go on to task 15 then.

We want to summarize data

for all the student
groups in a pivot table.

So to do so, our helper
the one we're working with,

Lael, is going to switch
to the all groups table.

We're gonna do that
with her and then switch

once we've done that, we
will edit the record for

the astronomy society to
use 76 as the 2023 value.

So let's go to the next
table our next worksheet,

which is all groups,
particularly row six for 2023.

The actually the astronomy record six,

the astronomy society,
for 2023 should be 76.

So I'm gonna go ahead
and type that number in.

So we've made a change on our table.

So what needs to happen
then, is we need to switch to

the all groups pivot table
worksheet and refresh

the pivot table that's there
to verify that the 2023

membership value for the astronomy society

reflects that change that we just made.

So let's go to the all
group's pivot table.

Let's go ahead and refresh that,

refreshing is on the Data tab,

make sure you're somewhere
on that pivot table.

And on the Data tab, actually,

it's on the pivot table
analyze tab in the data group,

let's refresh, and I
usually just refresh all.

And then looking at the
astronomy society in row six

to make sure it reflects
76 for the 2023 membership.

Task 17 would like us to apply

a light blue pivot style
medium to pivot table style

to this pivot table.

So on the Design tab, you're
going to the light area,

light blue pivot style medium two.

So it's actually down in the medium area.

Light blue pivot style medium two.

Task 18 wants us to
add the office field to

the filters area of the pivot table

and then filter that
table set so that only

organizations with private
offices are visible.

So, in my task pane, I will drag off

the office field name
down to the filters group.

And then in row one I
can filter accordingly.

I want to make sure
that the private offices

are the only ones visible and OK.

Before we go on to task 19,
let's take a second look

at task 14, we actually changed the names

the display of the names
of the field values

to 2021 membership, but I
did not change the formatting

to the number format
with zero decimal places.

So let's go ahead and do that real quick.

On the sheet on the academic pivot table.

Just quickly go down to the values area,

click your drop down arrow and
choose value field settings

and go into the number formatting
and change it to number

from general and make sure it's
set for zero decimal places.

Do the same for 2022 membership

and the 2023 membership.

Otherwise, the grader
will dock you one point,

I don't want you to have to lose that.

Alright task 19 would
like us to create a slicer

based on the activity field
activities field value.

Then we'll resize that
slicer and place it in a,

an area that begins in
cell F3 and ends in J14.

And we'll also use the slicer
to filter the pivot table

so that only the fraternal
groups are visible.

So back on the spreadsheet,

I'm gonna scroll over a little bit.

And then on the pivot table analyze tab,

you should be able to find insert slicer.

And the directions mentioned that

it should be based on
the activities field.

So activate activities.

And then we'll be sizing the slicer

with the slicer, contextual
tab, make sure you get

into this the size group over
here, and the size should

be 2.2 by 3.2, height and width.

And then once you've got that set up,

you want to start the slicer in cell F3

and make sure it ends inside J14.

All right, and then you will be

filtering by fraternal groups.

And if you scroll back over
here to the pivot table

it should look like this.

Our last step, step 20,
says Lael also wants us

to summarize membership data
for all the organizations

using a pivot chart, and
that's to help determine which

groups are showing the most
interest from students.

So we'll be going to the
activities pivot table worksheet,

and inserting a pivot chart using

the clustered column chart type,

and then format it according
to the next three steps.

So let's go ahead go
back to the spreadsheet,

go to the activities pivot table.

Make sure that the pivot
table is active by clicking

on it somewhere, and then choose insert.

In the charts, group, pivot
chart and pivot chart.

And it is a clustered column
that is the default type here,

so all you have to do is click OK.

And then we will resize the pivot chart

to begin in cell F3.

And end in O19.

So let me close my pivot table task pane,

or pivot chart fields
task pane for a minute

so I can size this out to O19.

Right, it wants us to add a
chart title membership by type.

So I'll use the chart elements icon in

the upper right corner of the pivot chart.

Chart title, membership by type.

And then we'll filter
the pivot charts only

the membership data for
groups with educational field

and fraternal activities show up.

So notice you've got some
filtering tools in the

lower left corner of
the of the pivot chart.

Go ahead and make sure
that the educational field

and fraternal activities
are showing and click OK.

And then if you really need to make sure

that you compare it to your instructions.

Take a look at the illustrations
to make sure everything

is in place before you submit
this assignment for grading.

Thank you.