Creating and Editing Formulas

  • Updated

Like other spreadsheet software, Spreadsheet.com allows you to use formulas in cells to display values. By default, worksheets show a formula bar where you can go to view, input, and edit formulas.

Spreadsheet.com includes many of the functions you are already familiar with, mathematical ones like addition (+), SUM, or ODD; logic ones like IF, AND; Text ones like CONCATENATE; Lookup functions like VLOOKUP, INDEX, MATCH; Financial functions like IRR, NOMINAL; and many more. But also, Spreadsheet.com features unique functions custom-tailored to our data types, like PARENTCELL for cell hierarchies and COUNT computations for related rows.

Create and edit functions for worksheets where you are budgeting, like estimating project costs, opportunity estimates in a CRM, or for managing your company's inventory. Or create formulas to handle data in unique ways, for instance, to determine the number of stars in a rating cell or to assign users with formulas.

 

How to add a formula to a cell

To add a formula to a cell, first, click on the desired cell, and then start by typing the = sign. The = sign is an indication that you are about to type in a formula. Next, begin typing your formula, including functions and cell references as necessary.

blobid0.png

Functions

A named function is a fixed formula that performs a specific calculation. For instance, the function SUM() returns the mathematical sum of all values put inside the parenthesis. E.g. =SUM(2,4) would return 6. Spreadsheet.com includes functions that you may be familiar with from other spreadsheet software, and a few new functions specific to Spreadsheet.com's unique functionality.

See our list of functions or collection of function references for more insight into specific functions.

Additionally, formulas can include basic mathematical functions and their symbols, like addition (+), subtraction (-), division (/), and multiplication (*).

Cell References

Formulas can have inputs that reference the values in specific cells. For instance, =SUM(A1,B1) would return the sum of the values currently in A1 and B1. In this way, A1 and B1 not only identify a specific cell but serve as references to the values in that cell.

If you change the values in those cells, for instance increasing the value in A1, then the result of your formula will also update.

Formula Bar

The formula bar allows you to easily add and edit existing formulas. To access the formula bar, click on the desired cell and then click on the formula bar at the top. From here you can click and edit the formula as desired.

For instance, in our hiring budget worksheet below, we are missing formulas to sum quarterly budgets. So we add formulas by clicking on the cell, entering our function and cell references, and pressing enter. We then add a formula to the empty row below. This time, when we press enter, we realize we have made a mistake, and fix it by editing the formula in the formula bar. Finally, we drag both formulas across, applying them to the selected cells.

Formula.gif

Formulas and data types

Formulas can also be used in cells, ranges of cells, or columns that are set to unique Spreadsheet data types. For instance, in a Date column, you can use functions to edit the date values that are returned. Either by using date functions like TODAY() or other mathematical or text functions like MAX() to return a date.

Number data types can obviously be used in formulas, are often the default date type for formula cells. However other digit data types can both be used in formulas or can have formulas calculate their output, such as percent, date, duration, and others. Still other data types, like rating or certain icon sets may display as an icon, but contain values behind them that can be referenced in formulas or can use formulas to determine their value.

For instance, in the animation below, we set a column to the rating data type with currency symbols, then constructed nested IF statements to return a project scale depending on the Total Cost of the project.

Rating_Formula.gif

For complete details on data types, see our introduction to data types article or all our data types articles. Or for information about data types with unique relationships with formulas, see our articles on the Column Formula data type, Related Rows Lookups and Related Row Computations, and Formulas in Primary Columns. Also, see our articles on assigning users, attachments, and other ways to assign values using formulas.