Calculate Semester Final Grade with VLookup, IF Function, and Conditional Formatting

A VLookup in excel is a function that “vertically looks up” a provided value and provides a corresponding lookup value to the right of it. VLookups are great for looking up additional information about a product based on a SKU number, UPC, or a unique ID number. They can also be used to lookup a grade letter based on the total points earned in a class.

In my spreadsheet below, I have created a sample grade sheet that assigns weights to each individual assignment. Download The Spreadsheet!

The midterm, final, and the group project are each worth 25% of the total grade. The remaining 25% of the total grade is divided among 10 quizzes throughout the semester. The points earned from each assignment is calculated by the following formula.

=(B2/100)*C5

The score earned from the assignment is divided by 100 to reach a decimal format and then it is multiplied by the weight of the grade. This will give you the points earned for each assignment which is located in column D. The sum of all the points earned is located in cell D15.

=SUM(D2:D14)

Conditional Formatting

Based on the value of cell D15 (total points earned), the background color will turn green if the total points earned is greater or equal (>=) to 70, or it will turn red if the total points earned is less (<) 70. This is possible with conditional formatting in the “styles” section of the “Home” tab.

NOTE: I am using Microsoft Excel 2016 to create this spreadsheet. The steps below may be different if you are using a different version.

First, you highlight the cell(s) that you want to change based on a certain condition and then click “Conditional Formatting”. Next, select “Highlight Cell Rules” and then “Less Than”. A popup box will show up. Under “Format cells that are less than” enter 70 and click the “with” drop down and choose “Custom Format”. Choose the fill tab and select the red color as the background. This will turn the cell red if the total points earned is below 70 which is a failing grade.

The same steps apply to turn the cell green when you have a passing grade, except this time, you will want to select greater than or equal to 70. Also, choose green for the background fill color.

Feel free to change some of the scores in the gray background so you can see the effect on the total points earned. If it is less than 70, the conditional formatting will turn the cell red.

VLookup Grade Based On Score

=VLOOKUP(D15,F2:G10,2,TRUE)

Values in Vlookup:

1. The first is the value we want to look up (lookup value)

2. Second is the range where the lookup value is.
Note: Lookup value should always be in the first column of the range

3. Third is the column of the range where the return value is located. In this case, we want the “Grade” which is column 2.

4. Lastly, TRUE for an approximate match and FALSE is for an exact match.
Note: TRUE is the default value

What Is Happening???

The VLookup Function is typed into cell F13 and automatically changes the grade based on the value of cell D15 (total points earned). Next, it uses the range F2 through G10 as the “grade key”. When it finds the matches the lookup value to the score, it returns the corresponding grade that’s in the 2nd column of the range F2:G10. Hopefully, it’s an A!

Try changing some of the scores in the gray background so you can see the VLookup do it’s job!

If Function Grade Based On Score

=IF(I13>=95,”A”,IF(I13>=90,”A-“,IF(I13>=87,”B+”,

IF(I13>=83,”B”,IF(I13>=80,”B-“,IF(I13>=77,”C+”,

IF(I13>=70,”C”,IF(I13>=60,”D”,”F”))))))))

This method of calculating your semester grade looks like more work, but it is actually easier as long as you are careful with your parenthesis. Just like math, if you open one you need to close it too. The “IF” function checks if the value is greater than or equal to 95. If that condition is true, it will display an “A”. If that condition is false, it will move to the next condition and so on. If none of the conditions are true, it will move to the default value. Sadly, the default value in this case is an “F”.

Again, try changing scores in the gray background so you can see the “IF” function in action!

Woah! Can I Have An Easier Example?!?

Yes! Your eyes may cross at first sight of the “IF” function above. I setup another “IF” function to print a motivational message base on the final grade.

=IF(D15>=70,”Awesome! You PASSED the class!”,”You FAILED! Better luck next year!”)

If the total points earned for the semester is greater than or equal to 70, then Excel will print “Awesome! You PASSED the class!”. If this condition is not met, total points earned are less than 70, and Excel will print “You FAILED! Better luck next year!”.

Awesome! You have learned to calculate your final semester grade with Vlookups, if functions, and conditional formatting.

Add Navigation Dropdown Menus in WordPress with BootStrap v4

I decided to consolidate some of the links in my navigation bar at the top (header) of my site. This eliminates the need for individual web pages that are full of links. Essentially, you will get to where you want to go quicker with less scrolling. Mobile device users will appreciate this the most! They can quickly tap the drop down menu of their choice and then tap one of the links the drop down menu provides. I decided to create a drop down menu for my “work samples” link and for my “background” link. In my custom WordPress theme, the current navigation bar is located in the header.php file.
View the source code of a page with Ctrl + U. You will see the “Navigation” section of the page or you can refer to the pic below.

The area of interest here is the div with the “dropdown show” class. Another div drop down menu is located in this div container. The main link is set to “” so the browser will remain on the page if clicked. When clicked, three links will show up below and will be available to select. The two div containers are then closed. All of this is contained within a list class “nav-item” for BootStrap to change it accordingly.

The drop down menu is in effect and will look great on any device!

Migrate website to WordPress Content Management System

I migrated my website to the WordPress CMS (Content Management System) so it would be easier to add new web pages and I enjoy a good challenge. Beforehand, I would have to code each static page in HTML and style it to my liking. When there are only a couple of pages it is no big deal, but if you are constantly adding content to a website, it is quicker to use a content management system like WordPress, Drupal, or Joomla. Writers and editors that don’t know HTML, or just don’t want to deal with it, can hit the ground running and add new website content fast. In a nutshell, you login to your site and write your articles in the feature rich WordPress editor. It is easy to add media (e.g. pictures, videos) and arrange them however you like on the page. If you aren’t ready to post your article yet, you can save it as a draft and publish it later. This is great when an editor needs to proofread the article.

HOW DID I DO IT?

The first thing to do: Install WordPress to your web server.

*If you would like to work on it locally before making it “live”, you will need to setup a local web server (WAMP, MAMP, or LAMP) as I did. This will allow you to make changes offline. Otherwise, follow the directions below to setup to set it up online and edit your web site on the fly.

Some web hosts have a one-click setup from the administrative panel. If you don’t have this option, or want more options, download WordPress to your computer.

Unzip the downloaded file to a folder on your hard drive.

Make sure you have a database setup on your web host and take note of the credentials.

Edit the file wp-config.php and add your database information.

Upload WordPress to your web server via FTP.

Run the WordPress installation script by accessing the URL in your browser. (http://www.example.com/wp-config.php)

You now have WordPress installed on your web server and it can be edited at http://www.example.com/wp-admin

A standard HTML file has a header, content section, sidebar, and footer. A WordPress page, or post, pulls dynamic content from each section to create each web page in PHP. This eliminates redundant header and footer content, the pages will load faster, and they will rank higher on Google. The idea is to split up the content from one of my static web pages to recreate the structure and look of the original website.

In the WordPress editor I created a page for each of my original web pages.

In addition, I applied my original style.css file to my custom WordPress theme. The navbar links to my pages needed to be changed to <a class=”dropdown-item” href=”/wordpress/index.php/examplepage”>. I installed WordPress in a subfolder on my domain (http://Ray-Sanchez.com/wordpress/) so I need to begin with “/wordpress/” in my links.

Images needed to be uploaded through the “Add Media” section of the WordPress editor and linked accordingly. I added the class “img-fluid” to each picture so that they would respond to different size devices.