Forum Discussion

Mark_reg's avatar
Mark_reg
Brass Contributor
Jun 21, 2018

Calculate Percentage of cell range of cells with value

Hello,   I wish to calculate the percentage of a range of cells (column), but only if they have a value other than '0' (some lazy conditional formatting on my part). It pertains to calculating stud...
Excel Desktop
Formulas & Functions
JKPieterse's avatar
JKPieterse
Silver Contributor
Jun 21, 2018
Can you share some sample data please?
  • Mark_reg's avatar
    Mark_reg
    Brass Contributor
    Jun 22, 2018

    Hi Jan Karel,

    Thank you for being willing to look at my problem. :)

     

    I hope this example makes sense. So if 10 classes happened the percentage of being present should be calculated based on 10 classes. If 15 classes happened the percentage should be based on that. etc. 

    Here P stands for Present, and A for Absent. 

     

    In my spreadsheet I will use 'A', 'P' and 'WN' (Without Notice). However, as all the cells that 'appear' blank will actually have a value of '0'  because the original cells have reference formulas, anything as not '0' could be counted as a 'lesson'.

     

    Does that make sense?

     

    Kind regards,

    Martin

     

    • Asghar Shah's avatar
      Asghar Shah
      Copper Contributor
      Jun 22, 2018

      Do you mean that one "p" is equal to 10%? if there are 9ps then the percentage should be 90%? if yes then we can solve this problem with the help of countif function.

      • Asghar Shah's avatar
        Asghar Shah
        Copper Contributor
        Jun 22, 2018
        This formula can be used:
        =COUNTIF(F6:F26,"p")*10&"%"

        the answer will be in this case 70%.

Resources