Google Sheets for Peer Feedback 3 – ARRAYFORMULA


<iframe width=”640″ height=”480″ src=”https://www.youtube.com/embed/Mow78KQNA54″ frameborder=”0″ allowfullscreen></iframe>
<div style=”max-width: 450px; float: left; padding-right: 20px;”>

As you noticed in the last tutorial, you can’t simply copy down a formula and have it apply to new form submissions. This is because the response sheet actually inserts a new row for every submission, breaking up the block of formulas. How then, do we have the formulas automatically apply to new submissions?To solve this problem, we need to use the function ARRAYFORMULA. This is a very powerful function that allows you to create an entire column of formulas with only one cell. The advantages of this are many:
<ul>
<li>it allows formulas to apply to new form submissions</li>
<li>it lets you make a change to a formula in only one place</li>
<li>it allows you to copy a single cell and modify it for variations</li>
</ul>
With the new Google Sheets, ARRAYFORMULAs are fairly simple: you use a range (usually A:A, B:B, and so on) instead of a cell reference. In the previous example, we used the formula:

<span class=” default-formula-text-color” dir=”auto”>=</span><span class=” default-formula-text-color” dir=”auto”>VLOOKUP</span><span class=” default-formula-text-color” dir=”auto”>(</span><span dir=”auto”>C2</span><span class=” default-formula-text-color” dir=”auto”>,</span><span dir=”auto”>Lookups!A:B</span><span class=” default-formula-text-color” dir=”auto”>,</span><span class=”number” dir=”auto”>2</span><span class=” default-formula-text-color” dir=”auto”>,</span><span class=”boolean” dir=”auto”>false</span><span class=” default-formula-text-color” dir=”auto”>)</span>

All we need to do now is change the Search key from C2 to C2:C, and put the whole thing in an ARRAYFORMULA function:

<span class=” default-formula-text-color” dir=”auto”>=</span><span class=” default-formula-text-color” dir=”auto”>ARRAYFORMULA</span><span class=” default-formula-text-color” dir=”auto”>(</span><span class=” default-formula-text-color” dir=”auto”>VLOOKUP</span><span class=” default-formula-text-color” dir=”auto”>(</span><span dir=”auto”>C2:C</span><span class=” default-formula-text-color” dir=”auto”>,</span><span dir=”auto”>Lookups!A:B</span><span class=” default-formula-text-color” dir=”auto”>,</span><span class=”number” dir=”auto”>2</span><span class=” default-formula-text-color” dir=”auto”>,</span><span class=”boolean” dir=”auto”>false</span><span class=” default-formula-text-color” dir=”auto”>)</span><span class=” default-formula-text-color” dir=”auto”>)</span>

<a href=”https://docs.google.com/a/unishanoi.org/spreadsheets/d/1yaHAgOBnPTrzdg-ru3FyZf3lEMihTo7-tSQ_olAQKZM/edit#gid=1372106026″ target=”_blank”>Go to the example</a>, and Click Form &gt; ‘Go to live form’ and fill it out to see what happens. You should see the formula applied to the new entry.

</div>
<div style=”width: 360px; float: left;”>
<h3>Google Sheets Lessons 1-9</h3>
Click on a lesson below for a written tutorial demonstrating the lesson.
<ul>
<li><a href=”http://www.teacherpaul.org/3054″>Lesson 1 – CONCATENATE</a></li>
<li><a href=”http://www.teacherpaul.org/3059″>Lesson 2 – VLOOKUP</a></li>
<li><a href=”http://www.teacherpaul.org/3063″>Lesson 3 – ARRAYFORMULA</a></li>
<li><a href=”http://www.teacherpaul.org/3068″>Lesson 4 – formatting ARRAYFORMUL</a>A</li>
<li><a href=”http://www.teacherpaul.org/3072″>Lesson 5 – QUERY</a></li>
<li><a href=”http://www.teacherpaul.org/3078″>Lesson 6 – UNIQUE</a></li>
<li><a href=”http://www.teacherpaul.org/3080″>Lesson 7 – INDEX &amp; MATCH</a></li>
<li><a href=”http://www.teacherpaul.org/3086″>Lesson 8 – conditional INDEX &amp; MATCH</a></li>
<li><a href=”http://www.teacherpaul.org/3088″>Lesson 9 – Autocrat Add-on</a></li>
</ul>
<h3>Examples / Challenges</h3>
<iframe src=”https://drive.google.com/embeddedfolderview?id=0B3Jsm0J_tIhNfnlHNmVDQ3FJLXJvUG1fZzl3VW1tRzhNaEhXaGZxSm1BNUFlT2J3bmdVWkE#list” width=”350″ height=”600″ frameborder=”0″></iframe>

</div>