Skip to main content

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?

@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 - 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)
)
)

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

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)

 



Reply


Terms & Conditions