Excel/Excel Formula


Dear Edward

I was wondering if you would be able to advise me on the issues below:

1. I am trying to create a formula that will provide an automatic classification of degrees based on students’ results in 18 subjects across 3 years. The weightings are as follows:

Year 1 – Six subjects – Weighting is 10% of degree
Year 2 – Six subjects – Weighting is 20% of degree
Year 3 – Six subjects – Weighting is 10% of degree
Year 3 – Six subjects – Weighting is 60% of degree

The degree classifications are as follows:

70+          First Class
60+          Second Class, First Division
50+          Second Class, Second Division
40+          Third Class
Below 40     Fail

2. I also wondered if a formula could be applied to distinguish grades that are awarded by predominance. The rule for predominance is explained below.

Honours classification and the Predominance Rule
Where the percentage mark is within three percentage points of a higher classification and at least half the weighted module marks are in the higher classification, the higher classification shall be awarded using the Predominance Rule.

Many thanks in advance

I spotted this question in the question pool - which means the person you originally asked is unable or unwilling to help.

I'm not sure that you have explained quite enough to be able to get an answer - you want degrees based on results in 18 subjects across three years, but you refer to weighting over 4 (well, year 3 twice - not sure if that is a typo) and it's not clear what the weighting should do.

That said, the basic question sounds as though it is simple enough because logical rules (which I don't yet understand!) are being applied - logic turns well into formulas.

More than happy to help further - a sample sheet might help as you could indicate a little clearer here the sort of data you have and the data you need to get out.

my email is support@flameenterprises.co.uk
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 About.com. All rights reserved.