Trying to expand my knowledge of writing formulas

Marios Hadjikyriacou

Im having a little bit of a hard time trying to make formulas longer and saving column space(s). For instance when I run into an issue of creating a formula in 1 cell i sometimes create a new column and put the formula I wanted to add in there. 

This takes up space which can be free'd up and when the sheet gets over 1000 rows long it eats up the smartsheet. I have looked at the smartsheet formulas reference sheet and I am just not sure how to add to an existing formula to make it work the way I want it to.

 

For Instance:

 

I'm using a formula that looks like this =YEAR([example]1). I want to add an iferror to it, so if it errors it will give a value of 0 back. (Which is where I would make a new column called

=IFERROR(=YEAR([example]1, 0).

Mike Wilday

You only want to use the =sign at the beginning of the formula like this. Whenever you nest a formula you just put it inside the ('s

=IFERROR(Year([example]1. 0)

Marios Hadjikyriacou

Thank you Mike that was helpful. 

If i wanted to make a nested formula how would I go about doing so? Is there a priority of which function needs to be first in order for it to work? I tend to get errors or invalid argument alot.

 

 

Paul Newcome

Here is how I helped a coworker get used to nesting formulas:

 

Spread them out into as many different pieces as you can. Lets just say for the sake of the example I am trying to do what you were wanting to accomplish in your original post. I have two parts, so I would use two different cells for each part to start with.

 

The basic sheet layout (column names in bold/row numbers underlined):

 

          Data                             Formula

1    06/06/2019                  =YEAR(Data1)

2                                        =IFERROR(Formula1, 0)

.

Then you would start working backwards from your last formula.

 

=IFERROR(Formula1, 0)

.

Formula1 is referencing a cell that has another formula in it. So just take the formula from that cell (not including the equals sign) and drop that in place of the cell reference.

 

Now you have 

 

=IFERROR(YEAR(Data1), 0)

.

If you break things down into as many pieces as possible and use cell references in each part, it helps to troubleshoot any issues, it allows you to see if maybe you need to tweak or add another portion for everything else to work together, and then the cell references act as placeholders showing you where each portion should go in the final nested formula.

Mike Wilday

You are always free to post in here if you get errors. But as a first resort when trying to nest functions I always start with the peices and make sure they give me what I need (Like Paul is suggesting).

Then if I get the innards working, and another layer is breaking at some point, then I refer to this article to determine what might be causing the error: 

• https://help.smartsheet.com/articles/2476176-formula-error-messages

And I also check to make sure the formula is set up correctly using this help article.

• https://help.smartsheet.com/functions

Reviewing the functions to make sure you are complying by the rules always helps when troubleshooting. 

A missing comma, a missing closing parenthesis are common errors but one that is hard for some to figure out is created by smart quotes when you compose your formulas in a word processing program and it substitutes straight quotes for smart quotes (those nicely slanted quotes that look good in documents but are terrible for computations). Word and Google Docs both transform straight quotes into smart-quotes. Smartsheets is particular to straight quotes. Always use a text-editor like notepad++ or Atom if you are on a Mac to compose your formulas. They also have embedded coding tools that help you make sure you close all your formulas with ending paragraph marks.  

• https://notepad-plus-plus.org/download/
• https://atom.io/ 

Hope that helps!