Round Up to 0

I have a math column right now called TOTAL HEALTH that adds in the values from 3 other columns (more will be added).
I never want it to display negative amounts. I want it to default to 0 if the TOTAL HEALTH ever goes below 0.

For example, user has 5 TOTAL HEALTH. Some happens where they get hit for -8. I do not want the TOTAL HEALTH column to show -3. I want it to recognize that it is going to be less than 0 so default it to 0.

You can create an if then column that will check if the value is below zero. If it is, then return zero. If it’s a positive number, then return the number. You can display the if then column value in your app.

That solved it with the exception of transferring over the units. I used a heart emoji for my total health column. With this new if-then column, it is only taking the value. Any fix for that?

Nevermind, I figured it out. Made a template column and added in the units. I can display that template column then. Thanks for your help!

1 Like

If you don’t want to use extra column(s) then you can use ArrayFormula in googlesheet as below where E2, F2, G2… are your columns to calculate sum.

={“Total”;ARRAYFORMULA(if(E2:E<>"",(if(E2:E +F2:F < 0,“:heart:”,E2:E + F2:F)),""))}

Hi…I just noticed one problem. So I can set that the user goes back to 0 using the if-then column as what they see. However, that math column still has them at -3. So if they complete a challenge that earns them 5 health, they are only getting to +2

Yeah, I figured that would be the next issue. What you may need to do is create another math column that adds the 3 other columns together (X). Then create another match column that multiples the value by -1 (Y). Then create and if then column that checks if the total health in the (X) is equal or greater than zero and return 0. Else return the value from the (Y) column. Make this final if/then column part of the math for your original health total column. That way if the health value ever becomes less than zero, it will add the inverse of that difference, otherwise it will just add zero.