Custom functions in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a quick way to calculate the sales tax for any given amount directly in Google Sheets using a custom function.

📊 Data: You have a list of sales amounts in a column. The sales tax rate is fixed at 7.5%.

🎯 Deliverable: Create a custom function named SALES_TAX that takes a sales amount as input and returns the tax amount. Then use this function in the sheet to calculate tax for each sale.

Sample Data

Sale ID	Amount
1	100
2	250
3	75
4	400
5	150

1

Step 1: Open the Google Sheets script editor to write a custom function.

In Google Sheets, go to Extensions > Apps Script.

Expected Result

A new script editor window opens where you can write code.

2

Step 2: Write the custom function named SALES_TAX that calculates 7.5% tax on the input amount.

function SALES_TAX(amount) { return amount * 0.075; }

Expected Result

The function is saved and ready to use in the sheet.

3

Step 3: Use the custom function SALES_TAX in the sheet to calculate tax for each sale amount.

In cell C2, enter =SALES_TAX(B2) and drag down to C6.

Expected Result

Cells C2 to C6 show the tax amounts: 7.5, 18.75, 5.625, 30, 11.25 respectively.

Final Result

Sale ID | Amount | Tax
--------|--------|-------
1       | 100    | 7.5
2       | 250    | 18.75
3       | 75     | 5.625
4       | 400    | 30
5       | 150    | 11.25

✓The custom function SALES_TAX correctly calculates 7.5% tax for any amount.

✓Using custom functions makes repetitive calculations easy and clean.

✓The tax values update automatically if the sale amounts change.

Bonus Challenge

Modify the SALES_TAX function to accept a second optional parameter for tax rate, defaulting to 7.5% if not provided.

Show Hint

Use a default parameter value in the function like function SALES_TAX(amount, rate = 0.075) { return amount * rate; }