Median ignoring Zero Values

[tlosgyl3]

I have a Table 1 that extracts data from table 2 and table 3 to Table 1 columns A and B.

Where Tables 2 and 3 contain no data Excel returns the balue 0 to table 1.

The tables look like this:

Table 1, Table 2, Table 3
Column A Column B Column A Column A
+T2 Cell A1 +T3 Cell A1 Data Data
Result Result
m 4 m 4
f 6 f 6
f 8 f 8
0 0
0 0

With the data above and using the array formula =MEDIAN(IF($A$5:$A$9>0,IF($B$5:$B$9>0,$B$5:$B$9)))

I get a correct result of 6. But if there is no Data in Table 3 Column A I get the result #NUM!

As this data gets used to complile a further data set I need it to return 0 (Zero).

Any help appreciated.

tlosgyl3








I want to find the median of the values in column B of table 1 subject to 2 conditions:

[Faz]

If you want a Median of an array, (i'll say A1:A10) not counting zero values, this is how you do it. (I'm refering to my example)

1 5
2 6
3 7
4 0
5 0
6 4
7 3
8
9 4
10 6

Cells like A8 with blank values must be turned into 0, hence B1 takes this equation:
=IF(A1="",0,A1)

C1 should take this equation:
=IF(B1=0,0,1)

Drag these formulae down to the bottom of the table

C11 can be =SUM(C1:C10)

Cell A11 can also be =SUM(A1:A10)

Cell C12 can take the equation
=(C12+IF(MOD(C11,2)=1,1,0))/2

Cell C12 tells you the position of the median within the range

Highlight the entire table (except the sums below) and sort by column B in descending order.

Column D can take numbered values, 1, 2, 3... with respect to the row number.

Now cell E1 takes this equation:
=IF(C$12=D1,B1,0)

Drag this column down to the bottom of the table

The cell that will give the median takes the equation:
=SUM(E1:E10)

Long winded, but it will give you the solution.

[Domenic]

Maybe...

=IF(SUM(($A$5:$A$9>0)*($B$5:$B$9>0)),MEDIAN(IF($A$5:$A$9>0,IF($B$5:$B$9>0
,$B$5:$B$9))),0)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
tlosgyl3 <[email protected]>
wrote:

> I have a Table 1 that extracts data from table 2 and table 3 to Table 1
> columns A and B.
>
> Where Tables 2 and 3 contain no data Excel returns the balue 0 to table
> 1.
>
> The tables look like this:
>
> Table 1, Table 2, Table 3
> Column A Column B Column A Column A
> +T2 Cell A1 +T3 Cell A1 Data Data
> Result Result
> m 4 m 4
> f 6 f 6
> f 8 f 8
> 0 0
> 0 0
>
> With the data above and using the array
> formula =MEDIAN(IF($A$5:$A$9>0,IF($B$5:$B$9>0,$B$5:$B$9)))
>
> I get a correct result of 6. But if there is no Data in Table 3 Column
> A I get the result #NUM!
>
> As this data gets used to complile a further data set I need it to
> return 0 (Zero).
>
> Any help appreciated.
>
> tlosgyl3
>
>
>
>
>
>
>
>
> I want to find the median of the values in column B of table 1 subject
> to 2 conditions:

[tlosgyl3]

Thanks both - I'll try both solutions and come back to you.

tlosgyl3

[tlosgyl3]

Domenic's solution works very well - many thanks - I have adopted it. Faz your solution was just a bit too long winded to be used where the function needed frequent repetition but many thanks anyway

tlosgyl3