Technical Writer

Doug Van

<div>
Thank you, that is brilliant! :)
</div>


<div>
<div class="content wysiwyg-content">
Greetings all, <br />
<br />
I am trying to process data created by Google Forms. It automatically created a column of data. The cells contain data from 1 to 8, but also text accompanies the selections &quot;1 (low)&quot;, &nbsp;and &quot;8 (high)&quot;. This creates a problem calculating the average value for this column, so I need to find a way to ignore the text in those cells.<br />
<br />
<b>The simplest way:</b><br />
Create a new column by taking the left more character with =Left(cell, 1) and then calculate on the new generated column. <br />
<br />
<b>A more elegant way?</b><br />
Is there a way that I can calculate the average without needing to create a new column?<br />
<br />
Something like this? <i>=AVERAGE(Left(G2,1):Left(G<wbr />90,1))</i> - of course, that doesn't work. But what do I know? LOL<br />
<br />
Additionally, if that is just too basic, I would love some tips on how to do magic/data analysis on Google Forms generated data. Google's results are really simplistic. <br />
<br />
Thank you. :)
</div>
</div>


Greetings all, 

I am trying to process data created by Google Forms. It automatically created a column of data. The cells contain data from 1 to 8, but also text accompanies the selections "1 (low)",  and "8 (high)". This creates a problem calculating the average value for this column, so I need to find a way to ignore the text in those cells.

The simplest way:
Create a new column by taking the left more character with =Left(cell, 1) and then calculate on the new generated column. 

A more elegant way?
Is there a way that I can calculate the average without needing to create a new column?

Something like this? =AVERAGE(Left(G2,1):Left(G90,1)) - of course, that doesn't work. But what do I know? LOL

Additionally, if that is just too basic, I would love some tips on how to do magic/data analysis on Google Forms generated data. Google's results are really simplistic. 

Thank you. :)

Calculating average value in a column (but filter out text in some cells)?

Google is an American multinational technology company specializing in Internet-related services and products that include online advertising technologies, search, cloud computing, software, and hardware.

Google

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.

Spreadsheets

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Excel templates

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.