Transforming Data - Average

Unit introduction

You are now at the fourth unit of the “Transforming data using functions” course of Make Basics.

You are going to continue learning about different functions and in this unit you will focus on math functions, and in particular the average() function.

You will learn what this function does and how to use it in a scenario.

Before you continue, make sure you’ve completed the exercise in unit 1 of this course.

Let’s get to it!

Math functions in Make

Make has a wide variety of math functions which allow you to add numbers, round numbers to the nearest number without decimal points, calculate an average in a set of numbers, and more.

In this unit, we’ll show you how to use math functions in Make. We’ll focus on the average() function for this example.

The Math functions panel in Make includes:

• Variables: pi, random
• Functions: average, ceil, floor, max, min, round, sum, parseNumber, formatNumber
• Operators: *, /, mod, +, -, <, <=, >, >=

The average function

This function calculates the average value (also known as the arithmetic mean) of a group of numbers.

The average is the sum of all values divided by the number of values. For example, calculating the average of the numbers 10, 24, 35 returns 23.

For this to work, you need to provide at least 2 numbers to the function.

The average() function might be useful for example if you want to calculate the monthly average profit of your online store over 12 months.

An example of using the average function

You are going to practice using the average() function with your familiar Weather and Google Sheets modules.

You are going to create an output that will show you the formatted date (building on your knowledge from earlier) and the average temperature at that time.

Before you start, ensure you have set up the Basic Functions Google spreadsheet from unit 1 of this course. If you haven’t set it up already, now would be a good time to do it.

Weather Module Configuration

You want to get the average daily temperature for today. In our example, we set “London, UK” as location, but you can set the location up to your preferences.

Add the Weather → Get daily weather forecast module.

Tick Today and enter your location.

Days

• Today

I want to enter a location by cities

City Enter e.g. London, UK.

Google Sheets Module Configuration

Insert the Google Sheets → Add a row module to the Weather module.

Connect your Google account.

In the Spreadsheet ID field, map Use cases - Basic functions spreadsheet.

Under Sheet name, choose Math functions.

Connection Make Academy

Choose a Method Select by path

Choose a Drive My Drive

Spreadsheet ID /Use cases / Basic Functions

Sheet Name Math functions

Table contains headers Yes

Mapping the Date Field

Under Values, you are going to map the following:

In the Date (A) field, add the date. You might want to format the date so that it shows in your preferred format. You covered this in the previous unit so it’s a good time to use your new knowledge!

To make the date show in your preferred format, use the formatDate() function.

First add the formatDate() function in the Date (A) field.

Add now (which refers to the current date and time) between ( and ; and your preferred date format between ; and ).

The date format refers to how you want your date to look like, it can be for example DD/MM/YYYY. Make sure to close the brackets with ).

Your Date (A) field should look similar to this one below:

Date (A) formatDate( now ; DD/MM/YYYY )

Mapping the Average Temperature Field

In Average Temperature (B) field, you are going to map the average temperature for today. In order to do this, you need the maximum and minimum daily temperature.

Start by adding the average() function in the field.

Add Temperature.Minimum between ( and ; and Temperature.Maximum between ; and ).

Notice how when you hover over these items, the number data type shows?

Your field mapping should look like this:

Avg Temperature (B) average( Temperature.Minimum ; Temperature.Maximum )

Testing the Scenario

Click OK to save your mappings.

Now click Run once to test if your scenario is set up correctly.

Navigate to your Google Sheet to check the changes that happened. You should have the date and average temperature for the day!

Here’s an example of what we got. Note that your results might be different, depending on location and weather.

More math functions

You know how to get the average of numbers in Make now, good job! There are other math functions in Make, feel free to explore them on your own. We will cover more of these in our next courses, such as formatNumber().

You might also want to check out this post on the Make Community or the Make Help Centre article if you want to learn more about functions.

ceil

Rounds up to the next whole number. For example, for 2.2, the ceil is 3.

floor

Rounds down to the previous whole number. For example, for 2.8, the floor is 2.

max

Finds the largest numeric value in the set of data you specify. For example, max(23;78;1234) returns 1234.

min

Finds the smallest numeric value in the set of data you specify. For example, min(23;78;1234) returns 23.

round

Rounds a numeric value to the nearest whole number. For example, round(1.3) returns 1.

sum

Adds values you specify. For example, sum(23;78;1234) returns 1335.

parseNumber

Converts string to number in numeral system. For example, parseNumber(30,000.00) outputs 30000.

formatNumber

Returns a number in the requested format. For example, formatNumber(123000;2;.;,) outputs 123,000.00.

A note about data types

It’s important to note here that the average() and all other math functions work only with the number or an array of numbers data type.

If you use other data types with math functions, Make will output either empty or incorrect results.

A common error happens with the text data type, because as you might remember, the text data type can store letters, numbers and special characters.

For example, you can store 3.14 both as text or number data type.

This rule applies to all math functions. So remember, if you need to use math functions, make sure to use the parseNumber() function to get the number data type first and then use the math function you want.

Correct Result: Using Number Data Type

If you stored 3.14 as number data type, and use it in the function below:

average( 3.14 ; 123 ; 11 )

You will get the correct result as you see here:

OUTPUT Bundle 1: (Collection) Average: 45.71333333333333

Incorrect Result: Using Text Data Type

If you stored 3.14 as text data type (here named pi), and use it in the function below:

average( pi ; 123 ; 11 )

You will get the incorrect result as you see here:

OUTPUT Bundle 1: (Collection) Average: 1.0470770333333335