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
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
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks