Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
calculating wtd number with conditional array formula | Excel Discussion (Misc queries) | |||
How do I paste horizontal references in large array | Excel Worksheet Functions | |||
MEDIAN() as array function? | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |