Share via

Count Average Compliance if N/A is an option

Danielle Kemper 0 Reputation points
2025-12-10T17:13:35.61+00:00

I have a spreadsheet that I need to count the average compliance of D5:L5. So I need the average of YES versus the total but there are 2 cells that have N/A as an option. Currently if I answer Yes to all cells but N/A in the 2 that have that option, it's showing the compliance of 78%, but it should be 100%. The current formula I have in that cell is: =IF(COUNTIF(D5:L5,"YES")+COUNTIF(D5:L5,"NO")+COUNTIF(D5:L5,"N/A")=0,"N/A",COUNTIF(D5:L5,"YES")/(COUNTIF(D5:L5,"YES")+COUNTIF(D5:L5,"NO")+COUNTIF(D5:L5,"N/A")))

Can you tell me how to make it calculate correctly?

Microsoft 365 and Office | Excel | For education | Windows
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-12-10T17:13:46.89+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    To correctly calculate the average compliance while accounting for the N/A options, you can modify your formula to exclude the N/A cells from the denominator. This way, the average will be calculated based only on the cells that have either YES or NO responses. Here's a revised version of your formula:

    =IF(COUNTIF(D5:L5,"YES") + COUNTIF(D5:L5,"NO") = 0, "N/A", COUNTIF(D5:L5,"YES") / (COUNTIF(D5:L5,"YES") + COUNTIF(D5:L5,"NO")))

    In this formula:

    • The denominator now only counts the YES and NO responses, effectively ignoring the N/A responses.
    • If there are no YES or NO responses, it will still return "N/A".

    This adjustment should give you the correct compliance percentage, which would be 100% if all applicable cells are marked as YES, regardless of the N/A options.

    1 person found this answer helpful.
    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Ashish Mathur 100.9K Reputation points Volunteer Moderator
    2025-12-10T23:26:09.8433333+00:00

    Hi,

    Does this work?

    =countif(D5:L5,"Yes")/countif(D5:L5,"<>N/A")

    The N/A should not be the result of a formula. It should be hardcoded.

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.