How Do I Return a Negative Number

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.

Find more posts tagged with

Accepted answers

Mark Cronk

Well done.

Mark

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.