> ## Knowledge Base Index
> Fetch the complete knowledge base index at: https://help.refrens.com/sitemap.xml
> Use this file to discover available pages before exploring further.
> Pure-Markdown content can be obtained by appending a '.md' suffix to the content URLs listed in the sitemap (without the trailing slash).

# How to Customize Columns & Formulas in Accounting Documents?

Different businesses calculate prices differently.

A trader may charge according to the number of units sold. An interior designer may calculate prices based on the area covered. A fabric supplier may use length and width, while a service provider may charge according to time.

With Refrens, you can customize the columns and formulas in your accounting documents so that these calculations happen automatically while you create the document.

You can:
* Rename existing columns
* Add custom columns
* Show, hide, or rearrange columns
* Create formulas using other columns
* Use functions such as rounding, square root, maximum value, and percentage

This guide explains how to set up these calculations correctly.

${youtube}[](1OnI3756aJo?si=Vg_zcjb1MZGQUW8n)



# Important: Do Not Replace the Default Amount Column

Before creating a formula, remember this rule =>

Always use the default Amount column. Do not hide it and create a new formula column for the final amount.

Every Refrens document calculates the Amount using: **Amount = Quantity × Rate**

This is a fixed system calculation and cannot be edited.

Some users hide the default Amount column and create a custom column named **Total Amount**, **Final Amount**, **Value**, or something similar. This is not recommended.

A custom column is not treated as the document’s actual Amount field.

Even if it displays the correct value for an item, it may not be used correctly for:
* Subtotal and grand total
* Taxes and discounts
* Additional charges
* Payment amounts
* Accounting entries
* Document conversions

If you need a custom calculation, add the formula to the system-linked **Quantity** or **Rate** field. Refrens will then use the calculated values in the default Amount column.

# Understand Quantity, Rate, and Amount

Every item in a Refrens document uses three important system fields:
* **Quantity:** The number of units being billed
* **Rate:** The price charged per unit
* **Amount:** The final item value, calculated automatically as Quantity × Rate

You can customize how Quantity and Rate are calculated. The default Amount column should remain unchanged.

The system Quantity field is also used for inventory tracking. If a document records the sale or purchase of an inventory item, Refrens updates the stock using the value in this field.

This means:
* If the system Quantity is 10, Refrens records a movement of 10 units.
* If the system Quantity is 1, Refrens records a movement of only one unit.
* A value entered in a separate custom quantity column does not control inventory movement.

Keep this in mind while choosing how to set up your formula.

# Simple Column & Formula Customization

You can customize column names, visibility, order, and formulas from the **Edit Columns & Formulas** option while creating or editing a document.

For example, you can rename a column such as **Quantity** to **Units**, add a custom field such as **Length**, or hide columns that are not relevant to your business:

![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/animation_1asixv2.gif =714xauto)

If a column is being used in a formula, Refrens keeps the formula connected to that column even when you rename or rearrange it.

# Create a Formula Using Multiple Billing Parameters

Suppose you run an interior design or flooring business and charge customers based on the **area covered**.

For every item, you need to enter:
* Length
* Width
* Rate per square foot

The area can then be calculated automatically as: **Area = Length × Width**

Instead of calculating the area manually for every line item, you can create custom columns and let Refrens calculate it for you.

### => Step 1: Add Columns for Length and Width


![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/screenshot-www-refrens-com-202_tomj6j.png =600x668)
Open your document and go to **Edit Columns & Formulas**.

Add the following columns:
* **Length** — set the column type to **Number**
* **Width** — set the column type to **Number**
You can also rename these fields based on how your business works, such as:
* Length (ft)
* Width (ft)
Setting the column type to **Number** allows these values to be used in calculations.

### => Step 2: Rename The Quantity Column To Area


![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/screenshot-www-refrens-com-202_pmcv2s.png =600x676)


Next, rename the **Quantity** field to **Area**, as the quantity is being calculated based on area here.

Change its column type to **Formula**.

When creating the formula, you can select the columns you want to use. Refrens displays them using their column names, making the formula easy to understand.

For this example, the formula would be: `={Length}*{Width}`

This means: **Area = Length × Width**

For example, if you enter: **Length:** 12 & **Width:** 10

Refrens will automatically calculate: **Area = 120**

You do not need to calculate or enter the area manually.

### => Step 3: Use the Calculated Area for Billing

You can now use the calculated **Area** as the quantity for pricing.

For example:
| Item | Length | Width | Area | Rate | Amount |
| ---- | ---- | ---- | ---- | ---- | ---- |
| Wooden Flooring | 12 | 10 | 120 | ₹150 | ₹18,000 |

Here:
**Area = 12 × 10 = 120 sq. ft.**
and:
**Amount = 120 × ₹150 = ₹18,000**

This makes it useful for businesses such as:
* Interior designers
* Flooring contractors
* Fabric and textile businesses
* Glass and aluminium suppliers
* Printing businesses
* Construction contractors
* Furniture manufacturers

### Rename Columns Without Breaking Your Formula

You can rename a column whenever required.

For example, suppose your formula is: `={Length}*{Width}`

and you rename **Length** to **Length (ft)**.

Refrens will automatically update the formula shown in the editor to:  `={Length (ft)}*{Width}`

The calculation will continue working as before.

Similarly, rearranging or hiding a column does not change what the formula calculates.

This allows you to customize the appearance of your document without having to recreate your formulas.

### Hide Calculation Columns for a Cleaner Document

Some columns may be useful for calculations but unnecessary for the customer to see.

For example, you may want Refrens to calculate the **Area** internally while keeping the final invoice simple.

Go to **Column Settings** and hide any column you do not want to display on the document.

The column can still be used in calculations even when it is hidden.

This helps you keep your invoices clean while retaining the calculations required for billing.

# Two Ways to Set Up Custom Calculations

Suppose your price is calculated using: **Sqft × Number of Units × Rate per Sq. Ft.**

You can configure this in two ways.
### Option 1: Calculate the Quantity

Use this option when you can calculate the total billable quantity first and multiply it by a rate.

Set the system-linked Quantity formula to:
\={Sqft}\*{Number of Units}

Enter the price per square foot in the system-linked Rate field.

For example:
* **Sqft:** 100
* **Number of Units:** 2
* **Calculated Quantity:** 200
* **Rate per Sq. Ft.:** ₹50

Refrens calculates: Amount = 200 × ₹50 = ₹10,000

If the item is linked to inventory, Refrens will record the stock movement as 200 units.

Use this method only when the calculated Quantity also represents the correct inventory movement.

If your inventory is tracked in another unit, check the result before using the formula.

### Option 2: Add the Complete Calculation to Rate

Use this option when you want to perform the complete calculation in the Rate field.

Before using this method, consider whether the item is connected to inventory.

||| Inventory warning: Refrens tracks inventory using the system Quantity field. If you set Quantity to =1, Refrens will record the inventory movement as one unit, regardless of the quantity shown in another custom column. Use this method only if inventory tracking is not required or if one unit is the correct stock movement.

If this limitation does not affect your use case, set the system-linked Quantity field to:  =1

Then add the complete calculation to the system-linked Rate field:
\={Sqft}\*{Number of Units}\*{Rate per Sq. Ft.}

Using the same example:
* **Quantity used by Refrens:** 1
* **Calculated Rate:** ₹10,000

Refrens calculates: Amount = 1 × ₹10,000 = ₹10,000

The Amount will be correct, but an inventory-linked item will show a stock movement of only one unit.

For inventory-linked items, use Option 1 whenever the calculated Quantity represents the actual stock movement.

### How to Identify the Correct Quantity and Rate Fields

A formula column can have any name, but it must be connected to the correct system field.

Inside **Edit Columns & Formulas**, check the field displayed below the formula column:
* **Field: Quantity** means the result will be used as the document’s actual Quantity and for inventory movement.
* **Field: Rate** means the result will be used as the document’s actual Rate.
* The default **Amount** column continues to calculate Quantity × Rate.

For example, you may rename a Quantity-linked formula column to:
* Area
* Sqft
* Billable Units
* Chargeable Weight
* Number of Hours

Refrens will still treat its result as Quantity because it is linked to **Field: Quantity**.

###### Example 1: Calculate Charges Based on Time

Suppose you charge according to the time spent on a service.

Add the following columns:
* **Time (HH:MM:SS)** — select **Text**
* **Rate per Min (₹)** — select **Currency**

This setup requires the system Quantity to be set to 1.

||| Inventory warning: Setting Quantity to 1 means Refrens will record one unit of inventory movement for an inventory-linked item. Use this method only for services or other items where inventory tracking is not required, or where one unit is correct.

Set the system-linked Quantity field to: 1

![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/image-6_16bv6es.png =452xauto)

Then use this formula in the system-linked Rate field:
\={Rate per Min (₹)}\*(HOUR({Time (HH:MM:SS)})\*60+MINUTE({Time (HH:MM:SS)})+SECOND({Time (HH:MM:SS)})/60)

This formula converts the entered time into minutes and multiplies it by the per-minute rate.

For example:
| **Item** | **Time** | **Rate per Min** | **Amount** |
| ---- | ---- | ---- | ---- |
| Service | 00:03:40 | ₹3,000 | ₹11,000 |


![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/image-7_31ao85.png =929xauto)


The calculation is:

3 minutes 40 seconds = 3.6667 minutes
Calculated Rate = 3.6667 × ₹3,000 = ₹11,000
Amount = 1 × ₹11,000 = ₹11,000

###### Example 2: Currency Conversion and Admin Fees

Suppose you need to convert an INR amount into SGD and add an administration fee.

![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/image-9_iyr8uw.png =462xauto)


Add these columns:
* **Total Amount (INR)** — select **Number**
* **Conversion** — select **Number**
* **Rate (SGD)** — select **Formula**
* **Admin Fees (SGD)** — select **Number**

First, calculate the converted value:
\={Total Amount (INR)}/{Conversion}

This setup uses a system Quantity of 1.

||| Inventory warning: If the item is linked to inventory, setting Quantity to 1 will record only one unit of inventory movement. Use this setup only if inventory tracking is not required or if one unit is the correct quantity.

Set the system-linked Quantity field to: 1

![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/image-10_1qtpwyv.png =469xauto)


Then use the following formula in the system-linked Rate field:
\={Rate (SGD)}+{Admin Fees (SGD)}

For example:
| **Total Amount (INR)** | **Conversion** | **Rate (SGD)** | **Admin Fees** | **Amount** |
| ---- | ---- | ---- | ---- | ---- |
| ₹3,27,381 | 75.16 | SGD 4,355.79 | SGD 1,500 | SGD 5,855.79 |

![](https://storage.crisp.chat/users/helpdesk/website/-/a/b/8/8/ab884d8153100800/image-8_1yo1exv.png =734xauto)

Refrens calculates:
Rate (SGD) = ₹3,27,381 ÷ 75.16 = SGD 4,355.79
Final Rate = SGD 4,355.79 + SGD 1,500 = SGD 5,855.79
Amount = 1 × SGD 5,855.79

# Advanced Formula Functions

For most invoices, simple calculations such as addition, subtraction, multiplication, and division are enough.

Refrens also supports additional formula functions for cases where you need more control over the calculated value.
### A. Round a Value

Suppose your area formula is: `={Length}*{Width}`
and the result is :`127.4568`

If you want the Area to display only two decimal places, use: `=ROUND({Length}*{Width},2)`
The result will be: `127.46`

You can change the number of decimal places as required:

**2 decimal places** `=ROUND({Length}*{Width},2)`
**1 decimal place** `=ROUND({Length}*{Width},1)`
**No decimal place** `=ROUND({Length}*{Width},0)`

### B. Calculate a Square Root

To calculate the square root of a value, use: `=SQRT({Area})`

You can also use calculations inside the function where required.

### C. Find the Maximum Value

To use the larger of two values, use: `=MAX({Column 1},{Column 2})`

For example: `=MAX({Actual Weight},{Volumetric Weight})`

This can be useful when your billing depends on whichever value is higher.

### D. Calculate a Percentage

To calculate what percentage one value is of another, use: `=({Column 1}/{Column 2})*100`

For example: `=({Completed Quantity}/{Total Quantity})*100`

If **Completed Quantity** is 75 and **Total Quantity** is 100, the result will be: **75%**

### Using Formulas Across Different Documents

You can create formulas according to the billing structure required for a particular document.

Changing the name, order, or visibility of columns does not change which fields your formula refers to.

This means you can customize your document layout without worrying about formulas changing simply because a column was moved.

# Troubleshooting Incorrect Calculations

If your document shows an incorrect amount or inventory quantity, check the following:

1. Confirm that you are using the default Amount column.
2. Make sure the default Amount column has not been hidden and replaced with a custom column.
3. Check whether your custom formula is linked to **Field: Quantity** or **Field: Rate**.
4. If the complete calculation is in Rate, confirm that Quantity is set to =1.
5. If Quantity is set to 1, confirm that recording one unit of inventory movement is acceptable.
6. Do not rely on a custom quantity column to update inventory.
7. Make sure numerical inputs use an appropriate number or currency type.
8. Check that the column names used in the formula match those shown in the formula editor.
9. Make sure you have not deleted a column that is still used in another formula.
10. Test both the calculated Amount and inventory movement before using the setup in a final document.


# Things to Keep in Mind

* Always use the default Amount column.
* Do not replace it with a custom column for the final amount.
* Add custom calculations to the system-linked Quantity or Rate field.
* Refrens uses the system Quantity field for inventory tracking.
* A separate custom quantity column does not control inventory movement.
* Set Quantity to =1 only if inventory tracking is not required or one unit is the correct stock movement.
* If the actual inventory quantity must be tracked, make sure it flows through the system-linked Quantity field.
* Helper columns can be private or hidden and still participate in formulas.
* Renaming or rearranging columns does not break existing formulas.
* Remove a column from all dependent formulas before deleting it.
* Test the formula and inventory movement with a sample item before using it in a final document.

With custom columns and formulas, you can adapt Refrens documents to your billing method while keeping amounts, taxes, accounting entries, and inventory records accurate.

If you still have questions, reach out to us at [__care@refrens.com__](mailto:care@refrens.com) or contact us through chat support for quicker assistance.