Formula question

D

danlinksman

I have a spreadsheet that list a planned completion date and then has an
actual completion date. I have a formula that places a Y or N depending if
production met the contractual due out date or not. What I want to do is
calculate my % effective based on the "Yes" and "No" criteria. Is there a
way?
 
R

Rowan

Assuming your Y and N results are in the range H2 to H21 then one formula is:

=COUNTIF($H$2:$H$21,"Y")/COUNTA($H$2:$H$21)

Format as percentage.

Regards
Rowan
 
D

danlinksman

Sorry, one other question if you have time. How do I adjust for no
information in cells. Example H1..H21 is involved in the formula however I
have no data in H13..H21 and it is affecting the % effective. How can I get
the formula to ignore blank cells?
 
R

Rowan

CountA will not count blank cells but will count cells which have a formula
returning "".
One way around this I suppose would be:
=COUNTIF(H1:H21,"Y")/(COUNTIF(H1:H21,"Y")+COUNTIF(H1:H21,"N"))

Hope this helps
Rowan
 
R

RagDyer

You could try this:

=COUNTIF(H1:H21,"Y")/COUNTIF(H1:H21,"?")

And format as %
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Top