Formula Auditing/Evaluation

Amy Foeller

How do you audit or evaluate a formula in smartsheet? In MS Excel, you can audit/evaluate a formula to see the calculations for each step of the formula. I can't see a way to do this in smartsheet. I have a simple formula that should be giving me a specific value, but it's not, and I can't figure out why since I can't evaluate the specific calculation steps.

If formula evaluation isn't currently a possibility in smartsheet, I highly recommend that this functionality be added. Being able to evaluate a formula is key, especially when the formula is complex.

Thanks!

Paul Newcome

It isn't available. I suggest that you Submit a Product Enhancement Request when you are able.

In the mean time... Whenever I run into this issue, I break the formula out into multiple cells and use cell references to tie everything together so that I can see exactly how each piece is working.

Here is a (very basic) example.

My goal is to multiply 2 by 3 and then add 5. To break it down I would put

=2*3

in cell [Column1]1.

Then I would put

=[Column1]1 + 5

in another cell.

Now I have both parts of my formula broken out and I can evaluate each separately. Once I know everything is working properly, I can replace any cell reference with the formula that is in that particular cell to consolidate it into a single formula. I do this a lot with the more complex formulas that have a lot of nesting going on. It also helps me make sure all of my parenthesis are where they need to be.

itsnotbroken

Quick question

How do you evaluate a COLLECT() function?

The output is a range, so it can't sdisplay that

Paul Newcome

@itsnotbroken It gets nested inside of another function.