Simple yes/no/N/a Survey form

C

cyberbaba

New Member
Joined
May 27, 2008
Messages
8
Hello All,
I tried the Search feature and did not find any similar threads so here goes. I need to create a simple 10 question survey with the options yes/no/na. I cannot seem to get the form to work right it goes crazy if N/A is selected and does not give the right score.

In short out of the 10 question 8 are N/A and the other two 1 is Yes and the other No logically the score shud be 50%.

Any help much appreciated. Note i am not using an VB forms just the form in regular cells in Excel.

Thanks
Nik
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
<table class="MsoNormalTable" style="width: 250pt; margin-left: 4.55pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="333"> <tbody><tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77">
A<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
B<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
C<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
D<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
E<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 1<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> Total<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
90%<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 2<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
No<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
0<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 3<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 4<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 5<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 6<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 7<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 8<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 9<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in 5.4pt; width: 58pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="77"> Question 10<o:p></o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
Yes<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> <td style="padding: 0in 5.4pt; width: 48pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64"> <o:p> </o:p>
</td> </tr> </tbody></table>
In column B I used Data Vailidation to make sure you only had "Yes"/"No"/"N/A".
In Column C I used the formula "=IF(B1="Yes",1,IF(B1="No",0,N/A))" and then copied it to the rest.
Column E I used the formula "=SUM(C1:C10)/COUNT(C1:C10)"

I think this is what you are looking for. I could be wrong. You could merge some of the cells to make it cleaner but that gets too complex.
 
Upvote 0
With a small correction to xld's formula and changing the y's and n's to yes's and no's:

=COUNTIF(A1:J1,"yes")/(COUNTIF(A1:J1,"yes")+COUNTIF(A1:J1,"no"))

Format as percent:

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>N/A</TD><TD>N/A</TD><TD style="FONT-FAMILY: Verdana">Yes</TD><TD>No</TD><TD>N/A</TD><TD>N/A</TD><TD>N/A</TD><TD>N/A</TD><TD>N/A</TD><TD>N/A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">50.00%</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=COUNTIF(A1:J1,"yes")/(COUNTIF(A1:J1,"yes")+COUNTIF(A1:J1,"no"))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

and could be shortened to:
=COUNTIF(A1:J1,"yes")/SUM(COUNTIF(A1:J1,{"yes","no"}))

and to avoid #DIV/0!
=COUNTIF(A1:J1,"yes")/MAX(1,SUM(COUNTIF(A1:J1,{"yes","no"})))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

gallen
  • Question
Macro Enabled Workbooks Suddenly Corrupting Regularly.
Replies
5
Views
947
Joe4
Joe4
Z
  • Question
Massive Nested IF Statement Help Needed
Replies
1
Views
113
Zutalores
Z
L
  • Solved
SUMIFS formula possible with current worksheet formatting?
Replies
4
Views
274
duggie33
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A
P
  • Question
VBA for sending rows of data if column A = current date. (Via email)
Replies
1
Views
386
paun_shotts
P

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top