Median - Ignore blanks
 

What is the formula to ignore blanks? In other words, there are some 0, so I
want to include the 0. However there are some blanks and I want to exclude
the blanks.

Thanks in advance.

Answer: Median - Ignore blanks
 

To calculate the median and ignore blanks in Excel, use the following formula:

Here, "range" refers to the range of cells that you want to calculate the median for. The IF function checks if each cell in the range is not blank, and returns an array of values that meet the criteria. The MEDIAN function then calculates the median of the non-blank values.

To use this formula, follow these steps:

1. Select the cell where you want to display the median.
2. Type the formula =MEDIAN(IF(range<"",range)), replacing "range" with the actual range of cells you want to calculate the median for.
3. Press Ctrl+Shift+Enter to enter the formula as an array formula. This tells Excel to treat the formula as a single unit and calculate it for each cell in the range.
4. The cell should now display the median of the non-blank values in the range.

Median - Ignore blanks
 

According to Excel Help, MEDIAN ignores blank cells (and those
containing text or logical values).

Hope this helps.

Pete

On Nov 24, 8:27 pm, jhicsupt
wrote:
What is the formula to ignore blanks? In other words, there are some 0, so I
want to include the 0. However there are some blanks and I want to exclude
the blanks.

Thanks in advance.

Median - Ignore blanks
 

According to the help files for the MEDIAN function, it already does this.

"If an array or reference argument contains text, logical values,
or empty cells, those values are ignored; however, cells with
the value zero are included"

Rick


"jhicsupt" wrote in message
...
What is the formula to ignore blanks? In other words, there are some 0,
so I
want to include the 0. However there are some blanks and I want to
exclude
the blanks.

Thanks in advance.

Median - Ignore blanks
 

I am using an IF statement and it is not ignoring the 0. I had to put in the
=1. However I want to include 0. Here's my statement:
=MEDIAN(IF((Oct2!$G$2:$G$1763=$C$2)*(Oct2!$T$2:$T$ 1763=Z59)*(Oct2!$V$2:$V$1763=Z60)*(Oct2!$AU$2:$AU$ 1763=1),Oct2!$AU$2:$AU$1763))

"Rick Rothstein (MVP - VB)" wrote:

According to the help files for the MEDIAN function, it already does this.

"If an array or reference argument contains text, logical values,
or empty cells, those values are ignored; however, cells with
the value zero are included"

Rick


"jhicsupt" wrote in message
...
What is the formula to ignore blanks? In other words, there are some 0,
so I
want to include the 0. However there are some blanks and I want to
exclude
the blanks.

Thanks in advance.

Median - Ignore blanks
 

How about instead of this portion:
*(Oct2!$AU$2:$AU$1763=1)

you try:
*(Oct2!$AU$2:$AU$1763<"")




jhicsupt wrote:

I am using an IF statement and it is not ignoring the 0. I had to put in the
=1. However I want to include 0. Here's my statement:
=MEDIAN(IF((Oct2!$G$2:$G$1763=$C$2)*(Oct2!$T$2:$T$ 1763=Z59)*(Oct2!$V$2:$V$1763=Z60)*(Oct2!$AU$2:$AU$ 1763=1),Oct2!$AU$2:$AU$1763))

"Rick Rothstein (MVP - VB)" wrote:

According to the help files for the MEDIAN function, it already does this.

"If an array or reference argument contains text, logical values,
or empty cells, those values are ignored; however, cells with
the value zero are included"

Rick


"jhicsupt" wrote in message
...
What is the formula to ignore blanks? In other words, there are some 0,
so I
want to include the 0. However there are some blanks and I want to
exclude
the blanks.

Thanks in advance.



--

Dave Peterson