How Do I Return a Negative Number

Options

Hi,

I need to return a negative number in a cell. The example is that a Change Order has been submitted, but not yet approved. Therefore, it should not be counted in the Actual Price column.

If it is Denied, it would also return a negative number.

Thank you.

Best Answer

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Margaret Evans ,

    I would need to see how your sheet is constructed to give a specific answer. However, you'll need to use a form of logic (IF, sumif, countif, not, <>, etc) to identify items to be included in the sum. You'll be checking for a complete and approved change order before adjusting the actual price with the change order amount.

    You can attach a screen shot here if you need more help. Please remove any sensitive information.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Margaret Evans
    Options

    Hi Mark,

    Thanks. I have attached a screenshot here of the formula, as well as various If Statements from the last week. I hope that helps rather than hinders.

    So here we go... When a Change Order is ticked Submitted in the Approval Status column, it needs to return a negative number in the Actual Price column.

    Also, if the Actual Price column is LOWER than the Total Price column, it needs to return a negative Actual Price.

    If Denied, it should return a negative number so it won't ever be counted in the budget.

    Once the Submitted CO is Approved in either instance, it should change to a positive number.



    Thank you.

    Margaret 😊

  • Margaret Evans
    Options

    I DID IT!!!!!

    I believe that I was making it way too complicated when I asked my question!

    Here is the solve!

    =IF(ISBLANK([Approval Status]@row), ABS([Actual Price]@row), IF([Approval Status]@row = "Approved", ABS([Actual Volume]@row * Estimate@row), IF([Approval Status]@row = "Submitted", SUM(-[Actual Price]@row), IF([Approval Status]@row = "Denied", SUM(-[Actual Price]@row, IF(ISBLANK([Approval Status]@row), ABS([Actual Price]@row), ""))))))

    This brings a positive number if the Change Order is Approved, but a negative number if Submitted or Denied. Again, if approved, it brings a positive number.

    Yaaaay!😅

  • Margaret Evans
    Options

    Thank you! Both Smartsheet Support and I were making it way too complicated with the If(and. However, it did make perfect sense that way; just not to Smartsheet! LOL! 😄

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!