Forum Discussion
Median of a column of all numbers in the column that are greater than zero
I have a column of 10 dollar amounts. A certain number of those cells are filled with dollars over zero; the remainder, if any, are always filled with zero dollars. How can I find the MEDIAN of only the amounts greater than zero in a single formula without having to modify the range of the formula every time I need the MEDIAN value of the amounts greater than zero dollars?
I used the MEDIAN formula applied to all ten values in the column and was returned a median value that didn't return the correct value because 4 of the entries were zero dollars. So, I adjusted the range in the MEDIAN formula for only the 6 entries that were greater than zero and it returned what I expected. Is there a formula that would allow the MEDIAN to only consider values over zero$?
=MEDIAN( IF( A1:A10 > 0, A1:A10) )this assumes you run Excel >/= 2021 and A1:A10 contains Numbers only
3 Replies
=MEDIAN( IF( A1:A10 > 0, A1:A10) )this assumes you run Excel >/= 2021 and A1:A10 contains Numbers only
Wow, thank you. The formula works perfectly, EVEN if I don't put ">0" in the formula. I first tried =MEDIAN(IF(F:7:F16,F7:F16)), from the email I received, and it worked!! Then I signed in to the Excel Hub and saw the formula was: =MEDIAN(IF(F:7:F16>0,F7:F16)), which makes sense...and it WORKED ALSO. I wonder why both worked? But, thank you very much!
I wonder why both worked?
=MEDIAN( IF( F7:F16, F7:F16) )ignores zeros but not negative Numbers
=MEDIAN( IF( F7:F16 > 0, F7:F16 ) )ignores zeros and negative Numbers
I initially posted the 1st then revised it with the 2nd to be safe. If you don't work with negative numbers you can safely work with option 1
