Calculating the mode of a criteria-based range

[PaladinWhite]

I have two columns of data. I want to calculate the mode of some cells in the
first column - including only those cells whose corresponding cell in the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the criteria-filtered cells?

Thanks!

 

[Gary''s Student]

Use AutoFilter:

Say A1 thru B6 contain:

V CRT
2 20
1 11
2 8
1 16
2 3

Click on B1 and:

Data > Filter > Autofilter > Custom > is greater than 10
this will produce:

V CRT
2 20
1 11
1 16

Copy and paste this to, say H16 and then =MODE(H16:H100) will get you what
you want.

 

[T. Valko]

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[PaladinWhite]

Unfortunately, rows are being added to the sheet all the time - I'd rather
not have to filter, copy & paste every time new ones are added. Isn't there a
way to do it so that I can keep a running mode?

 

[PaladinWhite]

That did exactly what I needed. Thanks a lot!

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Kathy L.]

Hello,
I have found this help useful, however I'm not quite getting this to work as
expected. I can get this to work with the example below, however my file is
using database controls used in other DCOUNT functions. For the criteria,
I'd like to point it to the controls, which are based on a larger set of raw
data. Perhaps it would be easier to reference my spreadsheet if I send to
you? Please let me know if I can email you. Thank you very much.

 

[T. Valko]

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria range
that's used by other D functions the criteria would have to be values only
and not contain any operators. (although you could probably remove any
operators within the formula but that just adds [undue] complexity.

 

[Kathy L.]

Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the IF
statement's Logical Test, if I work with your array function below? I'm
having difficulty translating the array function below to my criteria range
and raw data.

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria range
that's used by other D functions the criteria would have to be values only
and not contain any operators. (although you could probably remove any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP

Hello,
I have found this help useful, however I'm not quite getting this to work
as
expected. I can get this to work with the example below, however my file
is
using database controls used in other DCOUNT functions. For the criteria,
I'd like to point it to the controls, which are based on a larger set of
raw
data. Perhaps it would be easier to reference my spreadsheet if I send to
you? Please let me know if I can email you. Thank you very much.

 

[T. Valko]

How about providing some details?

--
Biff
Microsoft Excel MVP

Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the IF
statement's Logical Test, if I work with your array function below? I'm
having difficulty translating the array function below to my criteria
range
and raw data.

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria range
that's used by other D functions the criteria would have to be values
only
and not contain any operators. (although you could probably remove any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP

Hello,
I have found this help useful, however I'm not quite getting this to
work
as
expected. I can get this to work with the example below, however my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if I send
to
you? Please let me know if I can email you. Thank you very much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


message
I have two columns of data. I want to calculate the mode of some
cells
in
the
first column - including only those cells whose corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the criteria-filtered
cells?

Thanks!

 

[Peo Sjoblom]

You can't use a criteria range unless you are using D functions or Advanced
filter, describe what your criteria is/are
and maybe somebody will be able to help

--


Regards,


Peo Sjoblom

Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the IF
statement's Logical Test, if I work with your array function below? I'm
having difficulty translating the array function below to my criteria
range
and raw data.

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria range
that's used by other D functions the criteria would have to be values
only
and not contain any operators. (although you could probably remove any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP

Hello,
I have found this help useful, however I'm not quite getting this to
work
as
expected. I can get this to work with the example below, however my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if I send
to
you? Please let me know if I can email you. Thank you very much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


message
I have two columns of data. I want to calculate the mode of some
cells
in
the
first column - including only those cells whose corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the criteria-filtered
cells?

Thanks!

 

[Kathy L.]

I have one tab of raw data (several columns), which include 'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the control
criteria, which include 'Company', 'Business Group' and 'Job Type'. The row
below the criteria header includes a specific company, business group, and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to another cell
with a different formula. The intent is a user will select a company, etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing most often.

Result = 6

How about providing some details?

--
Biff
Microsoft Excel MVP

Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the IF
statement's Logical Test, if I work with your array function below? I'm
having difficulty translating the array function below to my criteria
range
and raw data.

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria range
that's used by other D functions the criteria would have to be values
only
and not contain any operators. (although you could probably remove any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting this to
work
as
expected. I can get this to work with the example below, however my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if I send
to
you? Please let me know if I can email you. Thank you very much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


message
I have two columns of data. I want to calculate the mode of some
cells
in
the
first column - including only those cells whose corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the criteria-filtered
cells?

Thanks!

 

[T. Valko]

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP

I have one tab of raw data (several columns), which include 'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the
control
criteria, which include 'Company', 'Business Group' and 'Job Type'. The
row
below the criteria header includes a specific company, business group, and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to another
cell
with a different formula. The intent is a user will select a company, etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing most
often.

Result = 6

How about providing some details?

--
Biff
Microsoft Excel MVP

Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the IF
statement's Logical Test, if I work with your array function below?
I'm
having difficulty translating the array function below to my criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria
range
that's used by other D functions the criteria would have to be values
only
and not contain any operators. (although you could probably remove any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting this to
work
as
expected. I can get this to work with the example below, however my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger
set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if I
send
to
you? Please let me know if I can email you. Thank you very much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


message
I have two columns of data. I want to calculate the mode of
some
cells
in
the
first column - including only those cells whose corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the criteria-filtered
cells?

Thanks!

 

[Kathy L.]

I think I finally have this working. Thank you VERY much!!!!

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP

I have one tab of raw data (several columns), which include 'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the
control
criteria, which include 'Company', 'Business Group' and 'Job Type'. The
row
below the criteria header includes a specific company, business group, and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to another
cell
with a different formula. The intent is a user will select a company, etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing most
often.

Result = 6

How about providing some details?

--
Biff
Microsoft Excel MVP


Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the IF
statement's Logical Test, if I work with your array function below?
I'm
having difficulty translating the array function below to my criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria
range
that's used by other D functions the criteria would have to be values
only
and not contain any operators. (although you could probably remove any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting this to
work
as
expected. I can get this to work with the example below, however my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger
set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if I
send
to
you? Please let me know if I can email you. Thank you very much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


message
I have two columns of data. I want to calculate the mode of
some
cells
in
the
first column - including only those cells whose corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the criteria-filtered
cells?

Thanks!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

I think I finally have this working. Thank you VERY much!!!!

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP

I have one tab of raw data (several columns), which include 'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the
control
criteria, which include 'Company', 'Business Group' and 'Job Type'.
The
row
below the criteria header includes a specific company, business group,
and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to another
cell
with a different formula. The intent is a user will select a company,
etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing most
often.

Result = 6

:

How about providing some details?

--
Biff
Microsoft Excel MVP


Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the
IF
statement's Logical Test, if I work with your array function below?
I'm
having difficulty translating the array function below to my
criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria
range
that's used by other D functions the criteria would have to be
values
only
and not contain any operators. (although you could probably remove
any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting this
to
work
as
expected. I can get this to work with the example below, however
my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger
set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if
I
send
to
you? Please let me know if I can email you. Thank you very
much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


in
message
I have two columns of data. I want to calculate the mode of
some
cells
in
the
first column - including only those cells whose
corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the
criteria-filtered
cells?

Thanks!

 

[Kathy L.]

I'm afraid I've uncovered another question...in my example below, suppose
Column A (Company) or Column B (Business Group) doesn't have a value? In the
work I'm doing, a user will select a Company and if needed a Business Group.
If a Company isn't selected, for example, I would want it to display the Mode
for the selected Job Type for all Companies & Business Groups. Is this
possible? Thank you.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

I think I finally have this working. Thank you VERY much!!!!

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP


I have one tab of raw data (several columns), which include 'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the
control
criteria, which include 'Company', 'Business Group' and 'Job Type'.
The
row
below the criteria header includes a specific company, business group,
and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to another
cell
with a different formula. The intent is a user will select a company,
etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing most
often.

Result = 6

:

How about providing some details?

--
Biff
Microsoft Excel MVP


Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the
IF
statement's Logical Test, if I work with your array function below?
I'm
having difficulty translating the array function below to my
criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria
range
that's used by other D functions the criteria would have to be
values
only
and not contain any operators. (although you could probably remove
any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting this
to
work
as
expected. I can get this to work with the example below, however
my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger
set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if
I
send
to
you? Please let me know if I can email you. Thank you very
much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


in
message
I have two columns of data. I want to calculate the mode of
some
cells
in
the
first column - including only those cells whose
corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the
criteria-filtered
cells?

Thanks!

 

[Kathy L.]

In addition (the work I'm trying to do is getting quite complex), I found
your solution under the subject "Second Most Common Value" very useful using
the following formulas:

MODE(F2:F8)
MODE(IF(COUNTIF(F$11:F11,F$2:F$8)=0,F$2:F$8+{0,0}))
MODE(IF(COUNTIF(F$11:F12,F$2:F$8)=0,F$2:F$8+{0,0}))

If I wanted to get the 2nd, 3rd, etc. most common value using the solutions
you provided below with the criterias, would this be possible? How would I
nest the formulas? Thank you very much.

I'm afraid I've uncovered another question...in my example below, suppose
Column A (Company) or Column B (Business Group) doesn't have a value? In the
work I'm doing, a user will select a Company and if needed a Business Group.
If a Company isn't selected, for example, I would want it to display the Mode
for the selected Job Type for all Companies & Business Groups. Is this
possible? Thank you.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

I think I finally have this working. Thank you VERY much!!!!

:

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP


I have one tab of raw data (several columns), which include 'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the
control
criteria, which include 'Company', 'Business Group' and 'Job Type'.
The
row
below the criteria header includes a specific company, business group,
and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to another
cell
with a different formula. The intent is a user will select a company,
etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing most
often.

Result = 6

:

How about providing some details?

--
Biff
Microsoft Excel MVP


Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in the
IF
statement's Logical Test, if I work with your array function below?
I'm
having difficulty translating the array function below to my
criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the criteria
range
that's used by other D functions the criteria would have to be
values
only
and not contain any operators. (although you could probably remove
any
operators within the formula but that just adds [undue] complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting this
to
work
as
expected. I can get this to work with the example below, however
my
file
is
using database controls used in other DCOUNT functions. For the
criteria,
I'd like to point it to the controls, which are based on a larger
set
of
raw
data. Perhaps it would be easier to reference my spreadsheet if
I
send
to
you? Please let me know if I can email you. Thank you very
much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


in
message
I have two columns of data. I want to calculate the mode of
some
cells
in
the
first column - including only those cells whose
corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the
criteria-filtered
cells?

Thanks!

 

[T. Valko]

I'm not sure I understand. See if this is what you had in mind.

Criteria for the mode:

A1 = company name or EMPTY
B1 = business group
C1 = job type

Comp = named range for the companies
BG = named range for the business groups
Type = named range for the job types
X = named range for the numeric values where we get the mode

Array entered** :

=MODE(IF(IF(A1<>"",(Comp=A1)*(BG=B1)*(Type=C1),(BG=B1)*(Type=C1)),X))

What it means: if A1 is EMPTY (no company selected) returns the mode for
only the business group and the job type. If A1 is not EMPTY (a company has
been selected) returns the mode for the company, business group and job
type.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

I'm afraid I've uncovered another question...in my example below, suppose
Column A (Company) or Column B (Business Group) doesn't have a value? In
the
work I'm doing, a user will select a Company and if needed a Business
Group.
If a Company isn't selected, for example, I would want it to display the
Mode
for the selected Job Type for all Companies & Business Groups. Is this
possible? Thank you.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

I think I finally have this working. Thank you VERY much!!!!

:

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all
the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP


I have one tab of raw data (several columns), which include
'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the
control
criteria, which include 'Company', 'Business Group' and 'Job Type'.
The
row
below the criteria header includes a specific company, business
group,
and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to
another
cell
with a different formula. The intent is a user will select a
company,
etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing
most
often.

Result = 6

:

How about providing some details?

--
Biff
Microsoft Excel MVP


Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in
the
IF
statement's Logical Test, if I work with your array function
below?
I'm
having difficulty translating the array function below to my
criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the
criteria
range
that's used by other D functions the criteria would have to be
values
only
and not contain any operators. (although you could probably
remove
any
operators within the formula but that just adds [undue]
complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting
this
to
work
as
expected. I can get this to work with the example below,
however
my
file
is
using database controls used in other DCOUNT functions. For
the
criteria,
I'd like to point it to the controls, which are based on a
larger
set
of
raw
data. Perhaps it would be easier to reference my spreadsheet
if
I
send
to
you? Please let me know if I can email you. Thank you very
much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


in
message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"PaladinWhite" <[email protected]>
wrote
in
message
I have two columns of data. I want to calculate the mode
of
some
cells
in
the
first column - including only those cells whose
corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the
criteria-filtered
cells?

Thanks!

 

[T. Valko]

the work I'm trying to do is getting quite complex

If I wanted to get the 2nd, 3rd, etc. most common value with
the criterias, would this be possible?

Let me see what I can come up with. Probably won't get to it until Friday
PM.

--
Biff
Microsoft Excel MVP

In addition (the work I'm trying to do is getting quite complex), I found
your solution under the subject "Second Most Common Value" very useful
using
the following formulas:

MODE(F2:F8)
MODE(IF(COUNTIF(F$11:F11,F$2:F$8)=0,F$2:F$8+{0,0}))
MODE(IF(COUNTIF(F$11:F12,F$2:F$8)=0,F$2:F$8+{0,0}))

If I wanted to get the 2nd, 3rd, etc. most common value using the
solutions
you provided below with the criterias, would this be possible? How would
I
nest the formulas? Thank you very much.

I'm afraid I've uncovered another question...in my example below, suppose
Column A (Company) or Column B (Business Group) doesn't have a value? In
the
work I'm doing, a user will select a Company and if needed a Business
Group.
If a Company isn't selected, for example, I would want it to display the
Mode
for the selected Job Type for all Companies & Business Groups. Is this
possible? Thank you.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


I think I finally have this working. Thank you VERY much!!!!

:

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all
the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP


I have one tab of raw data (several columns), which include
'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is
the
control
criteria, which include 'Company', 'Business Group' and 'Job
Type'.
The
row
below the criteria header includes a specific company, business
group,
and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail
7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to
another
cell
with a different formula. The intent is a user will select a
company,
etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing
most
often.

Result = 6

:

How about providing some details?

--
Biff
Microsoft Excel MVP


Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range
in the
IF
statement's Logical Test, if I work with your array function
below?
I'm
having difficulty translating the array function below to my
criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the
criteria
range
that's used by other D functions the criteria would have to be
values
only
and not contain any operators. (although you could probably
remove
any
operators within the formula but that just adds [undue]
complexity.

--
Biff
Microsoft Excel MVP


message
Hello,
I have found this help useful, however I'm not quite getting
this
to
work
as
expected. I can get this to work with the example below,
however
my
file
is
using database controls used in other DCOUNT functions. For
the
criteria,
I'd like to point it to the controls, which are based on a
larger
set
of
raw
data. Perhaps it would be easier to reference my
spreadsheet if
I
send
to
you? Please let me know if I can email you. Thank you very
much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"PaladinWhite" <[email protected]>
wrote in
message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"PaladinWhite" <[email protected]>
wrote
in
message
I have two columns of data. I want to calculate the
mode of
some
cells
in
the
first column - including only those cells whose
corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the
criteria-filtered
cells?

Thanks!

 

[Kathy L.]

I'm sorry I'm just now getting a chance to reply to your posts. I think I
have found an alternate solution for what I'm trying to solve, since the
functions are getting pretty complicated. Thank you so much for your help,
and I will post again if I'm still stuck and need assistance with this
function again.

I'm not sure I understand. See if this is what you had in mind.

Criteria for the mode:

A1 = company name or EMPTY
B1 = business group
C1 = job type

Comp = named range for the companies
BG = named range for the business groups
Type = named range for the job types
X = named range for the numeric values where we get the mode

Array entered** :

=MODE(IF(IF(A1<>"",(Comp=A1)*(BG=B1)*(Type=C1),(BG=B1)*(Type=C1)),X))

What it means: if A1 is EMPTY (no company selected) returns the mode for
only the business group and the job type. If A1 is not EMPTY (a company has
been selected) returns the mode for the company, business group and job
type.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

I'm afraid I've uncovered another question...in my example below, suppose
Column A (Company) or Column B (Business Group) doesn't have a value? In
the
work I'm doing, a user will select a Company and if needed a Business
Group.
If a Company isn't selected, for example, I would want it to display the
Mode
for the selected Job Type for all Companies & Business Groups. Is this
possible? Thank you.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


I think I finally have this working. Thank you VERY much!!!!

:

Try something like this (array entered):

=MODE(IF(('Raw Data'!A1:A10=Criteria!A2)*('Raw
Data'!B1:B10=Criteria!B2)*('Raw Data'!C1:C10=Criteria!C2),'Raw
Data'!D110))

Note that if there is no mode (at least 2 instances) that meets all
the
criteria the formula returns an error.

--
Biff
Microsoft Excel MVP


I have one tab of raw data (several columns), which include
'Company',
'Business Group', 'Job Type' and 'Source Code'. Another tab is the
control
criteria, which include 'Company', 'Business Group' and 'Job Type'.
The
row
below the criteria header includes a specific company, business
group,
and
job type that match values in the raw data. Here is a sampling:

Raw data:
A B C
D
1 Company Business Group Job Type Source Type Code
2 AFFILIATES CARD BUSINESS Marketing 12
3 CONSUMER SALES Retail 7
4 AFFILIATES CARD BUSINESS Marketing 7
5 FACILITIES FACILITIES Technician 5
6 AFFILIATES CARD BUSINESS Marketing 6
7 AFFILIATES CARD BUSINESS Marketing 6

Criteria page:
A B C
1 Company Business Group Job Type
2 AFFILIATES CARD BUSINESS Marketing

Note: The values for Company and Bus. Group criteria point to
another
cell
with a different formula. The intent is a user will select a
company,
etc.
and the data will populate for that company.

Based on the criteria, I'd like to identify the source appearing
most
often.

Result = 6

:

How about providing some details?

--
Biff
Microsoft Excel MVP


Thank you. I wish there were more Dxxx functions!
My problem (I think) is how do I reference the criteria range in
the
IF
statement's Logical Test, if I work with your array function
below?
I'm
having difficulty translating the array function below to my
criteria
range
and raw data.

:

There is no "DMODE" function.

If you want to use the MODE() function and reference the
criteria
range
that's used by other D functions the criteria would have to be
values
only
and not contain any operators. (although you could probably
remove
any
operators within the formula but that just adds [undue]
complexity.

--
Biff
Microsoft Excel MVP


Hello,
I have found this help useful, however I'm not quite getting
this
to
work
as
expected. I can get this to work with the example below,
however
my
file
is
using database controls used in other DCOUNT functions. For
the
criteria,
I'd like to point it to the controls, which are based on a
larger
set
of
raw
data. Perhaps it would be easier to reference my spreadsheet
if
I
send
to
you? Please let me know if I can email you. Thank you very
much.

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


in
message
That did exactly what I needed. Thanks a lot!

:

Try this array formula** :

=MODE(IF(B1:B5>10,A1:A5))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"PaladinWhite" <[email protected]>
wrote
in
message
I have two columns of data. I want to calculate the mode
of
some
cells
in
the
first column - including only those cells whose
corresponding
cell
in
the
second column has a value greater than 10.

For example:
2 | 20
1 | 11
2 | 8
1 | 16
2 |3
Would return MODE(2,1,1) = 1.

How do I pass the MODE() function only the
criteria-filtered
cells?

Thanks!