Percent complete when using date & checkbox field types

11/30/21
Accepted

I have a task sheet that lists 7 items per row that need to be completed. I would like to calculate the percent complete of these tasks, but am running into trouble with my formula.

I have 4 checkbox columns, and 3 date columns. I may not use the check or a date in each, some will have "NA" or text, depending on the situation, not sure if this is what is messing with my formula?

Basically, I want to calculate the percentage based on which cells are not blank. The date fields seem to work fine, but the check boxes appear to read as 'not blank' whether they are checked or not. See screen shot.

This is the formula I have attempted to use:

=(7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, ""))) / 7


Tags:

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Does this work?

    =(7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, OR(@cell = "", @cell = 0)))) / 7

    thinkspi.com

Answers

  • Devin LeeDevin Lee ✭✭✭

    Try this

    =1 - ((7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, =1) + COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, ISDATE(@cell)))) / 7)

  • Natalia KataokaNatalia Kataoka ✭✭✭✭✭

    Hi @Devin Lee , that formula almost works, though I am still running into issues if there is text in a cell vs a date/check that matches the field type.

    For example, all rows below the green line should be 100%, except for the 2nd and 7th rows.


  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Does this work?

    =(7 - (COUNTIF([Request Proposal inclusion]@row:[Lab/Equip Rates on JIS]@row, OR(@cell = "", @cell = 0)))) / 7

    thinkspi.com

  • Natalia KataokaNatalia Kataoka ✭✭✭✭✭

    That worked, thanks!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.