## Calculating exam results : pass, fail, compensation pass

Published on April 6, 2015 by in Excel | Advanced Lessons

I recently got a request through LinkedIn from someone I met at a conference who wanted to know how she could do the following..

She wanted to calculate exam results given the following parameters.

” If they do not fail any module but can now have up to a maximum of 3 modules that falls between 35 and <40 they can pass by compensation, my question is how do I get excel to understand it is up to 3 and no more. For example, if they have not failed any modules and have 3 or less modules that come in between the above they would ‘Pass by Compensation’, if they have not failed any modules but have 4 or more between 35 and <40 they fail. But if they fail any module, they fail regardless. ”

This required a combination of IF/And/CountIfs.

You can download the file here…Compensation pass fail question

And in this video I explain how I came up with the formula.

## Tables and Structural References or how to do a lot less highlighting when creating formulas

Published on October 7, 2013 by in Excel | Advanced Lessons, Excel | General

One of the issues I come across frequently in class are people who have to work with large amounts of data. That often means a Lot of scrolling and selecting of lists of data and I have often wondered how I could make this easier for people. I have found an answer with structural references. In this example, I have decided to use the SUMIFIS function to illustrate this.

The advantages of using structural references are:

1. Less scrolling and re-selecting..you enter the headings once and that’s it.
2. If you add/change data in the table, it’s automatically picked up by the function – you don’t have to go back and re-select.
3. When you look at the formula, it’s easier to follow (both for you and for others)

You can view the video below and the file is here: Structural References practice file

### Instructions

• Convert your list to a table (click in list, Ctrl and T)
• You will now see a new Ribbon
• On the left, rename the table – in the video below I have chosen sales
• Set up your parameters. In this example I used the following:
 Product County Classification Carrots Galway Services
•  As always, click where you want the answer to go.
• Start typing the following: =sumifs(
• Then type in Sales (the table name) . This will now appear in a dropdown list
• Double click on Sales, type [
• You will now see a list of the headings e.g. Product, County, etc
• Double click on Product
• Click on I4 (the cell containing Carrots). Press ,
• Repeat these steps for the County and Classification.
• Press ) at the end to complete the function.

## DATA TABLE | QUICKLY CREATE A LARGE TABLE OF VALUES

Published on October 23, 2012 by in Excel | Advanced Lessons, Excel | General

In this blog posting, I’m going to show you how to use a feature in Excel to create a large table of values. The feature is called Data Tables and I’m going to use the PMT function with it to show you how to quickly create a large table of  loan repayment values.

Here is the file for you to practise on.

Here is the video.