In this lesson we’re looking to make some significant progress in the layout and some of the formulae in our sheet. Click on the image below to see exactly what we’re looking to complete:
The first thing you might notice is that I’ve incorporated some formatting. At this stage all I’ve done is bold the column and row headings. In general, formatting should be done at the end because it saves you readdressing it throughout spreadsheet creation, but these headings are the final ones so go ahead and put all those bold headings in, except for the larger main one at the top!
The main heading is a combination of text that I’ve written, and parts of cells from Sheet “gv“. In order to combine regular text with functions you simply need to join them together with the “and” symbol (“&“), also known as an ampersand:
="St. Mary MacKillop College - Markbook - Yr1" & MID(gv.C4,1,1)
If you put that text into the now merged range of C1:S1, it will extract the Markbook Year from Cell “gv.C4” and “add” (or in programming terms, “concatenate“) it onto our “St. Mary MacKillop College – Markbook – Yr1” text.
This style of formula that uses functions and plain text is perfectly acceptable and a good way to have a cell output exactly what you want. You can have multiple ampersands in a formula so see if you can finish off that formula to display the correct title of our Markbook in the exact manner shown in the first screenshot. (Hint: In order to change the case of the course name, you will need to find a function that can do this!)
You may notice there are quite a number of merged cells…have a good look at the original screenshot and make sure you merge all the same cells that I have. If you miss any, some of my cell references in the later lessons may not make sense so make sure you spend time getting it correct.
The “Average” formula
Getting the average of a range of numbers involves simply using the AVERAGE function. So in Cell “E4“, we want to get the average of all the Task 1 results, i.e.
And this formula gives us the desired result but it doesn’t take into account the following two situations:
- What if there are no task results to average?
- What if there is no maximum score included for this task?
Situation 1: No Task Results
This isn’t too hard to address, there are actually a number of ways to do it but the one I’ve opted to use involves using the COUNTIF function which basically takes a range of cells and counts the cells that adhere to a condition you specify, so in our case we will count the blank cells, i.e.
COUNTIF ( E8:E37 , "" )
If that count comes back less than 30, then we must have a task result in that column, so that will deal with our first situation.
Situation 2: No Task Maximum
This simply involves checking if Cell “E5” has anything in it, we’ve created a formula like this before and it looks something like:
E5 <> ""
Then all that’s required is to combine it all together:
Notice I’ve used the AND function to combine the two situations just discussed and then I’ve encased the whole formula in an IF function so that an average is only inserted if it’s appropriate to do so.
So now you need to apply that average formula to all the other tasks. You’ll find when you autofill across merged cells that it will un-merge them, it’s no big deal, you’ll just have to re-merge them.
Okay, check and make sure that your sheet looks like the screenshot and once it does…let’s move on to the next set of problems needing our attention…