One advanced way to use formulas is to use them to assist in converting existing data between data types.

Some conversions require no additional steps and can be handled automatically in Spreadsheet.com, like converting from data of the User data type to the Email data type. Other data may need to have some logic applied to it like when converting from a Select list to an Icon set.

Using Formulas to Convert Between Data Types

When creating your formula, remember that each data type requires your formula to use specific output syntax to return values in that data type. Some data conversions are done automatically when you change data types. For more information, see our article on how Spreadsheet.com converts data of different data types.

If your data conversion can't be done automatically, you may need to use formulas to return the correct value once you change a column, cell, or range of cells to the desired data type.

Let's take a look at how this can work.

Case Study: Converting from Select to Icon Set

In the example below, an "Opportunity Probability" worksheet, we're are tracking "Chance of Failure" as a select column in Column B. This data doesn't make sense in its current form, so instead we want to show this data as an Icon set with RYGBG options in Column C.

We can't just convert the Select data to an Icon set, as they won't transfer automatically. We've set Column C to the Icon set data type and can use an If statement in a formula to relate the data in Column B to the new Column C.

Our formula is:

=IF(B2="0%", "GREEN", IF(B2="25%", "BLUE", IF(B2="50%", "GRAY", IF(B2="75%", "YELLOW", IF(B2="100%", "RED")))))

We can paste that formula into Cell B2, and then use the drag handles to autofill the rest of the column.

To learn more about data types that can use formulas to assign values, check out our series of articles on advanced formatting. Or see our series of articles on Data Types for more about the various data types and what happens at conversion.