Automatic Calculations

About this Tutorial

Create custom scripts to make your spreadsheets more like tools.

Video Transcript

0:00 Let's say we're creating a spreadsheet where we're selling information but we want to add some calculations inside of our sheet and we don't necessarily want to share the actual calculation meaning the actual math with the user.
0:11 We want to uh obfuscate it a little bit or we want to create some semblance of personal branding on our math.
0:21 So here in this case it's a very simple divide by 12 but we don't we want the user to feel like oh I want to know what the rate per month and instead of just dividing by 12 let's give them a custom function.
0:33 This is actually super easy to do. I'm going to show you right away how to do it step by step.
0:38 So go up to extension app script. We're going to go here and already I have an on uh open uh thing here for item create uh from the bookmarklet.
0:50 We're going to add to that. So we're going to add an item here. Actually we don't even need to add the item.
0:55 Don't even need to bother. Just go up to the top if there's any functions here. Just create a function. Rate uh per Thank for watching.
1:04 Bye for now. Month. And what we need is a yearly rate and we're just going to write that here. Some uh variable name.
1:13 We can name this anything we want. And now we're going to return the monthly . Let's call that monthly rate.
1:23 Well, what is the monthly rate? Well, variable monthly rate. I have to spell it correctly with an L. Monthly rate equals yearly rate divided by 12.
1:34 So that's it. We just do the math. We're taking this yearly rate, and we are returning it. We can hit command S now, but there is one thing, one extra thing we can do to make this absolutely sing, and so that someone knows what we're talking about.
1:46 Uh, we can add a comment here with uh slash, and then two You have to do is add that at custom function, you can see it here, and let's go see what that does.
2:05 So in our rate per month, we have if is blank, let's delete all of that, and just do equal sign, so we're gonna do apply, take this formatting off, go over to rate per month, equals rate, and now, automatically, it has shown up here, rate per month.
2:24 the exact thing that we typed in our Apps Script, rate per month, and what we need to do is put in the yearly, so G2, and what do we get?
2:32 We're going to get the exact same answer we got when we did our calculation here, add percentage, we got exactly the same, and so now, instead of doing the math in the formula cell and showing the math to a buyer, we show them this function that they can use, and we tell them, hey you can use rate per
2:50 month, and you can figure out what is this rate from the loan rate, add your yearly rate here. And you get the monthly rate.
2:59 Now again, this is very simple, like you divide it by 12, but you can do more interesting things like CPMs or even custom functions where you create ratings, functions, whatever you want to do.
3:07 You can add text to this. Really cool. Hopefully that was a really easy way to share with you how. I'm going to share with you all the ways you can it.
3:28 uh just the triggers uh that are available to you just to get yourself thinking about what kind of sheets can I create and what kind of automations can I create to turn our spreadsheets into tools.

Courses

Selling Spreadsheets Course Introduction

What Sucks About Selling Spreadsheets And How to Solve These Problems

SECTION 1 - You Are The Sheet

Coupon Code Maker Updates February 2024 - New Dashboard

How to Make Good Looking Spreadsheets

Curate Google Sheets Easily - Automatic Bookmarklet Maker

Explore Your Spreadsheet Selling Options

What you know matters

Who you are matters

How you think matters

Why you think it matters, matters

Surprising Things about Selling Spreadsheets

SECTION 2 - Your Spreadsheet Blueprint

Easiest Sheet Ever to Sell

Problems Everywhere, and the Spreadsheets that Solve Them

The #1 Mistake Spreadsheet Sellers Make and how to Save People Time

Customer’s Context Is King

The Two T’s: Turn Templates into Tools

Create a Branded Lead Magnet from Qualitative Data

Tracker Tactics

SECTION 3 - How to Make a Spreadsheet

Ask Questions

Quality Curation

Fabulous Filters

Wield Wizardry

Automatic Calculations

Automate Emails

You Must Double Duplicate (If you're selling a sheet you use)

Different Kinds of Automation

How do I prevent people from sharing my spreadsheets by making a copy?

SECTION 4 - Spreadsheet Sales Mastery

Write The Sales Page

The Strangest Secret to Selling a Spreadsheet

The F Word

Reciprocity Rules

Pricing Strategies for Spreadsheets

Upselling and Cross-Selling Techniques

Avoid A Multi-Tier Approach

SECTION 5 - Marketing Mastery

Where to Sell Spreadsheets - Marketplace Marketing

Customer Service is Marketing

Six Figure Secrets

How to Sell Spreadsheets on AppSumo Marketplace

Protect Your Spreadsheet IP

Lock Your Sheets with OnlySheets

Weird Idea: Find a Great Sheet Domain Name

A Few New Spreadsheet Business Ideas

SECTION 6 - Spreadsheet Breakdowns

Maximize Spreadsheet Sales to Startups

A Spreadsheet CRM: Automation at work

How to Make Better Lead Magnets

How to Turn a Simple List into an Epic Lead Magnet

The Art of the Sell: Breaking down a Free Title Generator

How to Make an AI Integrated Freebie Spreadsheet