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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
I

IvenBach

Board Regular
Joined
May 31, 2008
Messages
212
<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
Scott Huish

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
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

corentint
  • Solved
NEW from "Help! Formulas Disappearing After Every Save" (from 2020)
Replies
6
Views
966
corentint
corentint
A
  • Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
1K
akshay27
A
M
  • Question
Need to add _AfterUpdate events to a dynamically-created class
Replies
1
Views
389
Matrovsky
M
F
  • Solved
Adding up values in one table based on values in another table
Replies
2
Views
740
formula911
F
K
  • Question
Create a survey with a customised report
Replies
0
Views
280
KatieLDalton
K

Forum statistics

Threads
1,191,670
Messages
5,987,954
Members
440,121
Latest member
eravella

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
Top