What If Formula / Goal Seek?

liz.mayeux

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.

SSGoalSeek.docx

Mike TV

@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.

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

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