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.
Best Answer
-
Well done.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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.
-
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 😊
-
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!😅
-
Well done.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!