Categories

## DATEDIF How to Calculate Difference Between 2 Dates

The DATEDIF function calculates the number of periods between two dates. The best way to remember this function is it calculates the DATE DIFference between two dates.

## What is DATEDIF?

The DATEDIF function is a popular formula that has 3 parameters which are all required. If you [encounter any error]({{< relref “#datedif-errors-and-problems” >}}) when using this function check you have populated all three fields correctly.

The function reference is as follows:

=DATEDIF(start_date, end_date, unit)

The start_date parameter should be the earliest date, the end_date being the latest date, and the unit is the value of the periods you want returned from the difference between those two dates.

Accepted values in Google Sheets for the unit value are:

### Date Difference in Days

By setting the third parameter to the DATEDIF formula to D it means the resulting value returned will calculate the number of days between the two dates. Here are some examples:

Several things to note about these tests when using the DATEDIF function and the unit parameter as D:

1. The dates are not inclusive. Notice the first row is 0, if it were inclusive it would have been 1.
2. Should the end_date come before the start_date you will get an #NUM! error. Make sure your end_date occurs on or after the start_date.

### Date Difference in Months

If you want to calculate the difference between two dates in months, then set the third parameter of the formula to "M", here are some interesting examples of what this looks like for certain values:

As can be seen from the table above, the day of the month needs to be either eclipsed or matched for the returning value to increment. In rows 5 and 6 especially we can see the days in the month of February 2020 would not have eclipsed the start_date of 31st January 2020, but when the end_date advances into the next month (1st March) the increment occurs.

Therefore, if your start_date is the end of a month containing 31 days it will not be until the 1st day of the following month, preceding a month containing only 28, 29 or 30 days, that the value will be incremented.

### Date Difference in Years

If you want to calculate the difference between two dates as years then set the third parameter value to "Y". Here is what this would look like based on the following inputs:

As can be seen from the examples above, it’s not until the date and month of the start_date has been eclipsed or matched the value returned by the DATEDIF formula increments. This is very similar to when calculating the [date difference in months]({{< relref “#date-difference-in-months” >}}).

### Date Difference in Days Irrespective of Months

If you need to calculate the difference in days between two dates irrespective of the difference between months then you’ll need to use "MD" as the value of the third parameter, as demonstrated below:

As previously noted with the [difference in days]({{< relref “#date-difference-in-days” >}}) we can see from the examples above the difference in days is not inclusive. However, there are some interesting results:

Row 3 shows a result of -1. Why? This implies there was a day short in the previous month of getting to the same date, and the current date of 1st March 2020 is 1 day short of the 31 days (29 days in February + 1 day in March = 30 days – 31 days = -1). This then puts the remaining values of that month (in March) out, until we hit the new month April.

### Date Difference in Whole Months

If you want to calculate the difference between two dates in whole months you can set the third parameter to the DATEDIF formula to "YM".

The results from this result set above are fairly simple to understand and are consistent with what we already know about – the day of the month needs to be matched or eclipsed to increment the value.

### Date Difference in Days Irrespective of Years

Similar to our previous example with [date differences in days irrespective of months]({{< relref “#date-differences-in-days-irrespective-of-months” >}}) we have a similar calculation but this time irrespective of years, here are some examples:

Consistent with the days difference irrespective of months we see that where the start date isn’t matched by day of month and month, it calculates the difference the previous month fell short.

## Benefits of DATEDIF Over Subtracting Two Dates

An objection to using the DATEDIF formula is that it achieves the same result when subtracting dates, which is certainly far simpler than trying to remember a formula, and it’s parameter values.

The biggest benefit of the DATEDIF formula is that it helps to calculate the difference between two dates by providing us with the type difference we need. Therefore, the DATEDIF formula is more versatile for various use cases.

Another minor benefit to using the DATEDIF function when calculating the difference in days between two dates over the simple subtraction method is if the input dates contain time.

You can still achieve the same answer as the DATEDIF function, but you would need to remember and apply to use the TRUNC formula to achieve the same answer in row 3 above, like so:

## DATEDIF Errors and Problems

There are a couple of things to be mindful of when using the DATEDIF function. As already shown above in the [DATEDIF days examples]({{< relref “#date-difference-in-days” >}}) we had a result on the last row that gave us an error.

If you do get a #NUM! error check the parameter values are correct, and the earliest date is the start_date value, and the later date is the end_date value.

Provided your parameter values have been set correctly, the only other type of error which may cause problems and may not be as obvious is if the locale setting of your dates are not what you anticipated.

### Incorrect DATEDIF Locale

If you’re getting an answer which definitely isn’t correct you will want to check the settings of your spreadsheet to ensure it correctly interprets a date field value.

For example, some countries, such as the UK and Australia, have dates set to the following format: DD/MM/YYYY whereas other countries, such as Canada and the Unites States, have date values set to the format of: MM/DD/YYYY.

Ways to minimise this error occurring would be to quickly check your locale settings are correct before starting a new spreadsheet, or when modifying a spreadsheet.

In a Google Sheet this would be simply going to File > Spreadsheet Settings > Locale – change this to your country.

{{< figure src=”/images/datedif-check-locale.png” caption=”Check spreadsheet locale settings” alt=”Google Sheets locale setting” >}}

Another way to check is to input a date that wouldn’t be a date in the other format, for example, try 01/13/2020 and 13/01/2020 – one of these will not work and your DATEDIF formula will return a #VALUE! error as the date fields entered aren’t actual dates.

## Conclusion

In this article we’ve done a deep dive into the DATEDIF formula and seen the results it provided based on different parameter values.

We’ve also seen how the formula can be better suited than simply subtracting two dates, and we’ve diagnosed our problems and fixed them.

Overall the DATEDIF function is a fantastic function to use, and you should look at incorporating it more in your own daily use. If you’d like to learn more about the function in Google Sheets, you can read the [documentation]({{% relref “datedif-docs” %}}).

Categories

## 3 Simple Steps To Send Email With Amazon SES

If you have a custom domain and want to be able to send email through your personal Gmail account without having to register for a Business Gmail account then here’s how you can do it using Amazon’s SES.

## Prerequisite

If you haven’t done so already you will need to set up your custom domain in Amazon’s SES. Here is a detailed step-by-step guide on how to create your email accounts using Amazon SES, Lambda and S3.

Once you’ve completed the steps on setting up your custom email address you can proceed with setting up the ability to send email from Gmail.

## Steps

The steps in this guide to send email from your custom domain within your personal Gmail account will be as follows:

1. Create an SMTP account with Amazon’s SES.
2. Register your SMTP credentials in Gmail.

Let’s go through each of these steps in detail.

### Step 1 – Create SMTP Account in Amazon SES

You will need to login to your AWS Amazon account and then from the Services menu click on Simple Email Service.

From the SES page on the left sidebar click on the label SMTP Settings.

Here you will see some core configuration settings for your SMTP credentials which you will need so you might want to copy them to your text editor:

• Server Name: email.smtp.us-east-1.amazonaws.com
• Port: 25, 465 or 587
• Use Transport Layer Security (TLS): Yes

Your configuration for the Server Name may be slightly different if you have selected a different location (look up at the top right-hand corner next to Support – to see which region your domain has been registered).

#### Register SMTP Credentials

To finish off this process click on the Create My SMTP Credentials button and follow through each of the prompts.

Create an IAM User Name to distinguish this user’s credentials. Once you have a basic user name then click on Create.

This next screen is very important as it will contain the unique user name and password that Gmail will need to create your SMTP account. Your username will be a combination of capitalised letters and numbers, and the password will be somewhat the same but with lower case letters.

Once you have captured these details and stored them safely it’s time to head on over to Gmail to register our new sending email address.

### Step 2 – Register SMTP E-Mail in Gmail

Log in to your personal Gmail account, eg. johnsmith@gmail.com, and once you’re in click on the settings icon (usually an icon in the form of a bolt) followed by the Settings drop down menu item.

From the Settings area click on the tab with the label Accounts and Import, then find the area labelled Send mail as and within this area click on the link Add another email address.

This should pop up a modal window where you need to enter the following details:

• Untick “Treat as alias” as you capture email through Lambda & S3 if you’ve followed the [prerequisite]({{< relref “#prerequisite” >}}) above.

On the last window enter the following details you’ve captured from Amazon SES:

• SMTP Server: email.smtp.us-east-1.amazonaws.com (the Server Name)
• Port: 587
• Select Secured connection using TLS (recommended)

Finally, click on the Add Account button and then Google will send through an email you will need to open and click on a link to verify.

If you’ve set up your Lambda and S3 inbound email service to forward through any emails to this very same Gmail service you’ve logged in as you will get an email very shortly. Otherwise, go and fetch the email to where you’ve set the custom domain to forward emails through to and click on the link.

Easy as.

## Conclusion

In this article we have learned how to set up an Amazon SES SMTP service by creating a unique username and password. You’ve been able to use the configuration settings on Amazon SES to then configure sending emails from your custom domain by using these settings in their respective fields in Gmail’s Settings area.

If you use software to manage your email, you can copy the same process over and apply the same settings.

Categories

## How To Create A Secure S3 Website Using Cloudfront & AWS Certificate Manager

If you have made the migration to host your website using Amazon’s S3 service and encounter issues with serving your content through a secure certificate or even trying to get Cloudflare to connect then this article will hopefully help set your connection right.

To help illustrate the concepts behind the connection, I’ll use a site I once owned to show the entire process freeworksheetsmath.com.

This is perhaps the easiest out of all the steps, although the rest aren’t too bad, but to get started you will need to log in to your AWS and your Cloudflare accounts.

Our first X steps will be in the AWS area, then towards the end we finish up with Cloudflare.

So, starting in your AWS account let’s get the following configurations set up.

## Step A – Create an S3 Bucket (or two)

If you haven’t done so already you will need to create an S3 bucket to be able to host your static files.

Click on the orange Create bucket button and enter the web address of your website. For example, if your web address is www.some-site.com then you would enter this into the Bucket Name field. If you want to host your website from a specific region then select this region in the next field. Lastly, as our static site is to be viewable by the public we need to untick the Block all public access checkbox and confirm this selection by acknowledging the warning that pops up underneath.

For my old website this creation of the S3 bucket stage would look something like this:

You’ll know everything has worked when you see a green confirmation pane, and a listing of all your S3 buckets.

### Configure Your S3 Bucket for Static Hosting

There are a couple of further configurations still needed on our bucket to enable it for static site hosting. Therefore, click on your S3 bucket in the table before you and from this new page click on the Properties tab.

On the properties screen we have a couple of options to further enable static site hosting, the most obvious being the tile labelled Static website hosting – click anywhere on the white space in this tile (i.e. everything but the Learn More link):

The available options on this tile should be fairly obvious, we want to select the option to Use this bucket to host a website and upon selecting this option we need to set what the index document is when people enter our web address in their web browser, and what the error document is when people type an incorrect web page for our website.

As I use Hugo to generate my static sites, it automatically creates a 404.html page as the error page, therefore my settings for my S3 bucket would be as follows:

Before we leave this window you’ll need to grab the URL. The URL generated by AWS for my S3 bucket is http://freeworksheetsmath.com.s3-website-ap-southeast-2.amazonaws.com. Keep a copy of this URL as it will be needed later.

When you’ve copied the URL click the Save button.

### Create Bucket Policy

Once you’ve set the properties of the bucket, you need to set the Bucket’s policies.

Click on the Permissions tab, then click on the Bucket Policy button.

Below the main text box is a link labelled Policy Generator, click this, and you will be navigated to a visual editor where you can set your permissions.

On this screen set the following values:

• Step 1: Select Policy Type = S3 Bucket Policy
• Step 2: Effect = Allow
• Step 2: Principal = *
• Step 2: AWS Service = Scroll down and tick GetObject
• Step 2: Amazon Resource Name = Type in your bucket according to the format arn:aws:s3:::/ (mine would be aws:aws:s3:::freeworksheetsmath.com/)
• Step 2: Add any conditions if needed (I didn’t set any).

Here’s how my options appeared:

To generate the JSON code to use in our Bucket Policy you need to click the Generate Policy button found in Step 3.

Doing so pops up a modal window where you need to copy the contents of that window and then paste it over to the text box area above the Policy Generator link.

The code generated for my S3 bucket was as follows:

{
"Id": "Policy1590953744901",
"Version": "2012-10-17",
"Statement": [
{
"Sid": "Stmt1590953064638",
"Action": [
"s3:GetObject"
],
"Effect": "Allow",
"Resource": "arn:aws:s3:::freeworksheetsmath.com/*",
"Principal": "*"
}
]
}

When I copied this back into the bucket’s Bucket Policy text area and then clicked Save you should see confirmation of the nature of your bucket’s policy being public.

Your bucket has now been set up to correctly display web pages. You can upload a file to your bucket and test this works by navigating to the URL you copied earlier.

### Test S3 Bucket

To test your S3 bucket will correctly display web pages, create a simple index.html (or whatever identifier you used when setting the website’s index page in the Properties window).

I created a simple web page as follows:

<!DOCTYPE html>
<html><body><p>Hello world!</p></body></html>

Then upon clicking on the Overview tab in the bucket, click on the Upload button.

2. Set permissions (I left everything as is). Click Next.
3. Set the properties of the file (I left it set to Standard). Click Next.
4. On the Review screen click Upload.

If you can see the text of the HTML document you uploaded into your bucket then well done! If not, click on the Properties tab in your bucket, then click on the Static Website Hosting tile and click on the link to your bucket.

If you still cannot see anything check the settings on this tile for the Index document, and the name of the file you uploaded – they need to be exactly the same.

### Create Another Second S3 Bucket

If you’d like to create another S3 bucket which automatically routes traffic through to your previous S3 bucket you’ve just created then do so now.

Go back through the previous steps, except this time name the bucket differently according to the web address you wish to redirect.

For example, I didn’t use the www sub-domain for my website address, but I wanted to redirect users who type the popular sub-domain in their address bar so that it redirects them correctly to freeworksheetsmath.com. Therefore, I created a new bucket labelled www.freeworkeetsmath.com.

The only difference with this set up is we only need to set the Properties on this bucket.

Therefore, once the bucket has been created, click on the Properties tab, then click on the Static website hosting tile again.

You’ll need to set the properties here a little different from your main bucket. Click the Redirect radio button and enter the name of your main bucket and whether to redirect traffic to a secure website or standard.

Here’s how the properties of my redirected bucket looked:

Click Save, and your redirected website is good to go!

## Step B – Create SSL Certificate

If you want your bucket to be served through a secure connection (highly recommended) then you’ll need to use another service in AWS called AWS Certificate Manager.

By clicking on the Services link in the upper-left, type Certificate Manager and select the option that appears.

To start this process click the Get Started button to Provision certificates.

IMPORTANT!
I found I had to set the zone for my certificates to N. Virginia to get them to work properly with Cloudflare. If you experience any problems with connecting your certificates to Cloudflare you might want to do the same.

To continue through you will want to select Request a public certificate, upon clicking the Request a certificate button you will be presented with a list of steps, let’s go through these one by one.

### Step 1 – Add domain names

Add the domain names you want this certificate to cover.

I wanted to be specific, so in this area I entered each of the following domains:

freeworksheetsmath.com
www.freeworksheetsmath.com

Click Next.

### Step 2 – Select a validation method

With this step you need to prove you own the domain by performing one of two tasks: modifying your DNS records, or through email verification.

If you’re comfortable and have access to your domain’s DNS records then select the DNS Validation option.

### Step 3 – Add Tags

If you’d like to manage your certificates by applying tags you can do so here, otherwise click Review button.

### Step 4 – Review

The final step is to review everything you’ve done. If you’re happy with everything then click the Confirm and request button.

### Step 5 – Validation

On this final step, if you’ve previously selected DNS validation you will be required to create a unique CNAME record for your domain.

Here I need to set the CNAME record to verify both the root domain and the www sub-domain.

Once you’ve set these properties in your DNS provider you need to wait for AWS to validate and confirm your domain.

## Step C – Create Cloudfront Distribution

Our final step is connecting both the S3 bucket with the secure certificates generated.

Click on the Services link again in the top-left hand corner and enter Cloudfront.

Click on the Create Distribution button to get started.

### Step 1 – Select Delivery Method

Click on the Get Started button for Web.

### Step 2 – Create distribution

On this screen you need to set a few properties, as follows:

• Origin Domain Name = Paste the URL of your bucket’s website address without the leading http value. For example, the URL of my S3 bucket is http://freeworksheetsmath.com.s3-website-ap-southeast-amazonaws.com but I just pasted freeworksheetsmath.com.s3-website-ap-southeast-amazonaws.com into this input field.
• Viewer Protocol Policy = Set to Redirect HTTP to HTTPS
• Compress Objects Automatically = Yes
• Alternate Domain Names (CNAMEs) = I entered per line each of the domains I had registered with the Certificate Manager, namely:
freeworksheetsmath.com
www.freeworksheetsmath.com
• SSL Certificate = Set Custom SSL Certificate (example.com) and then select the domain of the Certificate you created previously.
• Default Root Object = Set index.html or whatever you set in your websites properties when setting the Static website Hosting.

Additionally, change any of the other settings as needed. Once you are done, click on the Create Distribution button.

You’ll need to wait a few minutes while the distribution creates the necessary configurations, and you’ll know everything is ready when you see the state of the distribution as Enabled

## Step D – Set Domain CNAME Record

Finally, to finish this whole process off all you need do is set the unique Cloudfront URL to the value of your domains in your DNS provider.

My Cloudfront URL is d2i7zzjw0icbrm.cloudfront.net, therefore, the property value for the CNAME records of my root domain and www sub-domain would be this Cloudfront URL.

To check everything has connected properly, and the domains have propagated through you can test this by running a dig command in your terminal:

\$ dig freeworksheetmath.com

When you see some A records with IP address numbers then you’ve likely got things connected.

## Conclusion

In this article we have learned how to create a secure website using Amazon S3, AWS Certificate Manager and Cloudfront.

Here was the final result of my website:

Categories

## 3 Easy Steps To Create A Redirect Layout In Hugo

If you’ve created a static site using the wonderful Hugo package and need to create some simple redirect HTML web pages then you’ll need to do the following:

1. Create your HTML template and place it in your main layout folder.
2. Create an archetype template file.
3. Create a file using the archetype and fill in all the meta header information.

Creating a simple redirect layout in Hugo is relatively easy and can help if you are transitioning from other content providers such as WordPress where your Hugo website structure may slightly change for some posts and pages.

Let’s explore these steps in a little more detail:

## 1. Create HTML Template

To create a redirecting HTML page we need to create a new HTML file and folder in Hugo.

As we are creating one page to represent one redirect, we will name the file single.html and place it in a new folder which we will label redirect.

When you have done this your folder structure should look something like this:

hugo_root/
â”œâ”€â”€ archetypes/
â”‚   â””â”€â”€ default.md
â”œâ”€â”€ content/
â”‚   â””â”€â”€ posts/
â”‚       â””â”€â”€ my-blog-posts.md
â”œâ”€â”€ layouts/
â”‚   â””â”€â”€ redirect/         <--- CREATE THIS FOLDER
â”‚       â””â”€â”€ single.html    <--- AND CREATE THIS FILE
...

### Barebones HTML Redirect File

The contents of the newly created single.html file should be as follows:

<!DOCTYPE html>
<html lang="{{ .Site.Language.Lang }}">
<meta http-equiv="Refresh" content="0; url='{{ .Params.redirect }}'" />
<body></body>
</html>

<!DOCTYPE html>
<html lang="{{ .Site.Language.Lang }}">
<meta http-equiv="Refresh" content="0; url='{{ .Params.redirect }}'" />
<body></body>
</html>

### Barebones with Link in Body

<!DOCTYPE html>
<html lang="{{ .Site.Language.Lang }}">
<meta http-equiv="Refresh" content="0; url='{{ .Params.redirect }}'" />
<body>
<!-- uncomment if you want to insert your template's footer any {{ partial "footer.html" }} -->
</body>
</html>

## 2. Create Archetype Template File

To make it easier for us to remember the content needed in a redirect post we will create an archetype template file with all the needed data.

Therefore, in your archetype folder create a file labelled redirect.md – this must be the same name as the folder created in layouts in the previous step.

hugo_root/
â”œâ”€â”€ archetypes/
â”‚   â”œâ”€â”€ redirect.md      <--- CREATE THIS FILE
â”‚   â””â”€â”€ default.md
â”œâ”€â”€ content/
â”‚   â””â”€â”€ posts/
â”‚       â””â”€â”€ my-blog-posts.md
â”œâ”€â”€ layouts/
â”‚   â””â”€â”€ redirect/
â”‚       â””â”€â”€ single.html
...

### Archetype Template

Then within this file populate the contents like so:

+++
draft = true
date = {{ .Date }}
type = "redirect"
slug = "{{ .Name }}"
redirect = "https://..."
+++
CONTENT NOT RENDERED

Only insert details here if you want to enter any additional detail about the purpose of the redirect, otherwise you can just leave this portion of your file blank.

There are several important things here to note:

1. There needs to be a type meta tag which matches the name of the layout folder we just created.
2. The content are of the markdown post isn’t rendered, therefore you can type whatever you want in here. Maybe even an explanation on what to do in case you forget!
3. Remember when you’re ready to flick this redirect live change the draft from true to false or remove the draft line completely.

## 3. Create Redirect Page

To create a redirect page we open up our terminal, in our current Hugo website working directory and enter the command:

\$ hugo new redirect/test-my-redirect.md

This should create a new file in your contents folder, such that your directory now looks like this:

hugo_root/
â”œâ”€â”€ archetypes/
â”‚   â”œâ”€â”€ redirect.md
â”‚   â””â”€â”€ default.md
â”œâ”€â”€ content/
â”‚   â”œâ”€â”€ redirect/
â”‚   â”‚   â””â”€â”€ test-my-redirect.md   <--- HELLO WORLD!! =)
â”‚   â””â”€â”€ posts/
â”‚       â””â”€â”€ my-blog-posts.md
â”œâ”€â”€ layouts/
â”‚   â””â”€â”€ redirect/
â”‚       â””â”€â”€ single.html
...

Upon opening this new file you should see something like the following:

+++
draft = true
date = 2020-05-27T16:26:14+10:00
type = "redirects"
slug = "test-my-redirect"
redirect = "https://..."
+++
CONTENT NOT RENDERED

Only insert details here if you want to enter any additional detail about the purpose of the redirect, otherwise you can just leave it blank.

From here you can edit this file where needed, especially the redirect tag and then remove the draft tag when finished.

## 4. Publish

\$ hugo server

Test the link by visiting the url: localhost/test-my-redirect

If all works well you should hopefully experience the redirect!

## Conclusion

1. How to create a custom template layout.
2. How to create an archetype to help use the layout needed for this type of layout template.
3. How to create a new page using the archetype and its corresponding layout.
4. How to publish our new layout and test.

Hopefully your redirect issues in Hugo will be solved with this helpful post.

Categories

## How to Create a Google App Script Library

In our previous post on clasp and Google App Script code we discovered how we can pull code down locally, do our edits and then push it back to Google Apps.

This is great when we’re working on code on the back-end of Google Sheets, or Google Forms, or any of the other great Google Apps.

However, what do you do when you want to write code, but you don’t want someone ‘accidentally’ editing your code?

Is there a way where you can write your code and then link to it within a project and have it run without the worry someone will break it?

Yes, of course!

We will still be using the fantastic processes of clasp, but this time we’ll be creating a new project. So instead of cloning a project to our local computer we’ll start by creating a new directory and creating a project there.

## Create project

Create a new project with WebStorm of your favourite code editor. Initialise npm in your new directory (if you haven’t already done so):

\$ npm init
...

Install clasp globally if you haven’t already done so:

\$ clasp -v
2.3.0

To create a new project it’s just a matter of making sure you’re logged in and progressing through the prompts:

Then simply creating a new project:

clasp create [scriptTitle]

## Create Ignore File

You’ll want to exclude files from being a part of the project, so you may want to look at creating a .claspignore file. My file tends to have these added:

node_modules/**
node_modules/**/.*/**
node_modules/**/.*
.idea
.git
.DS_Store

Before jumping into the code you may also want to install a developer dependency to your project if you’re interfacing with Google Apps. I’d highly recommend adding a library to help with code completion:

\$ npm install --save-dev gas-lib

The next part of the journey is all over to you as you write your code.

If this takes time, and you want to keep your code safely stored on Google’s cloud then feel free to run the following command while you work away:

\$ clasp push

## Create version

Then when you’re ready to publish your code for publication and use you need to create a version:

\$ clasp deploy

You should see a version number followed by a hash which represents the Deployment ID your script.

### Do I need to re-deploy if I make changes?

Should you need to make any additional changes after having deployed your code you can still clasp push the code to Google Apps, but instead of using clasp deploy you will use the following command:

\$ clasp version

The difference with this procedure is that you will need to provide a description for the new deployment version. As I find I’m only maintaining one source code most of my descriptions have been annotated with “fix X”, but feel free to use a better descriptor to suit your needs.

For example, if I’ve just made a fix to the code base on my computer, I could annotate the description like so:

{{< figure src=”/images/clasp-prompt-provide-description.png” caption=”Provide description to code changes” >}}

Great!

So now you’ve pushed your code up to Google Apps, how can you use it in the backend of other Google Apps?

All you need is your Script ID for other Google App Scripts to use it.

Thankfully it’s just a simple command away. In your terminal enter the following command:

\$ clasp open

When you do your script will open up in a browser window and on the terminal prompt you will see a URL which would have the following schematic:

You can either try to capture the SCRIPT_ID_HASH from the URL in your terminal, or browser window, or you can fetch your Script ID from within the browser window clicking on File > Project Properties, and you should see on the pop-up modal a field labelled Script ID.

Finally, to be able to import your code into another project you from the other projects’ code editor click on the Resources menu then click on Libraries....

You should see an area from this modal window where you can paste your Script ID:

When you do successfully add your code you have the option of giving the library a callable name. I prefer to keep this as the default name of the library as it helps to keep things easy to maintain. If you do change the name, you may find having to update the name each time you update your version!

Once you’ve then correctly added this Script to your project you must select a version to apply. Here I’m just selecting the latest version. Should the Script author update their code you will need to manually open this Libraries’ modal window and select the new version.

To reiterate again there shouldn’t be a need to change the Identifer field on this window, but if you must then do so here.

The purpose of the Identifier is that this is the referring Object name that is used in the projects code to use the functions of the inserted library.

Once you’re happy with everything just click the Save button.

## Use It In Your Code

From here on out it’s now just a matter of referencing the new library in your code by using the Identifier.

So if we were using the OAuth2 library in our code it would look something like this:

var init = OAuth2.createService('QBO');

## Conclusion

In this post you’ve been able to learn how to create a custom library in Google App Script.

You’ve seen how easy it is to deploy a new script, how to update it (if needed), how to import this library into your new projects, and how to reference it in your new project.

• Reuse common libraries when interfacing with certain Google Apps
• Prevent anyone ‘accidentally’ clicking around and removing your code
• Easier access to code and to update, rather than trying to find the Document, clicking Script Editor (etc)

Categories

## How to Flip ARRAY_CONSTRAIN Formula in Google Sheets

Previously I posted how you can use the INDEX() function to obtain the fields needed for a simple SUM() function.

Then I came across another handy Google Sheet function ARRAY_CONSTRAIN().

## What ARRAY_CONSTRAIN Formula Does

There are three parameters with this formula:

1. range – insert the range for the formula to operate on.
2. num_rows – set the number of rows to compress.
3. num_cols – set the number of columns to compress.

### Example

Let’s look at a current depreciation problem I’m trying to solve.

I’m trying to compress the different types of depreciation on an array of different purchases projected over the next few years and to calculate what the new depreciation would be once these items have been purchased.

The data looks like this:

{{< figure src=”/images/purchase-schedule-with-depreciation-life.png” alt=”depreciation schedule” caption=”Simple depreciation schedule” >}}

What we’re trying to achieve is to determine what the depreciation would be each year according to the year when items have been purchased, and their corresponding depreciation life in years.

## Limitations of ARRAY_CONSTRAIN

Unfortunately the big limitation of ARRAY_CONSTRAIN is it works from the top-leftmost corner to cut the excess cells.

For example, if we apply the following formula on our data set:

=ARRAY_CONSTRAIN(C1:G6, 3, 2)

It will return for us the following highlighted cells:

This causes a problem for our depreciation schedule as we’d like to fetch the last two years when we’re in the 2024 column for row 3, so using the following formula:

=ARRAY_CONSTRAIN(\$C3:G\$3, 1, B\$3)

For the Network Upgrade row would only return [20000,<blank>], as these are the nearest to the top-left cells.

## Workaround to ARRAY_CONSTRAIN

Is there a way to flip the input row?

There is!

Is there a REVERSE() function in Google Sheets we can apply to the range?

Unfortunately not yet.

So what we need to do as a workaround is to create our own REVERSE() function by sorting our data and reversing it backwards.

Thankfully my data already has a year row (Row 1), all I need to be able to do is sort the data set by that row, apply descending sort and then insert the new range back into the ARRAY_CONSTRAIN() formula.

Here’s how I flipped my data range:

=ARRAY_CONSTRAIN(QUERY(TRANSPOSE(\$C\$1:G3),"SELECT Col" & ROWS(\$C\$1:G3) & " ORDER BY Col1 DESC", 0),\$B3,1)

Let’s break apart this function and understand what’s happening, as usual we’ll start with the innermost functions and work out way out:

• ROWS(\$C\$1:G3) – I used this to be able to get the column number to return from the QUERY function. As the last Column in the data set is the one I wanted, I just obtained this from the height of the original input range.
• TRANSPOSE(\$C\$1:G3) – as my data set is in rows, I need to translate this into columns for it to be functional as a data set with the QUERY() function. The data set needs to include the row containing the years.
• QUERY(..., "SELECT Col" & ... & " ORDER BY Col1 DESC", 0) – the second SELECT parameter to our QUERY() function fetches just the one column, but orders by the first column being the column containing all the year values. The third parameter 0 just makes sure we don’t return any header rows.
• ARRAY_CONSTRAIN(..., \$B3, 1) – we finally wrap everything back into our original ARRAY_CONSTRAIN function. This time we change the parameters a little as they are in a column format, but the purposes are still the same – we want the nearest 2 years (the value of cell \$B3) – and as there’s only 1 column our third parameter is 1.

To calculate depreciation on this row our final touch is to sum everything returned from the range and divide by the number of years:

=SUM(ARRAY_CONSTRAIN(QUERY(TRANSPOSE(\$C\$1:G3, "SELECT Col" & ROWS(\$C\$1:G3) & " ORDER BY Col1 DESC", 0), \$B3, 1))/\$B3

We further wrap the formula in a check to see if there are any depreciation values in the Dep’n (Years) column:

=IF(LEN(\$B3),SUM(...),"")

### Result

Here’s the final result showing the function as well as our accumulated depreciation for the items purchased:

## Conclusion

In this post you’ve been able to discover and learn about the ARRAY_CONSTRAINT. You’ve seen its limitations in fetching from the upper-leftmost corner, and perhaps in time Google Sheets will append an additional parameter to set where the corner should be fetched from.

In the meantime we’ve discovered how we can reverse or flip a range to make use of the ARRAY_CONSTRAINT function by using the TRANSPOSE and QUERY functions together.

Categories

## Ignore Blank Cells in QUERY Function Google Sheets

If you have a series of data where you’d like to apply the QUERY function over, but want to ignore certain rows if a particular cell is blank or empty then you would write your formula as follows:

=QUERY(DataRange, "SELECT * WHERE Col1 IS NOT NULL", 1)

The way you can have the Google Sheets QUERY function ignore blank or empty cells is by using the string IS NOT NULL in your formula.

## Example

As an example, suppose we had the following data on assets we’d like to purchase that have differing depreciation rates, but we only want to return the items where there is a depreciation value (implying purchases with no depreciation will be written off in the year they are purchased):

In the above diagram we have the following function:

=QUERY(A2:E8, "SELECT * WHERE B IS NOT NULL", 0)

Here’s what each of the parameters passed into the QUERY function mean:

## First Parameter – Data Range

Set the data range for which the QUERY function will be performing its task.

In the example above the QUERY is performed on the data inputs for each of the large purchase rows.

## Second Parameter – Query Filter

Applying a filter on the data range by using a form of SQL language. In our example, here’s what the statement translates into:

• SELECT * – select everything from the range;
• WHERE B IS NOT NULL – apply the filter on column B where the cell is not null (is not blank or empty).

## Third Parameter – Header Rows

Although our data set does contain a header row (row A) we don’t want to return this in our result, therefore, we set this value to 0.

# Solution

The result of our formula is values from the original data set which need to be depreciated over several years.

We can further apply more logic to our QUERY function to perhaps SUM the value of all assets purchased containing the same depreciation value.

# Conclusion

In this post we’ve explored the powerful QUERY function and how we can filter our data by excluding rows where they contain cells which are blank or empty.

By applying the condition IS NOT NULL in our QUERY filter we can easily remove these from our results.

If you’d like to learn more about QUERY functions, especially how we can merge multiple rows into one you should check out our illustrated step-by-step article.

Categories

## 4 Simple Steps to Merge 2 Columns Into 1 With QUERY Function

If you have two or more columns of data and you want to merge these columns into one column then using the Google Sheets QUERY formula may be one way you can achieve this.

Here’s an in depth explanation of how to achieve this:

## Step 1 – Prep your data

If you data doesn’t contain any spaces then you’re good to go, if though your data does contain spaces then you will need to define a character at the end of each cell to get this to work.

## Step 2 – Understanding the QUERY Function

Let’s have two simple data sets, one which does not contain spaces in its data set, and another which does:

With both data sets we have highlighted in grey the returned values from the QUERY function.

### QUERY parameters

The QUERY function is a powerful formula in Google Sheets helps to treat data contained in tabular format like a database table which we can perform basic SQL queries on.

This function contains three parameters:

1. The data set.
2. The SELECT command.

It’s the third parameter of the QUERY function that will help us in being able to concatenate the data within each column.

At the moment, from above, we have set the third parameter to 0 which means our data set does not return any headers.

But what if I want to define the first row as being the header?

Then we would simply change this third parameter in the QUERY function to 1 – representing the first row. When we perform this change to our formula we see no change to our data, however, the returned values have now defined that first row as a header area.

What if we were to increase the number represented in our QUERY function? How is the result set returned if we define the header parameter as 2?

Here’s how it looks for our data sets:

Did you notice what the QUERY function does when we define more than one header row?

It concatenates the header rows and inserts a space between each appended element. Therefore, the first row containing A and the second row containing B produce the header A B.

(Can you foresee the issues we’re going to run into when we merge our columnar data which contains spaces already?)

So if we wanted to get all the elements within our column wrapped into one row we would just need to change that third parameter to the maximum height of our columns. We could use the ROWS() function to capture this number:

## Step 3 – JOIN columns

As the columns have been merged into one header row, we now want to stitch these column headers together.

This is achieved by wrapping the QUERY function in a JOIN function, like so:

Notice here we used the first parameter of the JOIN function as a space to mimic the behaviour of what the QUERY data did to our data too.

Do be aware the JOIN function does have a limit of 50,000 characters. If you have a lot of data to concatenate you might want to break it up to smaller data sets.

### What if our data contains spaces?

As you can see this strategy isn’t going to work too well as our result now contains too many spaces between data cells, and looks as follows:

Therefore, we’re going to need to improvise on the spaces in our current data set.

What we’re going to do is to modify our original data by substituting spaces for an oblique character that isn’t used in our data. For example, my current data set with names isn’t likely to have a tilde character ~.

Therefore, what we want to be able to do is to change our imported data set in the QUERY function so that we are operating on data that doesn’t contain any spaces – we want to be able to reserve these spaces for the explicit purpose of being able to split our data back into cells again.

One such way I achieved this was by changing our original formula:

QUERY(A1:B3,,ROWS(A:A))

To this:

QUERY(ARRAYFORMULA(SUBSTITUTE(A1:B3," ","~")),,ROWS(A:A))

What has happened with our first QUERY parameter?

Instead of referencing the original data set we modify the data set to substitute spaces for our oblique tilde ~ character.

The ARRAYFORMULA provides us the functionality of being able to operate on each cell within the range, and all we want to do is apply the SUBSTITUTE formula to each cell – if it has a space character replace it with the tilde character.

The result of our change should produce something like this:

We’ve now achieved our purpose of reserving the space characters for another specific purpose which we’ll see next.

## Step 4 – Transform back to one column

Our data is in one cell, split by the space character.

To stretch this back out into one single column all we need to do is SPLIT the data by the special space character and then TRANSPOSE it to leave the data as a column, otherwise it will remain in row fashion if we don’t.

Here’s what happens to our first data set when we SPLIT first:

=SPLIT(JOIN(" ", QUERY(A1:B4,,ROWS(A:A)))," ")

Then our concluding formula for the first data set:

=transpose(split(join(" ", query(A1:B4,,rows(A:A)))," "))

### What about our data set with spaces?

A little more work is going to be required with our original data set which contains spaces.

If we copy the above formulas into our working “space” formula here’s the result:

=TRANSPOSE(SPLIT(JOIN(" ",QUERY(ARRAYFORMULA(SUBSITUTE(A1:B3," ","~")),,ROWS(A:A)))," "))

As you can see we need to remove at the end each of those tilde characters.

How do you think we could do this?

If you were thinking ARRAYFORMULA and SUBSTITUTE again – well done! You’re acquiring a new skill today.

Here’s our concluding formula:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(JOIN(" ",QUERY(ARRAYFORMULA(SUBSTITUTE(A1:B3," ","~")),,ROWS(A:A)))," ")),"~"," "))

## Conclusion

So there you have it!

• What the third parameter of the QUERY formula does.
• How we can modify each cell within a range by using ARRAYFORMULA – specifically using the SUBSTITUTE formula.
• How to handle different data sets with the QUERY function, especially if they contain space characters.
• How to JOIN our QUERY data to form a single cell of data (careful of the 50,000 character limit).
• How to SPLIT our data set back into cells.
• How to TRANSPOSE our data set back into columns.

Well done if you’ve been able to follow along and understand these complex topics.

Categories

## Easily Merge Multiple Columns Into 1 with Query & ArrayFormula

In today’s post I’d like to explain how I used the following formula to help merge multiple columned data into a different columned arrangement as the purpose of the result was to export the original data set into a CSV file for import into NetSuite.

Here’s the formula we will dissect:

=ARRAYFORMULA(SPLIT(ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(JOIN(" ",QUERY(DataConcat!A:L,,ROWS(DataConcat!A:A)))," ")),"~"," ")),";",TRUE,FALSE))

# The problem

I had exported the following salary data from our budgeting software which contained how much each staff person would be receiving each month throughout the financial year.

An example of the raw data looked something like this:

The original spreadsheet had all months of the year and is labelled as Data:

I had to import this data into NetSuite using the CSV Import process, which required changing the structure a little bit, so that the resulting CSV looked like so:

As you can see with the requirements of the task, we need to be able to expand the data set to rows (as it’s currently laid out in columns), then merge the data back into one columnar set. Once we have achieved this we then use the handy File > Download > Comma-separated values (current sheet) option in Google Sheets.

# Solution: Step 1 – Expansion

The first rule when manipulating data is to leave the original data set intact.

Therefore, we create a fresh new sheet and label this whatever name is appropriate for your needs. In this example we will name it appropriate to what we are doing: DataExpand.

In our expansion we want to achieve all necessary CSV fields required. This means we need to fetch the ID number from the original Employee field, and to copy the remaining data into another field for the Employee Name. Then we split the header column in our original sheet to a data range. Followed lastly with the amount in each date range.

Our first sheet, should look a little something like this:

Here are the details as displayed in the photo above:

• Cells \$B\$1 and \$G\$1 contained the =COLUMN() reference to each of the months on the original data set. This just helps with creating an easy index function that uses that reference which can make it easier copying the same columnar set 12 times.
• Row 1 this contains the following formula to extract the ID number from the Employee field. Here we check to make sure we have data in the original Data sheet, if we do we want to perform a regular expression where we just want all the digits, therefore, we use the regular expression of d+:
=if(len(Data!A2),REGEXEXTRACT(Data!A2,"d+"),"")
• From \$B\$2 down we then use another regex formula to extract those digits and replace them with nothing. We also want to check if the employee received any salaries or wages, if they didn’t we don’t want to add them into the data set.
=if(index(Data!\$1:\$1000,row(),B\$1),trim(regexreplace(Data!\$A2,"d+","")),"")
• From \$C\$2 down we just insert the ID that is needed for each row if we have a confirmed value in the previous column.
=if(len(B2),\$A2,"")
• For \$D\$2 down if we have a value in \$B\$2 then we get the value of the salaries for that respective month. We use the handy index function here to get that value:
=if(len(B2),index(Data!\$1:\$1000,row(),B\$1),"")
• For \$E\$2 down if we have a value in \$B\$2 then we get the column heading of the respective month using, once again, the index function:
=if(len(B2),index(Data!\$1:\$1000,1,B\$1),"")
• Lastly, for \$F\$2 down we get the value in the previous cell, if something exists, and calculate what the last day of the month will be for that cell:
=if(len(E2),date(year(E2),month(E2)+1,0),"")

As we have been mindful of the formulas used for each budget month, we now just need to copy cells \$B\$2:\$F\$1 and then paste this 12 times across. We could make it even easier by, after our first paste in cells \$G\$2:\$K\$4 changing cell \$G\$1 to be a formula: =\$B\$1 + 1:

Then we copy this newly pasted range with formula edits across 10 more times.

# Solution: Step 2 – Concatenation

The purpose of the next sheet is to zip up our columnar collections. This is a relatively simple step, however, there a couple of things to be wary of:

1. Use TEXTJOIN when zipping up the columnar data rather than the JOIN or CONCATENATE functions. The primary reason for this is where there is no data in a cell TEXTJOIN creates the blank join, whereas the other functions neglect it. As an example, if we had the following table:

When using =JOIN(",",A1:C1) it produces the result Missing,Puzzle,Piece for the first row and =JOIN(",",A2:C2) will produce Missing,Piece for the second row.

Whereas using =TEXTJOIN(",",FALSE,A1:C1) produces the result Missing,Puzzle,Piece for the first row and =TEXTJOIN(",", FALSE, A2:C2) will produce Missing,,Piece for the second row.

As the TEXTJOIN function allows for the flexibility of the concatenation of your columnar data it’s import the data remain as it is represented elsewhere if there happens to be blank cells in your data.

1. The other important aspect with this formula is that we want to replace the space characters within our cells as the space character will be used to join and split data back into columns again later. Therefore, it’s important at this step (if your data also contains spaces) to replace the space character with an oblique character that is not used anywhere in your data set.

I am going to replace my space characters within my data to the tilde character ~.

Therefore, I have created a new sheet, which I’ve labelled DataConcat and cell \$A\$1 has the formula:

=if(len(DataExpand!B2),SUBSTITUTE(TEXTJOIN(";",FALSE,DataExpand!B2:F2)," ","~"),"")

Be mindful when transferring the data across to adjacent columns that you don’t copy paste or drag across the formula, as each column here needs to reference the collection in DataExpand. For example, in the next adjacent cell \$B\$1 the formula is:

=if(len(DataExpand!G2),substitute(textjoin(";",FALSE,DataExpand!G2:K2)," ","~"),"")

You will need to do this 12 times for each month.

# Solution: Step 3 – Merge

So here’s where we merge all the columns from the DataConcat sheet in Step 2 above into one big column collection.

=ARRAYFORMULA(SPLIT(ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(JOIN(" ",QUERY(DataConcat!A:L,,ROWS(DataConcat!A:A)))," ")),"~"," ")),";",TRUE,FALSE))

Wow, what a massive formula!

As with mathematics, we have to start with the inner most functions and work our way out. So let’s break up each component within this formula and explain what’s happening:

## QUERY function

QUERY(DataConcat!A:L,,ROWS(DataConcat!A:A))

Here we use the QUERY function to capture the data we want to operate on (the first parameter), being all the columns in the DataConcat sheet, and we then want to combine these cells into one row. We achieve this by setting the number of headers (the third parameter) to the maximum number of rows in the DataConcat sheet. This means every row in the DataConcat sheet will be zipped up into one row!

The way the QUERY function zips everything up is by applying the space character to each new cell. This was why it was important for us to remove the space characters from our own data set before we applied this function.

If you’d like to know more about the QUERY function I wrote up a more detailed post here explaining this type of use.

## MERGE – Using JOIN, SPLIT & TRANSPOSE functions

TRANSPOSE(SPLIT(JOIN(" ", QUERY(...)), " "))

The combination of these three functions is to merge the single row returned by the QUERY function into one column.

First we JOIN the rows together using the special space character which helps to delimit each individual row. Then we SPLIT by the space character which produces the rows across individual cells horizontally. Finally, the TRANSPOSE function translates the horizontal cells into vertical formation – a single column.

Unfortunately the JOIN function does have a character length limitation of 50,000 characters. You may need to break up your requirements or look at other means if this ends up being an impediment.

## EXPANSION – Using ARRAYFORMULA

The last step is expand our singular column so that it can be represented as needed for CSV export. If we’re expanding data sets we need to use the ARRAYFORMULA function, as demonstrated here:

ARRAYFORMULA(SPLIT(SUBSTITUTE(transpose(...), "~", " "), ";", TRUE, FALSE))

The ARRAYFORMULA provides us with the ability to loop through each of the cells in our data set and to apply individual functions to them. In our case we want to:

1. SUBSTITUTE back the oblique tilde ~ character with its original space character.
2. SPLIT back the data we concatenated in the DataConcat sheet. However, we need to be mindful there may be instances where we have blank cells, and we want the SPLIT function to return the blank cells, therefore, we need to change our fourth parameter in the SPLIT function to FALSE (default is TRUE). By switching this to FALSE it turns consecutive delimiters like ;; into three separate cells, rather than just one cell.

With these functions all combined they produce the wonderful result intended, like so:

# Conclusion

Within this post we were able to learn how we can extrapolate data from one form to another using several intermediary steps and complex Google Sheet functions.

Hopefully this post has been helpful with learning:

• How to organise your steps by leaving the original data set (use multiple sheets if needed).
• How to use the QUERY function and especially its third headers parameter.
• Why you would use the TEXTJOIN function over JOIN.
• The limitations of the JOIN function.
• How to expand your data set using ARRAYFORMULA with any function when needing to loop through individual cells in the result.
• How to set the SPLIT function’s fourth parameter so that consecutive empty cells aren’t treated as one.
Categories

## How to Easily Sum Columns with Dynamic Range

I had a case in a spreadsheet where I needed to test the cash flow of payments made against future loans.

However, when it came to scenario testing the cash flow we had the ask and answer the question on the viability of the organisations cash flow depending on the loan repayment schedule.

Would we be able to repay our loans back quicker? What would our cash flow look like if repayments were 10 years, or 15 years, or 20 years’ terms?

To enable the spreadsheet to calculate this on the fly, I had to find a way whereby I could set a field value to represent the number of years the loan was to be repaid and for the single row in the report reflecting the repayment schedule to show those repayments.

For those familiar with a cash flow statement you will know there’s generally a section within the Financial Activities to detail any financial repayments made, ours was labelled Principal Repayments as shown in the basic table below of the first few columns (as we had to perform a 20-year projection there were more columns than this):

Now the issue at hand was that the New Loans row was an input row. Management wanted to insert how much they were willing to borrow for each project, but they wanted to see the impact on the Total Net Cash Flow which was the sum of the Total Operating Activities plus the Total Investing Activities plus the Total Financing Activities.

So with an input field on the New Loans row, and the output field on another row Principal Repayments all I needed to do was one more input cell representing the Loan Terms (in Years).

The cell I used as the input for the loan terms is defined in cell \$B\$50.

So here’s how the Principal Repayments row ended up:

\$B\$37 = -sum(index(\$B\$35:B\$35,0,MAX(COLUMN(B\$35)-COLUMN(\$B\$35)-\$B\$50+1,1)):B\$35)/\$B\$50

So let’s break this formula down, starting in the heart:

1. MAX(COLUMN(B\$35)-COLUMN(\$B\$35)-\$B\$50+1, 1) this formula helps to determine the column to start our range capture.

Here’s what we would get if the formula was copied across different cells:

\$E\$35 = MAX(COLUMN(E\$35)-COLUMN(\$B\$35)-\$B\$50+1, 1)

Which for each reference and function call results in:

• COLUMN(E\$35) = 5
• COLUMN(\$B\$35) = 2
• \$B\$35 = 10
• MAX(5 - 2 - 10 + 1, 1) = MAX(-6, 1) = 1

For the cell \$Z\$35 which would contain this portion of the formula as MAX(COLUMN(Z\$35)-COLUMN(\$B\$35)-\$B\$50+1, 1) has the following results:

• COLUMN(Z\$35) = 26
• COLUMN(\$B\$35) = 2
• \$B\$50 = 10
• MAX(26 - 2 - 10 + 1, 1) = MAX(15, 1) = 15
1. index(\$B\$35:B\$35, 0, ...) the nifty thing about the index formula is that it returns a value or the reference to a value. The parameters of the index function are: INDEX(reference, [row_offset], [column_offset]) and as we don’t want to change the row_offset this is represented as 0, but we do want to change the column_offset and this is where the MAX formula helped us. From the value achieved from the MAX formula we then receive a reference.
2. sum(index(...):B\$35) as we’ve captured the cell reference from the index function we can add the range notation : and have the sum value add all values from that cell to the current cell we are in.
3. =-sum(...)/\$B\$50 lastly as we have added up all the new loans in the sum formula we then divide the total New Loans amount by the loan term.

So if we had this cash flow sheet spread out of 25 time periods out, by the time we got to the formula in \$Z\$37 it would look like this:

\$Z\$37 = -sum(index(\$B\$35:Z\$35,0,MAX(COLUMN(Z\$35)-COLUMN(\$B\$35)-\$B\$50+1,1)):Z\$35)/\$B\$50

Thereby only getting us the last \$B\$50 (loan term) periods and dividing that amount by the loan term.

INDEX formula is a nifty little formula to learn!