Forum Discussion

Adam Patterson's avatar
Adam Patterson
Copper Contributor
May 23, 2018

Excel Formula help

I have made an excel tracking document to track learners exams and assessments. I have set cell to turn colour depending on the percentage they achieved. What I would like to happen if the pass criteria is met in cells which is met i would like the cell to the far left (which will have the learners names) automatically turn a colour. The problem is that various inputs to indicate a Pass. 60% or greater will indicate a pass, also the word "Pass" or "RPL" would also indicate a pass. does anyone know if this is can be done?

formula
Formulas & Functions
  • Tomasz Kocur's avatar
    Tomasz Kocur
    Brass Contributor
    May 23, 2018

    Hi Adam 

    The issue is with merged cells and numbers in the cells between,

    you can sort it by the formula in the conditional formatting please see attached file 

     

    =SUM(COUNTIFS($B3:$S3,">=0.6",$B3:$S3,"<1"),COUNTIF($B3:$S3,"Pass"),COUNTIF($B3:$S3,"RPL"))=9

     

     

     

     

    • Adam Patterson's avatar
      Adam Patterson
      Copper Contributor
      May 24, 2018

      Thanks Tomas that is great and does the job perfectly. I don suppose if you have time to briefly explain what the formula means as i would love to learn how it works and be able to adapt this code to work with other tracking documents.

       

      Adam

Resources