+ Reply to Thread
Results 1 to 3 of 3

How to calculate pass/fail percentages entered on a spreadsheet?

  1. #1
    Jenna
    Guest

    How to calculate pass/fail percentages entered on a spreadsheet?

    I am trying to set up a spreadsheet with driving test results including one
    column to say pass one to say fail and calculate the percentage of each on a
    monthly basis. I can enter the info but don't know how to calculate the
    percentages

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi

    I've just assumed the pass and fails are in Col A.


    I then put this formula in B1 to Count the number of passes and divide by the number of Pass or Fails

    =SUMPRODUCT(--($A$1:$A$1000="pass"))/COUNTA(A:A)

    Same formula to find Fails

    =SUMPRODUCT(--($A$1:$A$1000="fail"))/COUNTA(A:A)

    Then Format cells as %

    VBA Noob

  3. #3
    Dave Peterson
    Guest

    Re: How to calculate pass/fail percentages entered on a spreadsheet?

    I don't think that wild cards will work like that.

    But if Pass (or Fail) is the only word in the cell:
    =SUMPRODUCT(--($A$1:$A$1000="fail"))/COUNTA(A:A)
    will work ok.

    And if pass and fail are included in longer strings:

    =SUMPRODUCT(--ISNUMBER(SEARCH("fail",$A$1:$A$1000)))/COUNTA(A:A)

    will look within each cell.

    (Similar formulas for Pass.)



    VBA Noob wrote:
    >
    > Hi
    >
    > I've just assumed the pass and fails are in Col A.
    >
    > I then put this formula in B1 to Count the number of passes and divide
    > by the number of Pass or Fails
    >
    > =SUMPRODUCT(--($A$1:$A$1000="*pass*"))/COUNTA(A:A)
    >
    > Same formula to find Fails
    >
    > =SUMPRODUCT(--($A$1:$A$1000="*fail*"))/COUNTA(A:A)
    >
    > Then Format cells as %
    >
    > VBA Noob
    >
    > --
    > VBA Noob
    > ------------------------------------------------------------------------
    > VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
    > View this thread: http://www.excelforum.com/showthread...hreadid=568630


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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