Calculating Median Value in a large array
 

I am trying to calculate the median value in a column of 51500 values, doing
so once for 83 separate columns. The formula =MEDIAN(array point 1:array
point 51500) works fine for most of the columns, but for others the system
returns a blank cell. There are data points in those arrays, so the blank
cell is confusing. Is there a way to instruct the system to look only at the
values with a whole number in the cell and to calculate the median from that
portion of the array, without sorting the column or modifying it?

Another approach would be to ask the system to return the MIN value. The
problem there lies in the fact that the system always presents zero as the
minimum, because is is. However to do the median calculation above I need
the smallest value above 0 in the array, is there a way to instruct the
system to present the smallest value above 0 in an array.

Calculating Median Value in a large array
 

=SMALL(A1:A50000,2) will return the 2nd smallest value.

Dave
--
Brevity is the soul of wit.


"willcozz" wrote:

I am trying to calculate the median value in a column of 51500 values, doing
so once for 83 separate columns. The formula =MEDIAN(array point 1:array
point 51500) works fine for most of the columns, but for others the system
returns a blank cell. There are data points in those arrays, so the blank
cell is confusing. Is there a way to instruct the system to look only at the
values with a whole number in the cell and to calculate the median from that
portion of the array, without sorting the column or modifying it?

Another approach would be to ask the system to return the MIN value. The
problem there lies in the fact that the system always presents zero as the
minimum, because is is. However to do the median calculation above I need
the smallest value above 0 in the array, is there a way to instruct the
system to present the smallest value above 0 in an array.

Calculating Median Value in a large array
 

It doesn't make sense that your Median formula returns a blank.

=MEDIAN(A1:A10)

Should return *something*, either a number or an error but not a blank.

To find the smallest value 0:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

Biff

"willcozz" wrote in message
...
I am trying to calculate the median value in a column of 51500 values,
doing
so once for 83 separate columns. The formula =MEDIAN(array point 1:array
point 51500) works fine for most of the columns, but for others the system
returns a blank cell. There are data points in those arrays, so the blank
cell is confusing. Is there a way to instruct the system to look only at
the
values with a whole number in the cell and to calculate the median from
that
portion of the array, without sorting the column or modifying it?

Another approach would be to ask the system to return the MIN value. The
problem there lies in the fact that the system always presents zero as the
minimum, because is is. However to do the median calculation above I need
the smallest value above 0 in the array, is there a way to instruct the
system to present the smallest value above 0 in an array.

Calculating Median Value in a large array
 

I agree, it works for most columns in the table, but not for all. Could be a
cell that Excel is interpreting as non-numeric. The data is an export from a
brio query I had run and I'm using Excel to organize the data.

"T. Valko" wrote:

It doesn't make sense that your Median formula returns a blank.

=MEDIAN(A1:A10)

Should return *something*, either a number or an error but not a blank.

To find the smallest value 0:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

Biff

"willcozz" wrote in message
...
I am trying to calculate the median value in a column of 51500 values,
doing
so once for 83 separate columns. The formula =MEDIAN(array point 1:array
point 51500) works fine for most of the columns, but for others the system
returns a blank cell. There are data points in those arrays, so the blank
cell is confusing. Is there a way to instruct the system to look only at
the
values with a whole number in the cell and to calculate the median from
that
portion of the array, without sorting the column or modifying it?

Another approach would be to ask the system to return the MIN value. The
problem there lies in the fact that the system always presents zero as the
minimum, because is is. However to do the median calculation above I need
the smallest value above 0 in the array, is there a way to instruct the
system to present the smallest value above 0 in an array.

Calculating Median Value in a large array
 

Thanks for the formula, unfortunately it didn't return a value. I copied and
pasted the formula only modifying it to update the array range. Not sure
what I might have done to modify it so it's not working.

"Dave F" wrote:

=SMALL(A1:A50000,2) will return the 2nd smallest value.

Dave
--
Brevity is the soul of wit.


"willcozz" wrote:

I am trying to calculate the median value in a column of 51500 values, doing
so once for 83 separate columns. The formula =MEDIAN(array point 1:array
point 51500) works fine for most of the columns, but for others the system
returns a blank cell. There are data points in those arrays, so the blank
cell is confusing. Is there a way to instruct the system to look only at the
values with a whole number in the cell and to calculate the median from that
portion of the array, without sorting the column or modifying it?

Another approach would be to ask the system to return the MIN value. The
problem there lies in the fact that the system always presents zero as the
minimum, because is is. However to do the median calculation above I need
the smallest value above 0 in the array, is there a way to instruct the
system to present the smallest value above 0 in an array.

Calculating Median Value in a large array
 

Median will ignore text, logical values (TRUE, FALSE), and empty cells.

Biff

"willcozz" wrote in message
...
I agree, it works for most columns in the table, but not for all. Could be
a
cell that Excel is interpreting as non-numeric. The data is an export
from a
brio query I had run and I'm using Excel to organize the data.

"T. Valko" wrote:

It doesn't make sense that your Median formula returns a blank.

=MEDIAN(A1:A10)

Should return *something*, either a number or an error but not a blank.

To find the smallest value 0:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

Biff

"willcozz" wrote in message
...
I am trying to calculate the median value in a column of 51500 values,
doing
so once for 83 separate columns. The formula =MEDIAN(array point
1:array
point 51500) works fine for most of the columns, but for others the
system
returns a blank cell. There are data points in those arrays, so the
blank
cell is confusing. Is there a way to instruct the system to look only
at
the
values with a whole number in the cell and to calculate the median from
that
portion of the array, without sorting the column or modifying it?

Another approach would be to ask the system to return the MIN value.
The
problem there lies in the fact that the system always presents zero as
the
minimum, because is is. However to do the median calculation above I
need
the smallest value above 0 in the array, is there a way to instruct the
system to present the smallest value above 0 in an array.