Calculating the Median of Multiple Columns

[tsouth4]

I was able to find this formula when searching through the threads. It seems to work but only if all of the columns have a value. My problem is that some columns have null values. I don't want to just convert them to a 0 but exclude them entirely. Any help would be greatly appreciated.


WhilePrintingRecords;
Numbervar Array Values:=[{field.1},{field.2},{field.3},{field.4},{field.5},{field.6},{field.7},{field.8},{field.9}];
Numbervar Array Final := [0,0,0,0,0,0,0,0,0];
Numbervar m;
NumberVar n;
NumberVar MedianValue;

//sorting
for m := 1 to ubound(Values) do
(
Final[m] := Maximum(Values);
for n := 1 to ubound(Values) do
(
if Values[n] = Final[m] then
(
Values[n] := 0;
Exit For;
);
);
);

MedianValue := Final[round((count(Numbervar Array Final))/2,0)];

 

[MCuthill]

tsouth,

The only solution that comes to mind is to dynamically build your array. Unfortunately, I am not sure how specifically to go about it - but from a logic stance:

1) Check each Field for Null.
2) If Not Null, Add to Array
3) After Array Built, Calculate Median as above.

Hopefully another Tek-Tipper can help with the specifics!

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[tsouth4]

Thanks for the reply Mike. That is what I was thinking as well but have yet to determine a solution.

-Tim

 

[lbass]

Following up on Mike's, suggestion, try the following. First use formulas to eliminate the nulls, as in:

//{@fld1}:
if isnull({field1) then
0 else
field1

Then use this formula, which adds non-zero fields to array y. Of course this assumes that zero is an invalid value--if it can be then you could change the default for nulls to some very large value which you then omit from the array instead of zero:

WhilePrintingRecords;
Numbervar Array x :=[{@fld1},{@fld2},{@fld3},{@fld4},{@fld5}];
numbervar array y := 0;
Numbervar Array Final;
Numbervar m;
NumberVar n;
numbervar p := 0;
NumberVar MedianValue := 0;
for m := 1 to ubound(x) do (
if x[m] <> 0 then (
p := p + 1;
redim preserve y[p];
y[p] := x[m]
));
//sorting
for p := 1 to ubound do
(
redim preserve Final[ubound];
Final[p] := Maximum;
for n := 1 to ubound do
(
if y[n] = Final[p] then
(
y[n] := 0;
Exit For;
);
);
);
MedianValue := Final[round(p/2,0)];

-LB