Skip to main content
Solved

Please help! IF formula with negative numbers

  • November 25, 2024
  • 3 replies
  • 37 views
M
Forum|alt.badge.img+5

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?

Best answer by Ron_Daniel

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

3 replies

Ron_Daniel
Forum|alt.badge.img+21
  • Ron_Daniel
  • Inspiring
  • Answer
  • November 25, 2024

@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)
)
)
M
Forum|alt.badge.img+5
  • mst21
  • Author
  • New Participant
  • November 25, 2024

@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. 🥇

Ron_Daniel
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • November 26, 2024

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)

 



Ron_Daniel
Terms & ConditionsAccessibility statement