I'm trying to calculate the value in the Pipeline

This discussion was created from comments split from: How to make a number less than 0 show up as 0.

Answers

  • Tina Ciak
    Tina Ciak ✭✭✭✭✭

    I have a similar situation, but I'm trying to calculate the value in the Pipeline. If the forecasted value has been exceeded, I'm getting a negative number with my current formula. I need it to show zero instead of a negative.

    Below is what I'm currently using. Any help would be greatly appreciated!

    =IFERROR(IF(OR([START YEAR]@row = [CURRENT YEAR]#, [END YEAR]@row = [CURRENT YEAR]#), [IN YEAR $ FORECAST]@row - [CY SAVINGS REALIZED]@row, 0), "")

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    I think, for this situation, you need to wrap it in another if so that if it goes negative you just display a 0. Try this formula.

    =IFERROR(IF(OR([START YEAR]@row = [CURRENT YEAR]@row, [END YEAR]@row = [CURRENT YEAR]@row), IF([IN YEAR $ FORECAST]@row - [CY SAVINGS REALIZED]@row > 0, [IN YEAR $ FORECAST]@row - [CY SAVINGS REALIZED]@row, 0)), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!