Incorrect calculation results

[tanyaz]

Hello-

I have a straight forward calculation set up and its working fine for one of the dates but not for another date.  I'm not sure what is contributing to the error.  Appreciate any feedback.

This is the calculation for 'Open Rate Overall Status

IF [Assessment %]<=-5 then "Very Good"

elseif [Assessment %]>-5<=0 then "Good"

elseif [Assessment %]>0<=5 then "Fair"

elseif [Assessment %]>5<=15 then "Poor"

elseif [Assessment %]>15 then "Very Poor"

else Null()

endif

Calculation for Open Rate RAG

if [Open Rate Overall Status]="Very Good" or "Good" then "Green" elseif [Open Rate Overall Status]="Fair" or "Poor" then "Amber" else "Red" endif

Results: highlighted in red is the date with incorrect Status and RAG.  Correct results for 1/31/2021 should be "poor" and "Amber"

List of KPIs	Assessment	Reporting Date	As of Date	Assessment %	Open Rate Overall Status	Open Rate RAG
Open Rate	0.12	3/31/2021	1/31/2021	12	Good	Red
Open Rate	-0.063	3/31/2021	2/28/2021	-6.3	Very Good	Green

[Luke_C · Accepted solution]

@tanyaz 

Try this:

IF [Assessment %]<=-5 then "Very Good"

elseif [Assessment %]>-5 and [Assessment %] <=0 then "Good"

elseif [Assessment %]>0 and [Assessment %] <=5 then "Fair"

elseif [Assessment %]>5 and [Assessment %] <=15 then "Poor"

elseif [Assessment %]>15 then "Very Poor"

else Null()

endif

[tanyaz]

Thank you so much @Luke_C .  Works perfectly now!!