Cell Formatting Help

[cionnaith]

Hi.

I am trying to make a cell return a blank (instead of a zero) and am having a problem. I have three cells that can have either "yes" or "no" entered in them. I need to calculate the proportion of total yes's from the three. Another cell (in column L) that returns values for the three entry cells are formatted as follows:

=IF(J21="Yes",1,IF(J21="No",2,""))

so that if there is no selection, a blank cell is generated in the column L cell instead of a zero, which is perfect. What I need to do is to calculate the proportion of 1's from the three column L cells that generate 1's, 2's or an empty cell in another cell, but I need that cell to return an empty cell if there are no entries in any of the three "yes" or "no" input cells. Right now that cell has:

=COUNTIF(L19:L21,"=1")/3

Any help on how to get this to return an empty cell, instead of a zero, if all of the cells are empty, would be most appreciated.

 

[mole999]

you could start by using

=COUNTIF(D5:D9,"Yes")
=COUNTIF(D5:D9,"No")

modified to suit and avoid the multi IF's

 

[AlphaFrog]

Try something like this...
=IF(COUNTIF(L19:L21,1)=0,"", COUNTIF(L19:L21,1)/3

 

[cionnaith]

Thank you so much for the replies folks. That did it Frog...thank you, thank you!!