Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
|
|||
|
|||
Answer: Median - Ignore blanks
To calculate the median and ignore blanks in Excel, use the following formula:
Formula:
To use this formula, follow these steps:
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATA Validation (Ignore Blanks) | Excel Discussion (Misc queries) | |||
Median calculation and ignore zeros | Excel Discussion (Misc queries) | |||
Data Validation - ignore blanks | Excel Discussion (Misc queries) | |||
Ignore Blanks in Data Validation | Excel Worksheet Functions | |||
USING IGNORE BLANKS IN FORMULA | Excel Discussion (Misc queries) |