Documentation of excel and

calculations for gpa and cgpa in

excel For semester 01

Submitted By :MUHAMMAD QASIM RAZA ,

Husnain ALI , AMEER HAMZA AND NABEEL
NAWAZ

Submitted To
:

Mam HUDA GUL

Roll no :

23013122-009 , 23013122-007 , 23013122-

011 , 23013122-006
Introduction to Micros oft E xcel
Microsoft E xcel is a powerful spreadsheet program that allows users to organize, format, and analyze
data. From basic data entry to complex calculations and visual representations, E xcel provides a wide
range of functionalities for both personal and professional use. It is widely used in various industries for
tasks such as budgeting, financial analysis, and project management.
Basic Features and Functionalities
Microsoft E xcel offers a plethora of features and functionalities designed to simplify and streamline
data management and analysis. Users can create spreadsheets, enter data, and perform simple
calculations with ease. The program provides tools for formatting, conditional formatting, and data
validation, allowing for clear and visually appealing presentation of information.

Furthermore, E xcel's extensive list of built-in functions enables users to perform complex calculations
and manipulate data efficiently. Whether it's arithmetic operations, statistical analysis, or date and time
functions, E xcel provides a versatile platform for addressing diverse data-related tasks.

1 Data Entry
Users can input data into E xcel through individual cells or by pasting data from other
sources. This allows for efficient organization and management of information.

2 Basic Calculations
Users can perform simple arithmetic calculations directly within spreadsheet cells,
making it easy to obtain quick results.

3 Data Formatting
E xcel provides various formatting options, allowing users to visually enhance their data
for improved readability and analysis.
F ormatting Data and Applying F ormulas
Excel's formatting capabilities allow users to effectively present and analyze their data. From conditional
formatting for data visualization to custom formatting for labels and numbers, Excel offers a wide range
of options for enhancing the appearance and clarity of information. Moreover, the program's formula bar
provides quick and efficient access to Excel's large library of functions and formulas, empowering users
to perform complex calculations and analysis with ease.

Users can also create custom formulas using Excel's formula editor, enabling tailored operations
specific to their data and business requirements. This feature allows for advanced data manipulation,
making Excel a versatile tool for diverse analytical needs.

Conditional Formatting Data Visualization Custom Formulas

Data Bars Pie Charts Lookup Functions

Color Scales Bar Graphs Math & Trigonometry Functions

Working with S preads heets and Cells
Spreadsheets are at the core of Excel's functionality. Users can create, modify, and organize
spreadsheets to manage different types of data. Excel allows for easy navigation within large
spreadsheets, making it convenient to work with extensive datasets. Additionally, the program provides
powerful tools for managing individual cells, including cell formatting, data validation, and protection.

Furthermore, Excel's range of cell-based functions enables users to perform specific operations within
selected cell ranges, allowing for dynamic and targeted data manipulation.

Spreads heets Management Cell Operations

Users can create and manage multiple Excel provides various tools and functions for
spreadsheets within a single Excel file. This is managing individual cells, including formatting
useful for organizing different types of data and options and data validation. Users can set
separating distinct sections of a project. specific rules for cell entries, ensuring data
accuracy and consistency.
Easy navigation within the spreadsheets
enables swift movement between sections and Cell protection features prevent accidental edits
data points. and maintain data integrity.
Sorting and Filtering Data
E xcel provides powerful tools for sorting and filtering data, allowing users to organize and analyze
information in a structured and efficient manner. Whether it's arranging data alphabetically, numerically,
or by defined criteria, E xcel's sorting feature streamlines data presentation for easy comprehension and
analysis.
Additionally, E xcel's filtering functionality enables users to extract specific subsets of data based on
defined conditions. This feature is particularly useful for targeted analysis and for extracting relevant
information from large datasets.

1 Data Sorting 2 Data Filtering

Users can quickly rearrange data in E xcel's filtering options allow users to
ascending or descending order based on specify criteria for extracting specific
specific columns, ensuring a structured subsets of data, aiding in focused
presentation of information. analysis and decision-making.
Advanced F eatures and Functions
Excel's advanced features and functions offer users the capability to perform complex and sophisticated
data analysis and manipulation. From advanced mathematical and statistical functions to data modeling
and pivot tables, Excel provides a robust platform for addressing intricate data-related challenges.

Furthermore, users can leverage Excel's automation features, including macros and visual basic
scripting, to streamline repetitive tasks and enhance efficiency in data processing and analysis. These
advanced capabilities make Excel a versatile and indispensable tool for professionals engaging in data-
intensive activities.

Advanced Data Modeling & Automation & Macros

Mathematical Analysis Users can automate
Functions Excel's data modeling repetitive tasks through
Excel offers a wide range features and tools enable macros and scripting,
of mathematical functions, users to manipulate and significantly enhancing
including trigonometric, analyze data to extract productivity and efficiency
logarithmic, and valuable insights and in data management and
exponential functions, make informed decisions. analysis.
facilitating complex
calculations and analysis.
Creating Charts and Graphs
Visual representation of data is a critical aspect of E xcel's functionality. The program offers a variety of
chart types, including line charts, bar graphs, pie charts, and scatter plots, allowing users to visually
present their data for clear and impactful analysis. E xcel's robust chart editor provides extensive
customization options, enabling users to create visually appealing and informative graphical
representations of their data.
Users can easily create charts and graphs from their dataset, customize visual elements, and
incorporate them into reports, presentations, or spreadsheets. E xcel's graphing capabilities play a
crucial role in data storytelling and decision-making processes.

Data Selection Chart Customization Integration &

Presentation
Users select the data range E xcel's chart editor provides
they wish to visualize and a wide array of Once created and
choose the appropriate customization options, customized, charts and
chart type for their data allowing users to tailor the graphs can be seamlessly
representation. appearance and design of integrated into various
charts and graphs to best documents and
convey their data. presentations to visually
enhance the
communication of data
insights.
Data Analys is and Vis ualization
Data analysis and visualization are integral components of Excel's functionality, empowering users to
derive valuable insights from their data and present findings in a visually compelling manner. Excel
provides tools for data validation, scenario analysis, and pivot tables, enabling users to delve deep into
the intricacies of their datasets.

Moreover, Excel's visualization features, such as conditional formatting, sparklines, and pivot charts,
allow for impactful visual representation of data patterns and trends, aiding in effective communication
and decision-making.

Data Validation & Scenario Analys is Pivot Tables & Visualization

Excel's data validation tools and scenario Excel's pivot tables and visualization options
analysis capabilities facilitate detailed provide users with powerful tools for condensing
examination and validation of data, assisting in large datasets and presenting key insights in an
critical decision-making processes. easily understandable format.
Tips and Tricks for Efficient Excel Usage
Excel offers numerous shortcuts and techniques to enhance user productivity and streamline everyday
tasks. Through keyboard shortcuts, formula autocompletion, and range naming, users can expedite
data entry, manipulation, and analysis processes. In addition, Excel's collaboration and sharing
features, including version history and comments, facilitate efficient teamwork and document
management.
Furthermore, users can optimize their Excel experience by utilizing add-ins and customizing the
program's settings to suit their specific requirements, ensuring a tailored and efficient workflow.

1 Keyboard Shortcuts 2 Collaboration Features

By mastering Excel's keyboard shortcuts, Excel's collaboration and sharing
users can significantly accelerate their capabilities enable seamless teamwork
data entry and manipulation tasks, and document management, fostering
boosting overall productivity. efficient and organized workflow
processes.
Calculating GPA and CGPA in E xcel
In this guide, you will learn the step-by-step procedure for calculating GPA and CGPA using Microsoft
E xcel. This comprehensive tutorial will cover the process of inputting and calculating course marks, credit
hours, grades, and quality points to determine both GPA and CGPA.
Understanding GPA and CGPA
GPA (Grade P oint Average) represents the average of a student's grades across all courses.
On the other hand, CGPA (Cumulative Grade P oint Average) represents the average of the
GPA from each semester, providing an overall view of academic performance.

Calculating GPA and CGPA requires meticulous data entry and calculation for each course
including course title, course code, marks, credit hours, grades, and grades values.

1 GPA
Represents the average of a student's grades across all courses.

2 CGPA
Represents the average of the GPA from each semester, providing an overall
view of academic performance.
Required Data for Calculation
Before beginning the calculation, gather the necessary information
including the course title, course code, marks obtained, credit hours, and
corresponding grades. This data is essential for accurately determining
the GPA and CGPA.
Once the data is collected, construct a table with columns for serial
number, course title, course code, marks, credit hours, grades, grades
values, and quality points.
Step-by-Step Calculation Process
1

Data Entry - E nter the course details in an E xcel spreadsheet including course title, course code, marks
obtained, and credit hours.

Grade Calc ulation - Based on the marks obtained, calculate the corresponding grades using the grading
system provided by the institution.

Quality Points - Calculate the quality points for each course based on the obtained grades and credit hours.

Data Entry Grade Calculation

E nter the course details in an E xcel Based on the marks obtained, calculate the
spreadsheet including course title, course corresponding grades using the grading
code, marks obtained, and credit hours. system provided by the institution.
Understanding the Grading
System
An essential part of the calculation involves understanding the institution's
grading system. The grades and their corresponding values play a crucial
role in the GPA and CGPA calculation process.

It's important to ensure that the correct conversion key for grades to grade
values is used, as this varies across different educational institutions.
 CALCULATIONS FOR GPA
In the above tables that shows the GPAs for semester 1 and semester 2 the Grade values , Grades and Quality points can be calculated by
using following formulas:
Grades values can be calculated using formula :
=IF(H12="C-
",1.7,IF(H12="A",4,IF(H12="B+",3.7,IF(H12="B",3,IF(H12="B=",2.7,IF(H12="C+",2.3,IF(H12="C",2,IF(H12="D",1,IF(H12="F",0))))
)))))
Grades is calculated by using formula :
=IF(E12>84,"A",IF(E12>79,"A-",IF(E12>74,"B+",IF(E12>64,"B-",IF(E12>60,"C+",IF(E12>57,"C",IF(E12>54,"C-
",IF(E12>49,"D",IF(E12>0,"F",3.3)))))))))
Quality points is calculated using formula :
=[@[Credit Hour]]*[@[Grade Value]]
GPA is calculated using formula :
=SUM(Table1[Quality Points])/SUM(Table1[Credit Hour])
Total credit hours of each semester is calculated using formula :
=SUM(Table1[Credit Hour]) and same formula for 2nd semester
Total Quality Points of each Semester is calculated by using formula :
=SUM(Table1[Credit Hour]) and same formula for 2nd semester
CGPA Calculation P rocess
1

Accumulating GPA - Calculate individual GPA for each semester by following the same steps as
described earlier for each semester's courses.

Average Calculation - Once GPAs for each semester are calculated, find the average of these GPAs to
determine the overall CGPA.

1 Accumulating GPA
Calculate individual GPA for each semester by following the same steps as described
earlier for each semester's courses.

2 Average Calculation
Once GPAs for each semester are calculated, find the average of these GPAs to
determine the overall CGPA.
The Quality points for each semester
is the product of Grades Values and
Credit Hours
Quality Point Accumulation
Quality points = [@[credit
hour]]*[@[Grade Value]] Unders tanding Quality P oints - Quality points are assigned to courses
based on the obtained grades for the course and the credit hours
associated with it. Understanding how quality points are assigned is
crucial for accurate GPA and CGPA calculation.

Calculating Total Quality P oints - After assigning quality points to each

course, the total quality points are calculated by summing up the quality
points obtained for all courses.
 Calculations of CGPA for Two Semesters

The above picture shows the calculation for CGPA performed on Excel
Final Result Interpretation
Interpreting GPA - The GPA reflects the academic performance of a student in a particular semester,
providing insights into their overall achievement across all enrolled courses.

Understanding CGPA - The CGPA serves as an overall indicator of a student's academic performance
across all semesters. It represents a comprehensive view of the student's achievements throughout their
academic journey.