Excel formula help please!

[ProbablyLate]

I'm trying to set up a spreadsheet for students marks. They have completed four tasks which they either get an A, B or C in. Their overall mark is the lowest that they get in any task (not a system I chose but that's another story!)

I would like to set up a spreadsheet where when I input the grades for the four tasks the overall mark automatically comes up. So if there is a C in any of the range of boxes, it will come up with a C, if there's a B but no C, they'll get a B, and if it's all As they'll get an A.

Can anyone help me with a formula for doing this? Thanks

If(or(a=‘C’,b=‘C’,c=‘C’),’C’,if(or(a=‘B’,b=‘B’,c=‘B’),’B’,’A’)))

This is broadly a way to do it. I’m on my phone so was struggling with speech marks and haven’t tested it so haven’t the right number of ) at the end. It only needs 2

An if statement sound like your best bet.
=if("cell reference for c", "", "cell reference for c", if(... repeat but replace Crefernce with B

Yeah... what Griffin said. Mine only checks if its empty!

Thanks @Idontgiveagriffindamn, where do I put in the range of cells I want it to look at?

So if I have my table set up so their four grades for the units are in cells A1, B1, C1, and D1, and want the overall grade in E1 how would I add that in?

Having looked again are you a, b, and c the cell references and I have to define each cell individually rather than the range?

Actually repeat the formula with A as well and then the last but can be ‘no grade found’ which will account for nulls.

@ProbablyLate

Having looked again are you a, b, and c the cell references and I have to define each cell individually rather than the range?

the small a, b, and c are the cell references in my example. This is just for the example purposes and you’ll need to change to reflect your spreadsheet When you create the formula start on the top row and drag down and it will do all of the rows.

Honestly I’d do a lookup table which translates ABC into 321, then takes the minimum value from any test then translates back into ABC. More steps but avoids a hideous nested if statement.

Thanks @Idontgiveagriffindamn , I've got it working now

@NeverTalkToStrangers I didn't think of that, I'll bear it in mind for next time!

CHAR(MAX(code(A2),code(B2),code(c2),code(D2)))

Convert the letter to a number, find the max number, convert back to a letter.

Oh that’s super-elegant @Daftasabroom. I suspected there would be built-in functions to translate letters to numbers automatically but haven’t ever had cause to use them myself.

God, I love a good Excel formula.

You have no idea how much joy this thread brings me (although I admit I have recently been being tempted to the MS Power BI dark side...).

@Idontgiveagriffindamn - I'm a bit late but "yep"

=IF(OR(A1="C",B1="C",C1="C",D1="C"),"C",IF(OR(A1="‘B",B1="B",C1="B",D1="B"),"B","A"))