As an extension from last week, this week is showing how we can take one language (VBA) and translate/transform it to another (JavaScript). We’re taking it from the native Microsoft Excel and putting it into the Google Sheets cloud.

https://docs.google.com/spreadsheets/d/14yweiJQAF9tef1J2c0a4Bj8viCOQgqkwUHx8GezdQr8/edit#gid=0

Fahrenheit to Celsius Converter

First, let’s start by opening up a new Google Sheet:

Next, let’s click Extension in the “ribbon” then Apps Scripts:

This will open a new tab and present you a blank template to start writing a function:

This is where I’ll enter the following code:

/**Fahrenheit to Celsius Converter**/

function FtoC(input) {

 return (5/9) * (input – 32);

}

After I click this little Save icon, this code will be good to go in my spreadsheet:

Let’s go back to the spreadsheet and add those 0 to 220 Fahrenheit integers like last week:

Now, enter the formula we’ve just created, =FtoC(), and select A1:

When you press enter it will show ‘Loading…’ in the cell:

But a moment later, you will have your result:

Now, let’s apply it to the rest of the list:

This is a lot easier, I think. Let’s take a look at the next function.

Volume of a Cylinder

Let’s go back to the script tab and enter the following code underneath our other function:

/**Volume of a Cylinder**/

function CylinderVol(r, h) {

 return Math.PI * r * r * h;

}

Again, wait for the function to load:

And there we have it:

And now to apply it to the other examples:

Finally, let’s have a look at the quadratic function solver.

Quadratic Function solver

function QuadEQN(a, b, c) {

// Calculate discriminant

var discriminant = b * b – 4 * a * c;

// Check if discriminant is negative

if (discriminant < 0) {

return “No real solution(s)”;

}

// Calculate the roots (x-values)

var x_1 = (-b + Math.sqrt(discriminant)) / (2 * a);

var x_2 = (-b – Math.sqrt(discriminant)) / (2 * a);

return [x_1, x_2];

}

Now, click Save and move back to the spreadsheet to create a template:

Next, enter the examples from last week:

1. 5x^2 + 6x + 1 = 0
2. x^2 + 2x + 1 = 0
3. x^2 – 4x + 6.25 = 0

Test Case 1

a = 5, b = 6, c = 1

Test Case 2

a = 1, b = 2, c = 1

Since both x_1 and x_2 are displaying -1, we know that this is one, single point.

Test Case 3

a = 1, b = -4, c = 6.25

Summary

What made this whole process much easier for me was following the process that I mentioned a few posts back:

1. Turn the problem into English,
2. Turn that English into Maths, then
3. Turn that Maths into Code.

Following this sequence of steps (and substeps between them) make programming these kinds of solutions much easier to handle.

Are you able to make your own formulas? Post them in the comments below – I’d love to check them out!