Like MODE but not quite.

[Markusdragon]

Hi, I'm sorry if this question seems a little newbie-ish, only I haven't
used MS excel since GCSE, and that was over 3 years ago. Could someone
tell me how to take the values in a list, and then display the most
common one -if the values aren't numbers-, only I've tried looking for
it and endlessly had to sift through instructions on how to use MODE!

 

[RagDyer]

This will work for numbers *and* text, and also count empty cells as "0".

=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))

So, if you have more empty cells then any other value, you'll get a return
of "0".

 

[Harlan Grove]

RagDyer wrote...

This will work for numbers *and* text, and also count empty cells as "0".

=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))

So, if you have more empty cells then any other value, you'll get a return
of "0".

Not quite.

Your formula treats blank cells the same as cells evaluating to "". If
those cells are most common, and the first of them evaluated to "",
then your formula would also evaluate to "". If the first of them were
blank, then your formula would evaluate to 0 because the INDEX call
would return a reference to that cell, and blank cells are evaluated as
0 in nontext contexts.

If blank cells should be ignored, use the array formula

=INDEX(rng,MODE(MATCH(IF(ISBLANK(rng),ROW(rng),rng),
IF(ISBLANK(rng),ROW(rng),rng),0)))

if the range of interest, rng, is a single column, multiple row range.
Replace the ROW calls with COLUMN calls if rng is single row, multiple
column.

 

[RagDyer]

Appreciate the input Harlan ... BUT ... as is often discussed here, an empty
cell is *empty*, which means it cannot contain a formula that *might*
evaluate to a zero length string.

I *did* stipulate "empty" in my instructions (explanation).

I failed to mention 'nulls' ( "" or zero length strings) primarily because I
didn't think to test for them.

So, thanks to you, the OP should now be aware that a return of zero means
*empty* cells are prevalent, *or* a blank cell return would mean that he has
"" cells within his range of interest.

To perhaps *simplify* the entire situation (on my part), let's use this
formula instead:

=INDEX(A1:A20&"",MODE(MATCH(A1:A20&"",A1:A20&"",0)))

And then let's just say that a *blank cell* return signifies that *blank*
cells, which include empty as well as "" cells, are prevalent in the range
of interest!

 

[Harlan Grove]

Appreciate the input Harlan ... BUT ... as is often discussed here, an empty
cell is *empty*, which means it cannot contain a formula that *might*
evaluate to a zero length string.

....

Blank is proper terminology. 'Empty' is ambiguous. There's an ISBLANK
worksheet function. There's no ISEMPTY worksheet function. Get your
terminology right.

Also, test. With A1:A20 blank, what does your original formula,

=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))

return? Now enter the formula ="" in cell A1. Does your original
formula now return "" ?

But to really make sure, change your formula to

=ISBLANK(INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0))))

and clear A1:A10 and enter 0 in A11:A20. Does this formula return TRUE?

What you're failing to understand, whether you've read it or not
before, is that when Excel expects to use values, it EVALUATES blank
cells as 0 in numeric and AMBIGUOUS contexts and as "" in text
contexts. First argument to MATCH is an ambiguous context. The second
argument to MATCH isn't usually evaluated. That why with A1:A2 blank,
the formula

=MATCH(A1,A1:A2,0)

returns #N/A, but enter 0 in A2 and it returns 2. The INDEX calls above
return a cell reference. When the referenced cell is blank, it's not
immediately evaluated. That's why the ISBLANK(INDEX(...)) formula above
returns TRUE (it's blank) rather than FALSE (it's 0 or ""). However,
the =INDEX(...) formula forces Excel to evaluate the blank cell, and
since it's not a text context, Excel evaluates it as 0.

I *did* stipulate "empty" in my instructions (explanation).

Yes, but you misunderstood what your formula does. Thus your erroneous
comment, 'and also count empty cells as "0".' For example, enter the
following formula in C1.

=INDEX(A1:A5,MODE(MATCH(A1:A5&"",A1:A5&"",0)))

Enter ="" in A1, 0 in A2 and A3 and leave A4 and A5 blank. If blank
cells match cells evaluating to 0, wouldn't the most frequent value be
0? Does this formula return 0?

I failed to mention 'nulls' ( "" or zero length strings) primarily because I
didn't think to test for them.

OK, but that's what blank cells match in your formula.

So, thanks to you, the OP should now be aware that a return of zero means
*empty* cells are prevalent, *or* a blank cell return would mean that he has
"" cells within his range of interest.

Wrong again because you still don't understand what's going on no
matter how pedantic you're trying to sound.

The problem with your formula is that when it returns 0 it's ambiguous
because it could mean either that 0 is the most frequently occurring
value OR that there are more blank cells or cells evaluating to "" than
any other value and the first of those cells is blank. A return value
of "" indicates that cells evaluating to "" and blank cells TOGETHER
are most frequently occurring AND that the first of these cells
evaluates to "".

To perhaps *simplify* the entire situation (on my part), let's use this
formula instead:

=INDEX(A1:A20&"",MODE(MATCH(A1:A20&"",A1:A20&"",0)))

Still ambiguous, though a different ambiguity. Now there'd be no way to
distinguish between cells evaluating to numeric 0 or text "0".

And then let's just say that a *blank cell* return signifies that *blank*
cells, which include empty as well as "" cells, are prevalent in the range
of interest!

....

To repeat, get your terminology right. BLANK means no cell contents, as
in the ISBLANK worksheet function. Empty is a term without specific
meaning, but could be used to mean "", though zero-length string is
more precise.

Lest you believe COUNTBLANK rather than ISBLANK governs the
terminology, from online help for COUNTBLANK: "Counts empty cells in a
specified range of cells." So it appears 'empty' may be defined as
cells evaluating to "" or blank. So you've got the terms 'empty' and
'blank' exactly backwards. Please attempt to learn the correct usage.

 

[RagDyeR]

Harlan, there's no way in hell that I can or wish to enter into any sort of
debate with you concerning the finer, or less then finer points of XL.


However, just because Redmond chose not to create an ISEMPTY function,
doesn't change the logical meaning of the term "empty cell".

I would guess that 99% of the folks around here would agree that an "empty"
cell contains *nothing*, while a "blank" cell *might* contain 'zero length
strings'.

No matter how "right" you may be, it doesn't change the general consensus of
the meaning.

I do bow to your superior knowledge of the program and also to your
unremitting persistence in pursuing a topic to a conclusion favorable to
your opinions.

For that reason, I state here that "empty" means *containing nothing*, and I
leave this thread, and acquiesce the last word to you.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Appreciate the input Harlan ... BUT ... as is often discussed here, an empty
cell is *empty*, which means it cannot contain a formula that *might*
evaluate to a zero length string.

....

Blank is proper terminology. 'Empty' is ambiguous. There's an ISBLANK
worksheet function. There's no ISEMPTY worksheet function. Get your
terminology right.

Also, test. With A1:A20 blank, what does your original formula,

=INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0)))

return? Now enter the formula ="" in cell A1. Does your original
formula now return "" ?

But to really make sure, change your formula to

=ISBLANK(INDEX(A1:A20,MODE(MATCH(A1:A20&"",A1:A20&"",0))))

and clear A1:A10 and enter 0 in A11:A20. Does this formula return TRUE?

What you're failing to understand, whether you've read it or not
before, is that when Excel expects to use values, it EVALUATES blank
cells as 0 in numeric and AMBIGUOUS contexts and as "" in text
contexts. First argument to MATCH is an ambiguous context. The second
argument to MATCH isn't usually evaluated. That why with A1:A2 blank,
the formula

=MATCH(A1,A1:A2,0)

returns #N/A, but enter 0 in A2 and it returns 2. The INDEX calls above
return a cell reference. When the referenced cell is blank, it's not
immediately evaluated. That's why the ISBLANK(INDEX(...)) formula above
returns TRUE (it's blank) rather than FALSE (it's 0 or ""). However,
the =INDEX(...) formula forces Excel to evaluate the blank cell, and
since it's not a text context, Excel evaluates it as 0.

I *did* stipulate "empty" in my instructions (explanation).

Yes, but you misunderstood what your formula does. Thus your erroneous
comment, 'and also count empty cells as "0".' For example, enter the
following formula in C1.

=INDEX(A1:A5,MODE(MATCH(A1:A5&"",A1:A5&"",0)))

Enter ="" in A1, 0 in A2 and A3 and leave A4 and A5 blank. If blank
cells match cells evaluating to 0, wouldn't the most frequent value be
0? Does this formula return 0?

I failed to mention 'nulls' ( "" or zero length strings) primarily because I
didn't think to test for them.

OK, but that's what blank cells match in your formula.

So, thanks to you, the OP should now be aware that a return of zero means
*empty* cells are prevalent, *or* a blank cell return would mean that he has
"" cells within his range of interest.

Wrong again because you still don't understand what's going on no
matter how pedantic you're trying to sound.

The problem with your formula is that when it returns 0 it's ambiguous
because it could mean either that 0 is the most frequently occurring
value OR that there are more blank cells or cells evaluating to "" than
any other value and the first of those cells is blank. A return value
of "" indicates that cells evaluating to "" and blank cells TOGETHER
are most frequently occurring AND that the first of these cells
evaluates to "".

To perhaps *simplify* the entire situation (on my part), let's use this
formula instead:

=INDEX(A1:A20&"",MODE(MATCH(A1:A20&"",A1:A20&"",0)))

Still ambiguous, though a different ambiguity. Now there'd be no way to
distinguish between cells evaluating to numeric 0 or text "0".

And then let's just say that a *blank cell* return signifies that *blank*
cells, which include empty as well as "" cells, are prevalent in the range
of interest!

....

To repeat, get your terminology right. BLANK means no cell contents, as
in the ISBLANK worksheet function. Empty is a term without specific
meaning, but could be used to mean "", though zero-length string is
more precise.

Lest you believe COUNTBLANK rather than ISBLANK governs the
terminology, from online help for COUNTBLANK: "Counts empty cells in a
specified range of cells." So it appears 'empty' may be defined as
cells evaluating to "" or blank. So you've got the terms 'empty' and
'blank' exactly backwards. Please attempt to learn the correct usage.

 

[Harlan Grove]

RagDyeR wrote...

Harlan, there's no way in hell that I can or wish to enter into any sort of
debate with you concerning the finer, or less then finer points of XL.

However, just because Redmond chose not to create an ISEMPTY function,
doesn't change the logical meaning of the term "empty cell".

Excel's design, function names and terminology isn't subject to
majority rule. BLANK means no cell contents, empty means blank or "".
If you can't remember that, oh well.

I would guess that 99% of the folks around here would agree that an "empty"
cell contains *nothing*, while a "blank" cell *might* contain 'zero length
strings'.

So 99% would be wrong. It happens with technical material like this.
Very few people are able to handle precise terminology.

No matter how "right" you may be, it doesn't change the general consensus of
the meaning.

So we can all make up our own terminology as long as we can get enough
other people to use it?

I do bow to your superior knowledge of the program and also to your
unremitting persistence in pursuing a topic to a conclusion favorable to
your opinions.

Nothing to do with opinion. On your claimed understanding of the
semantics of your original formula, you were demonstrably, objectively,
factually WRONG.

For that reason, I state here that "empty" means *containing nothing*, and I

....

To you. Contrary to Excel's documentation, but what do you care about
documentation?

 

[Leo Heuser]

Excel's design, function names and terminology isn't subject to
majority rule. BLANK means no cell contents, empty means blank or "".
If you can't remember that, oh well.

Redmond is to blame for making this ambiguity.

If BLANK always meant no cell contents, these two formulae should
always return the same answer

=COUNTBLANK(A1:A10)

=SUMPRODUCT(ISBLANK(A1:A10)+0)

but they don't.

If e.g. A1 contains "" (empty text), the first formula will return 10,
the second will return 9.

So in the first formula BLANK means empty and "", in the second
formula BLANK means empty.

Microsoft, Denmark, has seen the light. Instead of translating the
function ISBLANK() to ER.BLANK() ("blank" is also a Danish word)
it is translated to ER.TOM(), ER meaning IS and TOM meaning EMPTY.
COUNTBLANK() is translated to ANTAL.BLANKE() meaning
Number of blanks.

So, in Danish Excel, BLANK means empty or "" (empty text) and EMPTY
means no cell contents.


Leo Heuser

 

[Harlan Grove]

Leo Heuser wrote...

Redmond is to blame for making this ambiguity.
Agreed.

If BLANK always meant no cell contents, these two formulae should
always return the same answer

=COUNTBLANK(A1:A10)

=SUMPRODUCT(ISBLANK(A1:A10)+0)

but they don't.

....

Agreed. Anyone who expects terminological or referential consistency
from Microsoft or in Microsoft software deserves the headaches they
will ineluctably endure.

However, the question is which governs. Blank is in ISBLANK has been
part of Excel for a lot longer than COUNTBLANK.

Microsoft, Denmark, has seen the light. Instead of translating the
function ISBLANK() to ER.BLANK() ("blank" is also a Danish word)
it is translated to ER.TOM(), ER meaning IS and TOM meaning EMPTY.
COUNTBLANK() is translated to ANTAL.BLANKE() meaning
Number of blanks.

....

Which may be relevant in Danish newsgroups, but not in English
newsgroups. FWIW, this also seems to be the case in all other European
languages except Spanish. But that doesn't make it relevant in English
language newsgroups.

I have less concern about using empty or null in addition to blank to
mean cells with no contents. What I react to is misusing 'blank' to
mean something different. Like it or not, its precise meaning in
English language versions is no cell contents. It's misused in the name
of the COUNTBLANK function, but it's used more or less consistently in
English versions of online help, so it could be confusing to use it to
mean something else in English language discussions of Excel.