Can you run a formula if and only if the number is negative?
Here is what I am trying to do:
Each month we want to assign a score to the person doing the tasks based on meeting due dates.
Each month they start with 20 points. (Maximum of 20 points)
For every day past the due date, they lose 2 points.
I want a summary field where I can subtract the lost points from 20, but not add points if they complete ahead of schedule.
Here is what I tried:
The Days to Complete column has a simple formula: Date Due - Date Complete
The red circle shows the number for a task 1 day overdue. That works.
The Score Column has a simple formula that is =Days to Complete * -2 - The negative 2 produces a positive number to subtract the loss of 2 points.
Then my summary field, "Credit Card receipts" shows my formula 20 - Score.
The problem is if the person completes the task ahead of schedule they will get a positive number in the Days to Complete column and then the formula would produce a score of 22 in the summary field.
Is there a better way to do this? Or are there conditional formulas that will allow me to do this so that I don't have to manually complete this each month?
Best Answer
-
Hi @Andrea Westrich,
I think we can take a really simply approach to this and adjust the formula in the "Credit Card Receipts" to have a max of 20.
=IF(20 - Score1 > 20, 20, 20 - Score1)
If 20 - the score is greater than 20, return a value of 20, otherwise return 20 - Score
Answers
-
Hi @Andrea Westrich,
I think we can take a really simply approach to this and adjust the formula in the "Credit Card Receipts" to have a max of 20.
=IF(20 - Score1 > 20, 20, 20 - Score1)
If 20 - the score is greater than 20, return a value of 20, otherwise return 20 - Score
-
-
I'm glad this worked for you. Have a lovely day!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!