Smallest Average Variance

[Jcfx]

I am working on an inventory usage forecast that I have to port from a spreadsheet to Crystal and have stuck on the final calculation. This calculation will be used to identify which 'forecast' is the correct one to use for a particular part.

I am trying to calculate the minimum of a series of calculated fields. There are 10 fields (@F26 through @F40)in the detail section, each one is Similar to the formula below (Thanks to Lbass for the median calculation)

//@f26
//Median Average Difference for 6 month rolling

numbervar array x := [{@C26},{@D26},{@E26}];
numbervar i;
numbervar j:= ubound(x);
numbervar minx := 0;
numbervar maxx := 0;
numbervar medx := 0;

for i := 1 to j do(
if x[i] = minimum(x) then
minx := x[i];
if x[i] = maximum(x) then
maxx := x[i];
if minx = maxx then
medx := minx else
if x[i] > minx and
x[i] < maxx then
medx := x[i] else
if x[i] > minx and
x[i] = maxx then
medx := maxx
);
medx

How the report is laid out:

Group - Partnumber
Detail
@F26  @C26 @D26 @E26
@F27  @C27 @D27 @E27
:
:
@F40  @C40 @D40 @E40
________________________
Detail Results
.39  .08  .39  .01
.15  .09  .28  .15
:
:
.21  .01  .07  .21

I am looking for the Minimum of the @F fields, which using the three above would be .15

I understand how to do a minimum of a sum, but of individual fields?




Julie
CRXI CE10 / RS2005 Sql DB