Results and expressing as percentage

jblunda

In my forms, I have several questions that can result as either PASS, FAIL or N/A. I want to express only the FAIL cells as a percentage over the entire row results. I've been using this formula:

=COUNTIF(CellName1@row:CellName2@row, CONTAINS("PASS", @cell)) / 7

The only way I've been able to make this work is by counting all of the PASS cells and dividing the result by the number of cells (7) I want to compare it to.

This has drawbacks:

-As long as all of my answers are either Pass or Fail, the percentage expresses correctly. But, when I choose N/A, the expression fails.

-I also can't express this result WITHOUT counting the PASS cells.

Ideally, I want this formula to say something such as "Count all cells in row that have the word "FAIL" in them, compare that to the total number of cells I wanted to check, and tell me the total as a percentage."

How do I accomplish this?

Samuel Mueller

@jblunda try this formula

=iferror(countif(CellName1@row:CellName2@row, CONTAINS("FAIL", @cell))/ countif(CellName1@row:CellName2@row, not(CONTAINS("N/A", @cell))),"")

jblunda

I don't get the expected result, but I found a way to make it work:

=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell))),"")

When I use FAIL, I get the reverse answer of 80% (between 5 cells). If I switch that to PASS, I get my result.

Thank you, and I had no idea I could just write another argument in the denominator!

Samuel Mueller

Gotcha! I though you were looking to see the percentage of fails. Glad you got it working

Samuel Mueller

You still have a syntax error which is why you are getting unparseable it should look like below

=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")

Samuel Mueller

I think I missed a parenthesis on the other one I sent, my apologies

This should work

=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")

Samuel Mueller

@jblunda try this formula

=iferror(countif(CellName1@row:CellName2@row, CONTAINS("FAIL", @cell))/ countif(CellName1@row:CellName2@row, not(CONTAINS("N/A", @cell))),"")

jblunda

I don't get the expected result, but I found a way to make it work:

=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell))),"")

When I use FAIL, I get the reverse answer of 80% (between 5 cells). If I switch that to PASS, I get my result.

Thank you, and I had no idea I could just write another argument in the denominator!

Samuel Mueller

Gotcha! I though you were looking to see the percentage of fails. Glad you got it working

jblunda

A new question that can be added to this discussion:

Let's say I want the formula to add those cells that ONLY have either PASS or FAIL in them, and IGNORE them if they are blank or contain "N/A". How do I add a secondary condition in the denominator that address more than one criteria to NOT consider? Do I add another NOT argument?

Samuel Mueller

@jblunda yeah you can add as many critera's as needed so something like

=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, <>""),"")

the <>"" is essentially the cell is not blank. I think you could also replace that with not(isblank(@cell)) if you wanted.

jblunda

No luck, yet: "##INCORRECT ARGUMENT SET"

I also tried adding the not argument at the back:

=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell)), not(isblank(@cell)),"")

I also played with syntax to see if there was a simple missing () and added some spaces, but nothing panned out.

Samuel Mueller

@jblunda the formula always has a (range, critera) format, you are missing the range for your last criteria

=iferror(countif(cellname1@row:cellname2@row, CONTAINS("PASS", @cell))/ countif(cellname1@row:cellname2@row, not(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, not(isblank(@cell)),"")

cellname1@row:cellname2@row is your range, and

not(isblank(@cell)) is the critera,

and you would repeat that for each criteria.

jblunda

I've found a workaround, mainly because I'm stuck with an error I don't understand:

#UNPARSABLE:

=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell))), (cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), ""))

The goal: count all cells (within a specific range) with the word "PASS". Divide that number by a number gained by counting all cells (within a specific range) that have either PASS, FAIL or N/A. Express answer as percentage.

Its the denominator I can't understand. So, I simplified:

=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")

This formula works for what I need. Am I missing a () somewhere?

Samuel Mueller

You still have a syntax error which is why you are getting unparseable it should look like below

=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")

Samuel Mueller

I think I missed a parenthesis on the other one I sent, my apologies

This should work

=IFERROR(COUNTIF(cellname1@row:cellname2@row, CONTAINS("PASS", @cell)) / COUNTIF(cellname1@row:cellname2@row, NOT(CONTAINS("N/A", @cell)), cellname1@row:cellname2@row, NOT(ISBLANK(@cell))), "")