Please help! IF formula with negative numbers

[mst21]

I'm trying to score based on a percentage another field is calculating.

• If Profitability is greater than 0%, score 2.
• If Profitability is between -5% and 0%, score 1.
• If Profitability is less than -5%, score 0.

I can get it to work for greater than 0% and for less than -5% but it won't score the percentages less than -5%. I've tried at least 4 variations on this and can't get it right. Does anyone know what I'm doing wrong?

[Ron_Daniel · Accepted solution]

@mst21 - are these scoring properly?



If so, here's the formula I used:

IF({Profitability Percentage} > 0, 2,

IF(AND({Profitability Percentage} >= -0.05, {Profitability Percentage} <= 0), 1,

IF({Profitability Percentage} < -0.05, 0)

)

)

[mst21]

That was it! Thank you so much @Ron_Daniel!! You are my hero. 🥇

[Alexey_Gusev]

Hi,
indeed, AND in second row doesn't needed. When formula counts 'false' of a first row, it means value is <= 0 ,  otherwise it returned 2 points. The same for last row and the formula can be

IF(Profitability > 0, 2,
IF(Profitability> = -0.05, 1
,0))

Also, when you use conditions without IF, counting them as numbers instead, 'true' converted to 1 , 'false' to 0
So, in you case this simple and strange formula will work as well

(Profitability>0)+(Profitability>=-0.05)