I'm working on a tracking sheet for quality reviews of work completed. I have a list of criteria to be met for which the entry can be either
X for not applicable. Each month a number of these reviews will be done on each person.
In order to evaluate trending data I came up with a formula to return a percentage of times an
N appears, for each criteria.
The sheet is set up as follows:
criteria review 1 review 2 criteria 1 |Y| |N| criteria 2 |Y| |Y| criteria 3 |N| |X| criteria 4 |X| |N|
I want to total the number of responses for each month and for each criterion which include a Y or N - basically to exclude all X responses from the total used to average.
What I came up with is this:
=(COUNTIF('Sheet 3'!B4:F4,"N"))/(COUNTIF('Sheet 3'!B4:F4,"Y")+(COUNTIF('Sheet3'!B4:F4,"N")))
The problem is where all criteria for a given month are
X, this formula returns a
#DIV/0! error. I've tried using an
IF statement to solve for that, but I keep getting either too many, or too few argument errors. I asked if the count of
N > 0, then run that formula. If not, show
0. That didn't work, I'm guessing because of how convoluted it all got.
Any suggestions? I'd like to avoid using additional columns to subtotal things if at all possible.