Incorporating Calculations into Zapier

Use case: Basic Tax Calculation using Lookup Tables

The tax brackets can be confusing to work with. Different ‘levels’ of your income needs to be multiplied with a different percentage to find the total. When you want to do tax calculations in Zapier, you are limited with regular IF function. You need to nest IF functions for each tax level that makes the formula long and hard to read.

Alternatively, you can use a SpreadsheetWeb Hub application to create a table using a single Excel-based lookup function to calculate tax amounts.

Note: Zapier do has lookup table support as well. However, it is hard to create and maintain due to website capabilities and also limited with exact match option. In other words, Zapier's lookup table cannot return a value when you search for an amount between two tax brackets.

Model

Our sample is based on tax brackets and fictitious basic tax rates below:

Low	High	Rate
0	9,999	7%
10,000	24,999	10%
25,000	49,999	14%
50,000	99,999	22%
100,000	249,999	30%
250,000	and over	45%

For example, if your salary is 150,000, you will pay 150,000 * 30% = 45,000 in taxes.

Zapier Approach

As mentioned before, you need nested IF functions to calculate basic tax rate of your income. Zapier's spreadsheet-style formulas can be found in their Formatter app. Select Numbers as Event.

You need to select Spreadsheet-Style Formula option for the Transform property in the Set-up action step. Once selected, you can see a textbox where you can enter your formula.

As you can see in the screenshot and the formula at below, evaluating a tax table with 6 rows requires 5 IF functions to be nested. Also, we need to mention that you have to move your hand from keyboard and use your mouse to add a dynamic field, e.g., Salary in our sample.

IF({{1. Salary}}<10000,0.07,IF({{1. Salary}}<25000,0.1,IF({{1. Salary}}<50000,0.14,IF({{1. Salary}}<100000,0.22,IF({{1. Salary}}<250000,0.3,0.45)))))

Here is the tax rate for 50,000:

SpreadsheetWeb Hub App Approach

With SpreadsheetWeb Hub, you are not limited with IF function. You can use any lookup function of your choice. In the next section you can find how to build your tool in Excel. If you already have a model in an Excel workbook, you can skip to the App creation section directly.

Preparing model in Excel

First, create a table that contains lower limits and tax rates. (B5:C10)

Limit	Rate
0	7%
10,000	10%
25,000	14%
50,000	22%
100,000	30%
250,000	45%

Then, enter the value you want to calculate its tax into a cell (F4).

Here are both XLOOKUP and VLOOKUP versions of tax calculation:

Finally, give names into cells that you want to use on your Zaps. For example, the value you want to calculate its tax (F4) and the tax value (F7).

Creating a SpreadsheetWeb Hub Application

To create your application, login your SpreadsheetWeb Hub account and click Create New Application button from the Applications page.

Once your Excel file is uploaded, you will be directed to the Applications page. Unless you want to generate a custom user interface for your app, you can continue with Zapier.

Note: To create your custom web application, use Go to Designer button to open the Designer module and start building.

SpreadsheetWeb Hub in Zapier

In Zapier, choose the application as SpreadsheetWeb Hub app and Calculate as the event to calculate basic tax rate according to amount in previous steps.

Choose your workspace and application to populate the named ranges in your workbook. For example, we named the cell F4 as "Income" which appears as an input box. Bind the value you want to calculate its tax to "Income" and select the named range of the cell that contains the formula ("TaxRate").

You are ready to go:

Zapier supports spreadsheet-like formulas, but it has limitations. When it comes to work with tax calculations, Zapier’s spreadsheet-like formulas might not be enough for businesses that require more complex calculations with dependent cells that are easier to build and maintain in spreadsheets. With SpreadsheetWeb Hub, businesses can create their tax calculation tools without stumbling into Zapier’s limitations and include such complex spreadsheet calculations into their Zapier automations.