Look Who it Was Random video

Excel - Sakai Grade Book Sum When Not Blank - Part III of IV: Episode 1543

1,151 views · Published 12 April 2012 · 3:24 · Indexed 20 September 2026

Channel: MrExcel.com · 2012 · Education

Watch on YouTube

Microsoft Excel Tutorial: Calculating Total Possible Points for Non-Blank Cells in Excel | MrExcel.

Welcome back to MrExcel.com! In this episode, we continue our series on using Excel to create a live gradebook model. In the previous episodes, we covered a free macro to automate the process and how to get the points possible from the headings. Today, we tackle the most challenging part: calculating the total possible points for non-blank cells.

As we dive into this topic, we discover that Sakai has a rule that affects our calculations. If a student was not present for an assignment, Sakai leaves the corresponding cell blank instead of entering a 0. This means that the points for that assignment are not included in the total possible points. To account for this, we need to create a formula that checks for blank cells and adds a 0 if necessary.

To do this, we use the IF function and the ISBLANK function to check if the cells in the row are blank. If they are, we add a 0. If they are not, we use the number from row 2, which contains the points possible for each assignment. We use the $ sign to make sure the formula always refers back to row 2, regardless of which row we are in. This formula will return 29 values, so we use the SUM function to add them all up and get the total possible points.

This formula is called an array formula because it evaluates multiple cells and passes the results to the SUM function. To use it, we need to press Ctrl+Shift+Enter instead of just Enter. This will give us the correct result. We can then copy the formula down to the rest of the column and see that all the values match the ones in Sakai. This is a powerful formula that can save us a lot of time and effort in our gradebook calculations.

In the next episode, we will take this percentage score and use VLOOKUP to convert it into a letter grade. Thank you for watching and don't forget to join us for the next episode of the MrExcel Podcast. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Calculating total possible points for non-blank cells
(01:11) Creating a formula to calculate possible points per student
(02:02) Wrapping the formula in a SUM function
(02:25) Using Ctrl+Shift+Enter to evaluate the formula
(03:00) Converting percentage score to letter grade using VLOOKUP
(03:10) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Calculate total possible points
Convert percentage score to letter grade
Excel array formula
Excel gradebook data
Excel IF statement
Excel ISBLANK function
Excel SUM function
Excel VLOOKUP function
Learn Excel from MrExcel Podcast
MrExcel netcast
Sakai grade calculation rule
Sakai Gradebook Sum When Not Blank


Still working with the CSV Data from the Sakai Grade Book, Bill looks at Calculating the Total Possible Points from Non-Blank Cells. Do you need just the percentage scoring? Cannot figure out why your figures are so far off from the Sakai Total? In Episode #1543, Bill examines why this is happening and what you can do to quickly and effectively correct that issue. 'Livening' up your Grade Book. This is Part III of IV.

Formulas for the 'Grade Book Application' that Bill Jelen has created are from the book, "Excel For Teachers" [ http://www.mrexcel.com/teachers.html ] published by Holy Macro! Books. Learn more about the book "Excel For Teachers" by clicking here [ http://www.mrexcel.com/teachers.html ] to go to the "Excel For Teachers" information page.

...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE2010.html 

"The Learn Excel from MrExcel Podcast Series"

Visit us: MrExcel.com for all of your Microsoft Excel Needs!

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153353/

More from this channel