MIS Systems Thread, SIMS.net Calculating the highest of several results in Technical; Is there any way I can use a formula column in SIMS to return the highest result for each pupil ...
1. ## SIMS.net Calculating the highest of several results

Is there any way I can use a formula column in SIMS to return the highest result for each pupil from 2 columns. I have been messing about with the MAX formula column but can't seem to get it to work with that.

2. Hi @Xeba37

The MAX formula should work, you just select the columns to look at, then enter the rank (i.e. 1 for best) in the "Max" box. If you're doing this with grades, check that the points are correct for the gradesets those aspects are linked to and finally, remember to hit "Calculate" on the marksheet

3. ## Thanks to LosOjos from:

Xeba37 (13th December 2013)

4. Remember that if you use a MAX formula against a set of grade columns, the formula will return the points score value of the grade, not the grade itself (eg if the highest grade is a C you'll get 40). You'll then need to use a 'marks to grade' formula to convert the points score back into its grade.

5. ## Thanks to davidpott from:

Xeba37 (17th December 2013)

6. Originally Posted by davidpott
Remember that if you use a MAX formula against a set of grade columns, the formula will return the points score value of the grade, not the grade itself (eg if the highest grade is a C you'll get 40). You'll then need to use a 'marks to grade' formula to convert the points score back into its grade.
Yeah I was aware of that but have not figured out how to convert it back without using 2 'Nested if then else' formula columns which seems like a long way round. I think I'd be quicker doing la lookup in Excel and importing my converted results than going down that route.

7. Originally Posted by Xeba37
Yeah I was aware of that but have not figured out how to convert it back without using 2 'Nested if then else' formula columns which seems like a long way round. I think I'd be quicker doing la lookup in Excel and importing my converted results than going down that route.
Doesn't the "marks to grade" formula work?

8. Originally Posted by LosOjos
Doesn't the "marks to grade" formula work?
I couldn't see it on my list of formula but I have seen it now on the 'add columns for data entry using formula' rather than data review. Will see how I get on with that. Thanks

9. Forgive me for being an idiot, but how can I make the marks to grade column work when it seems to want an aspect selecting. I have used a MAX column to pick out the highest grade so far so my APS is in that.

10. The aspect it is asking for is the aspect that will contain the final, converted, grade. For example if you have set up an aspect to hold the highest grade out of a range of grades, then that's the grade you should enter here. It will then ask you to identify the column that contains the marks that need converting. Point it at the column that holds the MAX formula and it should work! Marks to grade formulas take a bit of getting used to but they do save a lot of time and effort in Excel.

David.

11. ## Thanks to davidpott from:

Xeba37 (19th December 2013)

12. I ended up doing a lookup in Excel and importing it, I think because I had so many aspects to deal with it was quicker that way anyway. Thank you though, I will bear that in mind for future work.

SHARE: