Trying to get formula to ignore values below 0

Topic Labels: Formulas
2823 3
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

Hi there. I’m trying to calculate a percentage but only if it’s positive. As an example, one record might say 7000 for the total and 1000 for the cost and I want to know what percentage that is. But I have some records that are 0/0 which just gives me “NaN” and some that the total is 0 but the cost is negative by a large number and then it’s returning “-infinity”

Ideally I’d like anything with NaN or a negative value to show up as 0 so that I can see the average for all the positive records. I was trying to use the IF function in the formula but I see nothing in the support section about using IFs with formulas besides just text labels. Here’s what I had as my formula but it won’t let me save it because it says it’s wrong so I’m coming here.
IF({Cost} > 0), “({Total}/{cost})”, “0”

I also tried:
IF({Cost} > 0), “Value({Total}/{cost})”, “0”

IF({Cost} > 0), “=({Total}/{cost})”, “0”

But none of these work and I don’t understand what the problem is

3 Replies 3

Hi @Emma_MH - try this:

IF(Cost > 0, Total/Cost, 0)

Strictly speaking to get a total as a percentage of cost I would do:

IF(Cost > 0, Total*100/Cost, 0)

I think the problem you have is that if Total and Cost are numbers, you are turning them into strings with the quotes.

Screenshot 2019-04-23 at 18.26.11.png

Total and Cost are decimal numbers, allowing negatives:

Screenshot 2019-04-23 at 18.26.24.png

Hope this helps


Also you can format the percentage column as a percentage:

Screenshot 2019-04-23 at 18.32.07.png

6 - Interface Innovator
6 - Interface Innovator

Thank you! That worked!