How Do I Return a Negative Number

Margaret Evans

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.

Mark Cronk

Well done.

Mark

Mark Cronk

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

Margaret Evans

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.

IF STATEMENTS - 11-18-20.docx

Submitted - Not Returning Negative Number.pdf

Thank you.

Margaret 😊

Margaret Evans

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!😅

Mark Cronk

Well done.

Mark

Margaret Evans

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! 😄