IF Statement Help

Nick Riek
Nick Riek
edited 12/09/19 in Formulas and Functions

I have a sales price database with one column for a retail price and another for a sale price. I'd like to make a 3rd column with a formula that populates the Save __% for our Marketing team. I made this formula which works with one small issue I'd like to resolve if possible.

=IFERROR(("Save " + (ROUND(1 - ([Sale Price]102 / [Retail Price]102), 2) * 100) + "%"), "")

The formula is working in giving the accurate savings while also showing a result that populates it to read easily with the word "Save __%". With the IFERROR it's also blanking the cell if there's no data in the two columns. The one result that is bugging me is that items that are not on sale say "Save 100%) because there's no value in the sale price column for the formula to use.



Is there a way to blank the column if the result is "Save 100%"?

Capture.JPG

Comments

  • Hi Nick,

    You might try adding an IF statement to your function that will return blank if the sale price is blank. For example: 

    =IFERROR(IF(ISBLANK([Sale Price]1), "", ("Save " + (ROUND(1 - ([Sale Price]102 / [Retail Price]102), 2) * 100) + "%"), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!