How to use formulas

As you all know, Retable already has allowed you to use Excel formulas on your spreadsheets. Now, we have improved our formulation function. Let's check how to use formulas in Retable!

You can easily create simple, cell-basis excel formulas with Retable.

Retable formulas involve functions, numeric operations, logical operations, and text operations that operate on columns.

In a formula, you can reference columns by column name. To use the value of an existing column in your formula you can simply input the name of that field:

For example, if you wanted a formula that calculates a total price based on your Price and Quantity columns, you write a formula like this; {Quantity}*{Unit Price}

Column names should be wrapped in curly braces when writing formulas.

Formulas can include parentheses () to change the order of operations:

Full List of Retable's Formula Types

Creating a Formula Column Type

• Add a column and select the “formula” column type
• Click the formula area and start to type your formula. You can check the examples to learn how to use excel formula for your Retable spreadsheet.
• When you start to type, Retable's excel formula options will appear directly under the formula section, and you can select the formula you want to use.
• Open your parenthesis.
• Then all your columns will be listed directly under the formula section. You can select the column that you want to add to your formula.
• You can add more than one column to your formula by separating the columns with a comma.
• When you finish adding the columns you should close your parenthesis.

Now, your formula is ready! It is fully dynamic so, when you change any data in the columns that you used in your formula, the formula results will be automatically updated. 🚀

If you want to learn more about how to use Excel formulas in Retable, check out this quick video below! 👇🏻

‍

Difference Between Excel & Retable Formulas

Retable: The Hybrid Solution for Spreadsheet and Database Needs

Retable revolutionizes data management by combining the functionalities of spreadsheets and databases into one powerful tool. While traditional spreadsheet programs like Excel excel in organizing data in a tabular format, Retable takes it a step further by offering robust database capabilities.

As a spreadsheet, Retable allows you to structure your information in a familiar grid view, complete with columns, rows, and cells. You can input, manipulate, and analyze your data just like you would in a typical spreadsheet program. However, Retable's true strength lies in its database functionality.

With Retable, you can establish relationships between records, enabling dynamic data links that enhance data entry, formula calculations, and more. These powerful connections between records provide a deeper level of organization and enable advanced data management. Whether you're dealing with complex calculations, data dependencies, or intricate data structures, Retable empowers you to handle it all with ease.

But that's not all! Retable goes beyond the limitations of a traditional spreadsheet by offering a variety of alternative views for your data. Need a calendar view to track events and deadlines? Retable has you covered. Want a kanban board to manage your projects? Retable can do that too. In addition, you can leverage Retable's form builder to create custom data entry forms tailored to your specific needs. And if you prefer a visual approach, Retable's gallery view allows you to showcase your data in an eye-catching manner.

One notable difference between Retable and traditional spreadsheets is how formulas are handled. In a spreadsheet, you can place a formula in any cell and reference other cells within the sheet. However, Retable takes a more relational database approach. Formulas in Retable are applied to entire fields, known as columns, ensuring that the same formula is uniformly applied to every record in that field. This consistency streamlines your calculations and ensures data integrity across your dataset.

Excel

One of the key strengths of Excel lies in the ability to reference specific cells within formulas, allowing for dynamic data analysis and manipulation.

By referencing specific cells in Excel, you can create formulas that perform calculations or make decisions based on the values contained in those cells. For instance, let's consider a scenario where you have a table of purchases and want to identify the source of each purchase.

In Excel, you can achieve this by writing a formula that references each cell, such as A5, to check the type of source it represents. The formula you mentioned, =IF(A5='Online','Web','Store'), is a perfect example. It utilizes the IF function to check if the value in cell A5 equals "Online." If it does, the formula returns "Web"; otherwise, it returns "Store."

You can apply formulas across multiple cells, columns, or even entire sheets, enabling you to perform calculations, comparisons, and data transformations at scale.

Retable

In the context of Retable, formulas are applied to entire columns instead of specific cells, providing a streamlined approach to data management. Let's take the example of identifying the source of each purchase. With Retable, you can write a single formula that references the {Purchase Type} field, and the formula will automatically check this field for every record in the table.

The formula you mentioned, IF({Purchase Type}='Online','Web','Store'), perfectly illustrates this concept. It utilizes the IF function to evaluate whether the value in the {Purchase Type} field equals "Online." If it does, the formula returns "Web"; otherwise, it returns "Store." By applying this formula to the {Purchase Type} field, Retable ensures that it is uniformly applied to every record within that field.

This approach simplifies the management and consistency of formulas throughout your dataset. Rather than applying formulas individually to each cell, Retable's focus on field-level formulas streamlines data entry, calculations, and data integrity across your records. This unified approach enhances efficiency and ensures that formulas are consistently and accurately applied to all relevant data.

In summary, Retable's approach to formulas involves applying them to entire fields, rather than specific cells. This allows for streamlined data management and formula consistency across records. By leveraging field-level formulas, you can enhance the efficiency and accuracy of your data calculations and transformations within Retable's powerful database and spreadsheet hybrid environment.

Retable formulas work within tables, not across a project

Retable formulas by default only reference columns within the same table. However, there are ways to bring over a value from a different table in order to reference it in a formula column. (this approach uses referenced columns and lookup fields).

Formula Writing Tips

• Use curly brackets to reference column names; When referencing a column, it's the best way to surround the column name with curly brackets (e.g. {Price} ).
• If you have an existing formula, and then change a column name; Retable will automatically update your formula to reflect the new column name.
• Use parenthesis properly: Parenthesis are used in Retable to separate out statements and conditions (e.g. (10+12) * 8 ), as well as containers for formula function (e.g. CONCATENATE({Column 1}, {Column 2}) ). No matter how you use parenthesis, there must always be an opening and closing parenthesis.