Scribd
Upload
69 views4 pages

Excel BP4 StudentsGrades

This document outlines the requirements for an Excel assignment to automatically calculate student grades and other metrics. Students are asked to enter student data into an Excel spreadsheet and use formulas and functions to: 1) Sort students alphabetically and compute each student's weighted total 2) Use formulas to calculate each student's letter grade based on their weighted total 3) Create a graph showing the distribution of student grades 4) Write a 1-2 page word document explaining the benefits of an automated solution 5) Create a 5-6 slide PowerPoint summarizing the word document The document provides examples of useful formulas and functions for the assignment like sorting, nested IFs, VLOOKUP, HLOOKUP and COUNTIF

Uploaded by

Greg
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
69 views4 pages

Excel BP4 StudentsGrades

This document outlines the requirements for an Excel assignment to automatically calculate student grades and other metrics. Students are asked to enter student data into an Excel spreadsheet and use formulas and functions to: 1) Sort students alphabetically and compute each student's weighted total 2) Use formulas to calculate each student's letter grade based on their weighted total 3) Create a graph showing the distribution of student grades 4) Write a 1-2 page word document explaining the benefits of an automated solution 5) Create a 5-6 slide PowerPoint summarizing the word document The document provides examples of useful formulas and functions for the assignment like sorting, nested IFs, VLOOKUP, HLOOKUP and COUNTIF

Uploaded by

Greg
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Professors M. Rossin and R.

Saadé Fundamentals of IT and Business Productivity


Purpose
The purpose of this assignment it to have Excel calculate the requirements automatically
through formulas and/or functions. You need to enter as much of the above information
as you can in an Excel spreadsheet and create formulas and/or functions that will
automatically calculate the results.

*Be resourceful. Use the textbook if you need help and you can also research the web for
ideas.

*Try your best. This is not a math course and we will not answer math related questions.
You may, within reason, add your own assumptions and make small changes to some of
the variables to make it easier for you to work with. We are interested in your ability to
create a logical spreadsheet and use formulas and/or functions to have Excel calculate the
requirements.

Requirements

1. Sort the students in alphabetical order

2. Compute the weighted total for each student

3. Compute the grades of each student as follows

Grade Total
A+ > 90
A 85 - 90-
A -80 - 85-
B+ 77 - 80-
B 73 - 77-
B -70 - 73-
C 60 - 70-
F <60-

4. Provide a Graph of the student grades.

5. Write a Word document (1 to 2 pages) “selling” your new solution. You can
explain what problems could arise with the “old” manual way of calculating the above,
give a brief explanation of your solution and why it is a good solution, and explain the
benefits of an automated solution for automatically calculating the needed results.

6. Create a short PowerPoint presentation (5 to 6 slides) summarizing your Word


documents in point form.
Features that may be useful
(You can choose to use all, some, or none of the features below as long as your
solution uses formulas and/or functions to automatically calculate the
requirements)
Sort feature:
Highlight the area to be sorted Data menu Sort submenu Sort by “last name” in ascending
order Sort by “first name” in ascending order

Nested Ifs
=IF(G42<60,”F”,IF(G42<70,”C”,IF(G42<73,”B -“,---IF(G42<90,”A“,”A+))))

VLOOKUP function
Insert menu Function submenu VLOOKUP function: Searches for a value in the leftmost
column of a table, and then returns a value in the same row fro a column you specify. By
default, the table must be sorted in an ascending order.

HLOOKUP function
Insert menu Function submenu HLOOKUP function Looks for a value in the top row of a
table or array of value and returns the value in the same column from a row you specify

COUNTIF function
Counts the number of cells that meet the given condition

You might also like