Reset Value to 0, No negative numbers

Is it possible to have a number go down the 0, but not have it have a negative value? I'll share my sheets to illustrate.

So this is my current sheet. I am running into an issue where people are having a negative total (picking up a ton of OT shifts) and they accumulate a big negative total value, so when they do no-calls, it's still keeping them in the negative.

This is what I want the sheet to do, is basically 0 Jake out and not let him go under 0 points. So when he gets a 4 point tardy, he gets the full 4 points.

I feel like this the missing piece, and I hope I gave enough information. Any help is appreciated.

Thank you.

Find more posts tagged with

Formulas

Accepted answers

All comments

KPH

What is the formula you are using in the Total column? It looks like a running total of tardy minus overtime per name?

Dan B.

Yeah, it's Tardy-OT = Total

I'll have to add in something to make sure it matches the right employee, probably in index match or basic [Name]:[Name] formula.

The issue I'm stuck on is dealing with negative values. I don't want employees to pick up a ton of OT to use a buffer from keeping their points high.

KPH

My thought was to use an IF function in the calculation of the total. So, if the total result is less than or equal to 0 the result is 0. If it is not, then the result is the true result of the calculation.

This will only work if you are using the prior row's total to calculate your total and without seeing your formula, I am not sure if this is the case. I mocked up a simple example to explain this but I cannot get the same result as you for 11/16/23 which makes me think my guess of your formula is not correct.

The formula I have assumed for the original total (in row 2 and then dragged down) is

=[Total (original)]1 + Tardy@row - Overtime@row

I have put this into an IF function to create the adjusted total, like this

=IF([Total (adjusted)]1 + Tardy@row - Overtime@row <= 0, 0, [Total (adjusted)]1 + Tardy@row - Overtime@row)

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.