Can you run a formula if and only if the number is negative?

Andrea Westrich

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?

Summer

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

Summer

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

Andrea Westrich

@Summer GENIUS!!! Worked beautifully!

Thank you so much!!

Summer

I'm glad this worked for you. Have a lovely day!