Formula Query (University Results Spreadsheet)

[ExcelNew7]

Hi,

Please may someone write a formula for me for the following?

In cell B17, I have a percentage. It is calculated by a sum function of 10 percentages. In cell C17 I want it to say '1st' if the percentage is 70% or higher, I want it to say '2:1' if the percentage is 60-70%, I want it to say 2:2 if the percentage is 50-60%, I want it to say '3rd' if the percentage is 40-50%, I want it to say 'Fail' if the percentage is less than 40%, unless the cell is 0% in which case I want it to say 'Result Pending'. The table where the data is is A6:K15. If for example A6 is filled out, but B6:K6 is empty, I want C17 to read ‘Result Pending’ regardless of what percentage is displayed in B17, and same for if A7 is filled out but B7:K7 is empty etc. for all rows 6-15. This must be a single formula that I could put in cell C17 please.

Thank you

 

[SanjayGMusafir]

Check this and revert -

=IFS(OR(Counta(B6:K6)=0,Counta(B7:K7)=0),"Result Pending",B17>=0.7,"1st",B17>=0.6,"2:1",B17>=0.5,"2:2",B17>=0.4,"3rd",And(B17<0.4,B17<>0),"Fail",True,"Result Pending)