What If Formula / Goal Seek?

Options

I know we aren't supposed to compare Excel to SS, but I'm not finding success in my help search.


In Excel, there is a function in the Data Ribbon labeled (Forecast) What-If Analysis - it has 3 drop down selections: Scenario Manager - Goal Seek - Data Table

I am really interested in the Goal Seek option which allows you to select a cell, enter what you want the calculation to equal and then click a 2nd cell that allows for the change. My example has to do w/ % reduction.

Does SS have this option or even a formula workaround?

Any Help would be appreciated. I'm sharing screenshots of the selections. Not everyone is familiar w/ this tool w/in excel.


Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @liz.mayeux

    No. You have to know what you're doing in SmartSheet to create the proper math formula to make things work correctly.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @liz.mayeux

    As Mike noted, there isn't a built-in window in Smartsheet where you can do this directly, however I'm sure we can get there with formulas! See: Create and Edit Formulas in Smartsheet

    If I'm understanding you correctly, you want to be able to lock in a specific number in one cell and then have the two cells above it auto-calculate the percentage difference between that and a different number (including the numerical value of the percentage).

    Is your Smartsheet set up the same way as your Excel sheet, with the values all listed down one column? If so, you could use formulas like this:

    Note that I've applied different formatting from the toolbar menu at the top to get the $ sign or %, depending on what's needed for that cell.

    However if you have a lot of values to do this with, I would actually suggest adjusting your sheet format so those are columns instead of rows. This will allow you to apply formatting to the entire column and use column formulas for your calculations:

    Cheers,

    Genevieve

  • liz.mayeux
    liz.mayeux ✭✭✭✭✭
    Options

    This is fascinating. I will put it to use while building out my sheets this week and let you know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!