Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Median ignoring Zero Values
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$90,IF($B$5:$B$90,$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 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=475831 |
#2
|
|||
|
|||
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. -- Faz ------------------------------------------------------------------------ Faz's Profile: http://www.excelforum.com/member.php...o&userid=27830 View this thread: http://www.excelforum.com/showthread...hreadid=475831 |
#3
|
|||
|
|||
Maybe...
=IF(SUM(($A$5:$A$90)*($B$5:$B$90)),MEDIAN(IF($A$ 5:$A$90,IF($B$5:$B$90 ,$B$5:$B$9))),0) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , tlosgyl3 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$90,IF($B$5:$B$90,$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: |
#4
|
|||
|
|||
Thanks both - I'll try both solutions and come back to you. tlosgyl3 -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=475831 |
#5
|
|||
|
|||
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 :) -- tlosgyl3 ------------------------------------------------------------------------ tlosgyl3's Profile: http://www.excelforum.com/member.php...o&userid=28074 View this thread: http://www.excelforum.com/showthread...hreadid=475831 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Ignoring 0 values in the series data | Charts and Charting in Excel | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
ignoring null values in graph | Charts and Charting in Excel | |||
How do I do count calculations ignoring duplicate values | Excel Discussion (Misc queries) |