Survey Values Question

[blondieefitz]

Can someone please help!

I am creating a spreadsheet to analyze the data of a survey. For each question of the survey there are 5 options that can be chosen as an answer, and each of the 5 answers have a numerical value that correspond with them ie: Very Poor=1, Poor=2. Fair=3, Good=4, Very Good=5. I need a way to assign those numerical values to the number of people who selected that option. All of the questions are listed in the A column, and the options for answers are listed in B-F. Say C2 reads as the title "Poor" and 6 people selected poor for a certain question- is there a way to create an average score for each question based on the numerical value of each answer without changing the cell from the number of people who chose that answer (ie. if 6 people chose the Poor, the cell would read six, but i need the calculation for the average to realize that poor scores a 2- so the value should actually be 12 when calculated.)

It sounds confusing but i know there has to be an easy answer!

Thanks!

 

[Snakehips]

blondieefitz,

Do either of Average1 or Average2 help?

P5 P

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><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="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><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></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD>V Poor</TD><TD>Poor</TD><TD>Fair</TD><TD>Good</TD><TD>V Good</TD><TD>Average1</TD><TD> </TD><TD>Average2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Q1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">3.58</TD><TD> </TD><TD style="TEXT-ALIGN: right">3.58</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Q2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3.14</TD><TD> </TD><TD style="TEXT-ALIGN: right">3.14</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Q3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">3.67</TD><TD> </TD><TD style="TEXT-ALIGN: right">3.67</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Q4</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">4.31</TD><TD> </TD><TD style="TEXT-ALIGN: right">4.31</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G3</TD><TD>=IFERROR(((B3*1)+(C3*2)+(D3*3)+(E3*4)+(F3*5))/SUM(B3:F3),"")</TD></TR><TR><TD>I3</TD><TD>=IFERROR(SUMPRODUCT(B3:F3,{1,2,3,4,5})/SUM(B3:F3),"")</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

NB you can only use the IFERROR(...) in 2007 or 2010.
It is included in the formula to prevent a /Zero error if there are no values entered.

Post back if you need a 2003 alternative.

Hope that helps.

 

[blondieefitz]

Thanks Tony!

after toiling around for a little while i came to the same conclusion that you did! my brain was just not working earlier

Thanks again