Forum Discussion

Kerry Trafton's avatar
Kerry Trafton
Copper Contributor
May 25, 2017

Please help with formula

Hi everyone,

 

I have a project plan spreadsheet that is color coded (Green, Yellow and Red) depending on the status of the task.  I wrote a formula to auto calculate the overall project status based on how many of each color were in the individual tasks, but there seems to be an error.  Instead of calculating the "if more than 30% is yellow then the project is yellow", if you change just one it turns the whole project yellow.  Here is the formula I am using:

=IF((COUNTIF(J10:J33,"R")/COUNTA(J10:J33))>10%,"R",IF((COUNTIF(J10:J33,"Y")/COUNTA(J10:J33:A))>30%,"Y","G"))

 

Please note the J cells the formula is referring to the column that has the color coding in it.

 

Thanks in advance!

Formulas & Functions
Need Help

3 Replies

  • Dibas Biswas's avatar
    Dibas Biswas
    Copper Contributor
    May 25, 2017

    The objective to achieve the color of Red, Green, and Yellow can also easily achieved by using conditional formatting. Just select the column for which color code on the basis of percentage and go to conditional formatting -> select rules -> Format cell that contains -> For cell value select in between of the value and set the percentage criteria and set the color you are looking for. Along with that, you can use user-defined color format to make any report look good.

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 25, 2017

    Hi Kerry,

     

    In your formula

    =IF((COUNTIF(J10:J33,"R")/COUNTA(J10:J33))>10%,"R",
       IF((COUNTIF(J10:J33,"Y")/COUNTA(J10:J33:A))>30%,"Y","G")
    )

    change

    COUNTA(J10:J33:A)  on COUNTA(J10:J33)