Crystal If Statement Help

evend
evend used Ask the Experts™
on
I need a formula to do the following:

Review a field called Per Diff (which is the difference between two values)  

If 0% Display "Perfect"

If it is between  -.10%  and  10%  Display "Good"

If between -25%  and -11% "Fair"

If between 11% and 25% "Fair"

else

Bad

Thanks in advance for help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Set the formula to Basic syntax, then:

select case abs({perdiff})
      case 0
         formula = "Perfect"
      case 0 to .1
          formula = "Good"
      case .1 to .25
          formula = "Fair"
      case else
          formula = "Bad"
end select

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
If you prefer the IF method

If abs({perdiff}) = 0 then
   "Perfect"
Else If abs({perdiff}) <=  0.1 then
   "Good"
Else If abs({perdiff}) <=  0.25 then
    "Fair"
Else
    "Bad"

mlmcc

Author

Commented:
This works if they are all positive percentages.  The return for the Per Diff % amount can be a negative .08% or a postive .08%?  Basically it is howing you a percentage over or short for a pick for our fullfillment process.  
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Top Expert 2010

Commented:
Abs() means absolute value, so the sign doesn't matter.

Author

Commented:
I only get results that come up as Perfect or Bad?

This is the formula for the PerDiff Field.  Could it have to do with it?


if Average ({Vend_Visit_Items.par}, {Vend_Visit_Items.description}) = 0 then
    0
else


{@Est Diff}/Average ({Vend_Visit_Items.par}, {Vend_Visit_Items.description})
Top Expert 2010

Commented:
What are the actual values of PerDiff?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Agree.  Sounds like the PerDiff field isn't between 0 and 1

mlmcc

Author

Commented:
They can be either positive or negative values

Examples

-0.13
-1.25
0.25
0.12
-0.38
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you  put the PerDiff formula on the report what values do you see?

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial