## Minimum value of variables

## Minimum value of variables

(OP)

I'm using Crystal Reports 2013. I've created a formula with three variables which returns absolute values. I need the formula to return the level corresponding to the smallest absolute value.

WhilePrintingRecords;

Local NumberVar tenK := Abs (ToNumber({@AcctBal}) - 10000); //Level A

Local NumberVar fiftyK := Abs (ToNumber({@AcctBal}) - 50000); //Level B

Local NumberVar OnehundredK := Abs (ToNumber({@AcctBal}) - 100000); //Level C

For example if @AcctBal = 76K then tenK = 66K, fiftyK = 26K and OnehundredK = 24K

Therefore, I would want the formula to return Level C as this is the smallest absolute value.

If there is an easier way to return needed values instead of variables, please share.

Thanks,

D. Lee

WhilePrintingRecords;

Local NumberVar tenK := Abs (ToNumber({@AcctBal}) - 10000); //Level A

Local NumberVar fiftyK := Abs (ToNumber({@AcctBal}) - 50000); //Level B

Local NumberVar OnehundredK := Abs (ToNumber({@AcctBal}) - 100000); //Level C

For example if @AcctBal = 76K then tenK = 66K, fiftyK = 26K and OnehundredK = 24K

Therefore, I would want the formula to return Level C as this is the smallest absolute value.

If there is an easier way to return needed values instead of variables, please share.

Thanks,

D. Lee

## RE: Minimum value of variables

-LB

## RE: Minimum value of variables

At first I thought Level C would always return the minimum value as well, but because I'm interested in the absolute value, I don't think it will be.

For example if @AcctBal = 26,000 then the absolute value of tenK = 16,000, fiftyK = 24,000 and OnehundredK = 74,000 Therefore, I'd want the formula to return Level A

Here's what I'm ultimately trying to do. In my report, I have formula fields which return the number of employees (@CtEmployee) and the account balance (@AcctBal).

Levels and portfolio size are in a separate Excel File. I need Portfolio size on my report. Each level can have multiple portfolio sizes based on the number of employees. I need to first determine the level (based on AcctBal which is CLOSEST to 10K, 50K or 100K) and once I have that, get the correct portfolio size.

Level Employee Portfolio Size

10K 10 100000

10K 25 250000

10K 50 500000

50K 10 500000

50K 25 1250000

50K 50 2500000

100K 10 1000000

100K 25 2500000

100K 50 5000000

Hopefully, this makes a little sense

## RE: Minimum value of variables

What is the content of your formulas {@AcctBal} and {@CtEmployees}? And of any nested formulas?

-LB

## RE: Minimum value of variables

numbervar balper := tonumber(sum({table.amount},{table.account}})/distinctcount({table.employee},{table.account}));//not sure what your group is--used acccount here

numbervar a := 10000;

numbervar b := 50000;

numbervar c := 100000;

numbervar y := minimum([abs(balper-a),abs(balper-b),abs(balper-c)]);

if y = abs(balper-a) then "a" else

if y = abs(balper-b) then "b" else

if y = abs(balper-c) then "c"

-LB

## RE: Minimum value of variables

Of course, your formula is much cleaner than mine. There were no nested formulas and the formula fields were placed in GF1.

I clearly see the minimum function is able to select the minimum from a list of values within an array [] separated by commas.

Cool! Much appreciate the help.