+ Reply to Thread
Results 1 to 17 of 17

End Value for Set of Cells with Y/N answers

  1. #1
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    End Value for Set of Cells with Y/N answers

    Hello,
    I need to return a score for a set of Y/N/Not Applicable answers and I just can't figure out the formula.
    There are a total of 37 questions.
    The total score should add up to 100.
    Only answers "Y" and "Not Applicable" should count.
    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: End Value for Set of Cells with Y/N answers

    Like this ...

    =COUNTIF($B$2:$B$38,"Yes")*100/37

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: End Value for Set of Cells with Y/N answers

    try:
    =COUNTIF(B2:B38,"Yes")*100/(37-COUNTIF(B2:B38,"No"))
    and
    =COUNTIF(B2:B38,"Not Applicable")*100/(37-COUNTIF(B2:B38,"No"))
    and SUM(these two cells)

  4. #4
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    Re: End Value for Set of Cells with Y/N answers

    Hi Puocam,
    Your formula works to count the "Yes" answers, but it does not count for "Not Applicable". The formula must count both.
    So for example, when I have one Yes answer the result is 2.7, and if I choose a Not Applicable, it should add to 5.4.
    Is there a way to tell the formula to count for both Yes and Not Applicable?
    Thanks

  5. #5
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    Re: End Value for Set of Cells with Y/N answers

    Hi Sandy,
    Thanks for the quick response.
    Sorry, but the formulas did not work. It was not returning a value.
    Thanks

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: End Value for Set of Cells with Y/N answers

    See attachment
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: End Value for Set of Cells with Y/N answers

    Here is ...

    =(COUNTIF($B$2:$B$38,"Yes")+COUNTIF($B$2:$B$38,"Not Applicable"))*100/37

    Or:

    =SUM(COUNTIF($B$2:$B$38,{"Yes","Not Applicable"}))*100/37

  8. #8
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    Re: End Value for Set of Cells with Y/N answers

    Hi Sandy,
    Sorry...should have been more clear. The formula needs to be a single formula within the nested in cell B39, next to score. Not separate cells. Thanks

  9. #9
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    Re: End Value for Set of Cells with Y/N answers

    Hi Phuocam,
    Thanks! Almost there. It seems to be adding the Yes and the Not Applicable, but it is not adding up to 100. Is adding up to 97.3. Any ideas why?

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: End Value for Set of Cells with Y/N answers

    I assumed that all questions must have answers so if someone does not answer (empty cell) then you will see the result of less than 100. If all 37 questions will have answers - the result will be 100

    so simply sum
    =SUM(COUNTIF(B2:B38,"Yes")*100/(37-COUNTIF(B2:B38,"No")),COUNTIF(B2:B38,"Not Applicable")*100/(37-COUNTIF(B2:B38,"No")))
    or
    easier
    =SUM(COUNTIF(B2:B38,{"Yes","Not Applicable"})*100/(37-COUNTIF(B2:B38,"No")))
    Last edited by sandy666; 03-03-2017 at 08:04 PM. Reason: 2nd formula added

  11. #11
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    Re: End Value for Set of Cells with Y/N answers

    Never mind Puocam...
    My mistake. It's working.
    Thank you

  12. #12
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    Re: End Value for Set of Cells with Y/N answers

    Hi Sandy,
    Phuocam's formula seems to be working.
    Thank you so much.
    Ruben

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: End Value for Set of Cells with Y/N answers

    You are welcome

    btw...
    If the basic problem is solved
    - it's always a good practice to show respect to the person(s) who have helped you
    - is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
    - mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,

    Thanks

  14. #14
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: End Value for Set of Cells with Y/N answers

    You are welcome.

  15. #15
    Registered User
    Join Date
    11-15-2016
    Location
    Maryland, US
    MS-Off Ver
    2016
    Posts
    29

    Re: End Value for Set of Cells with Y/N answers

    You got it Sandy. Thanks

  16. #16
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: End Value for Set of Cells with Y/N answers

    No problem

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: End Value for Set of Cells with Y/N answers

    Just for fun, try attachment
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Creating a Text value from a range of cells dependant on the answers.
    By Tazyote in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-15-2014, 02:40 PM
  2. [SOLVED] IF OR different answers
    By bubs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2013, 10:19 AM
  3. [SOLVED] Counting by looking in 2 cells for answers
    By moley165 in forum Excel General
    Replies: 2
    Last Post: 06-27-2012, 06:40 AM
  4. [SOLVED] Count the number of matches in a row of one word answers with a row of correct answers
    By flammer4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2012, 01:17 PM
  5. Answers YES and NO
    By Michelle66 in forum Excel General
    Replies: 3
    Last Post: 12-17-2011, 10:00 PM
  6. IF function: extending to read multiple cells/answers
    By balcony in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2011, 02:08 PM
  7. cells with the same answers
    By jerskine in forum Excel General
    Replies: 3
    Last Post: 08-01-2008, 06:27 PM
  8. Replies: 8
    Last Post: 03-29-2005, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1