Average lowest 3 numbers in a list of many

[Jillp]

We have several jobs with the same operations; we would like to average the lowest (3) Act Times. I'm not sure how to select the lowest (3) numbers. Any advice would be greatly appreciated. - Jill

Product Operation Est Time Act Time
Handwheel 50 4.50 5.25
Handwheel 50 4.50 2.25
Handwheel 50 4.50 2.75
Handwheel 50 4.50 3.00
Spinwheel 20 12.89 21.68
Spinwheel 20 12.89 16.66
Spinwheel 20 12.89 20.16
Spinwheel 20 12.89 16.50
Spinwheel 20 12.89 18.00
Spinwheel 20 12.89 16.00

 

[hilfy]

I would initialize an array that will hold 3 items and then work with that to determine the three smallest numbers. It might look something like this:

{@GetSmallest}
NumberVar Array smallest;
Local NumberVar i := 1;
Local BooleanVar found := false;
if OnFirstRecord then 
  smallest := [9999,9999,9999];  //Pick a number that will always be greater than the possible values for ActTime
While i < 3 and not found Do
(
  If smallest[i] > {Table.ActTime}
  (
    smallest[i] := {Table.ActTime};
    found = true;
  );
  i := i + 1;
);
{Table.ActTime}

This will give you an array of the three smallest values in your data. Us this formula in place of the Act Time field on your report. NOTE: You may have to play with the semi-colons to get this to save - I can't remember whether you need one after the ')' at the end of both If statements.

You then use something something like the following to calculate the average. This must be placed in either a group or report footer:

{@DoAverage}
NumberVar Array smallest;
Local NumberVar i := 0;
Local NumberVar arraySum := 0;
Local BooleanVar done := false;
While i <=3 do
(
  i := i + 1;
  if smallest[i] < 9999  then  //Use the same number that you use to initialize the array in the previous formula
    arraySum := arraySum + smallest[i]
  else
  (
    if (\i > 1 then
      i := i - 1;
    done := true;
  );
if i > 0 then  //prevent division by 0 error
  arraySum/i
else 
  0
)

These formulas will calculate a grand total average. If you need it by group, in the first formula change
[li]if OnFirstRecord then[/li]
to
[li]if OnFirstRecord or {Table.group field} <> previous({Table.group field}) then[/li]
replacing {Table.group field} with the field that you're grouping on.

-Dell

Senior Manager, Data & Analytics
Protiviti

http://www.protiviti.com

 

[Jillp]

Thanks for the reply - I'll take a look at this.