Just a little bit of technical non-sense. 

 

Yeah so right about now you’re wondering the following questions:

 

  • How does this work?
  • What did Nick use to build this?
  • Can you code?

Let’s find out

 

So first off this not coded one bit, what so ever! I can’t code worth of anything and if I tried this would be extremely buggy or most likely nonexistent. 

 

So if you didn’t already pick up on it, the questions are in a Google Form. And what do Google Forms link to? Google Sheets!

 

Do you see a possible way? If you said formulas, you’re correct!

 

So let’s look at the actual full Google Sheet. Since you only see one tab of it. 

 

This tab is all you see but look there’s more

So this is an outline of what you’re gonna see

Step 1 – The Form Collects the Responses —–> Step 2 – Google Sheets Fixes them into Rows ——>

Step 3 – Google Sheets Calculates the Gallons per year and CO2 Emitted per year ——> Step 4 – It makes it look nice

Step 1 – Form responses

Pretty Much What You’re Used To



Step 2 – Query

Google forms like to insert new rows which screws up formulas, so I used query to make sure it doesn’t insert new rows. Basically its copying the results onto another sheet.

I used this function for the entire sheet
=QUERY(‘Step 1) Form Responses’!A:Z)

See, It copied every row

Now Step 3 – calculations

So rows A-Z look familiar. They are just imports from the Query Tab.

I used this function for rows A-Z by distributing it. This example is what shows in B2

=’Step 2) Query’!B2

All This means is take B2 from the query tab and dump in on B2 in the calculate tab.

So now a list of the calculations in rows AA-BM

Don’t Worry! This isn’t as scary as it looks. We will break it down below!

Here’s an example for each function.

Let’s take a look at shower length. This is the most common way a question is used

So gallons used per day changes based on shower lengths

Heres an overview of the equation we will formulate

Shower Length (Min) x 2.1 (Gallons/Min) = Gallons Used Per Day

Gallons Used Per Day x Showers Per Week = Gallons Per Week

Gallons Per Week x 52 Weeks = 1 Year Gallons

So now let’s look at it in formulas

=B2 *2.1  Shower Length (Min) Collected from Form in Cell B2 x 2.1 (Gallons/Min) = Gallons Used Per Day

=AC2 * X2 Gallons Used Per Day Calculated in Cell AC2 (Above) x Showers Per Week Collected From Form in Cell X2 = Gallons Per Week

=AB2 * 52 Gallons Per Week Calculated in Cell AB2 (Above) x 52 Weeks = 1 Year Gallons

Hopefully, that wasn’t confusing

Answers with multiple choice:

Thanks to it being google forms, it will always have the same text. So we can calculate it to populate a cell with a certain number if it contains it

Let’s take a look at leaving a sink on while you brush your teeth. 
Heres an overview of the equation we will formulate

If the cell contains “Yes” fill it with 2920, if it doesn’t contain “Yes” fill it with 24.3333333

So now let’s look at it in formulas


=IF(REGEXMATCH(D2, “Yes”),2920, 24.3333333)

If the cell D2 contains “Yes” fill it with 2920, if it doesn’t contain “Yes” fill it with 24.3333333


Same concept for those with more than 2 possible outcomes. Except if it doesn’t contain the keyword it fills it with “0” since then it won’t affect the outcome. All of them point to the same cell, however since only 1 out of 3 things can

The same concept as above, just with 0’s for the ones that don’t contain the keyword.

Now summing them all up. This ones simple.


=sum(BL2,BK2,BJ2,BI2,BH2,BG2,BF2,BE2,BD2,BC2,BB2,AX2,AV2,AQ2,AP2,AN2,AS2)  

This is adding up all of the results that lead in numbers that we need to add to co2 emissions.

Finally, Step 4 – What you see, The Results

This one is just going to copy over the data we need from the big sheet with all the formulas

And that’s it! There’s nothing much to it

Final Step – Hosting it For You To See!

Thankfully, I have a domain already for my business. I’ll just throw it on a section of this website.

Also, did you code this website yourself? OF COURSE NOT! Thank you WordPress!

Have any questions, shoot me a message or come up and ask!