Formula to Count and Return Most common Value in a Dynamic Named Range

Hi Frank,

Thanks for input. I've used this Formula referencing the cells with
the A1-Style and works ok.

Can you explain the use of =1 in this Formula:
=MODE(IF(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$9)-ROW($A$2),0))=1,B2:B9))

I'VE TRIED TO AMEND THE FORMULA SLIGHTLY TO USE THE DYNAMIC NAMED RANGE
WITHIN THE FORMULA - CAN YOU HELP:
=MODE(IF(SUBTOTAL(3,OFFSET(INDEX(NAMEID,1,1),ROW(N AMEID)-MIN(ROW(NAMEID),0),1),RESULTS),,))???

WHEN I USE THIS REPLACED WITH MY A1-STYLE REFERENCES - I GET ZERO
For counting the number of values lets assume you have entered the
above formula in C1. Then use:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$2:$A$9,ROW($A$2:$A$9)-ROW($A$2),0))=1),--($B$2:$B$9=C1))

*I do have text values * = your column "A" and my Dynamic Named Range
NAMEID which contains the filtered elements.

Further help appreciated.

Thanks
Tinä

Frank Kabel Wrote:

Hi
for numeric values use something like the following array formula
(entered with CTRL+SHIFT+ENTER):
=MODE(IF(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$9)-ROW($A$2),0))=1,B2:B9))

B1:B9 is the range of interest (containing your numeric values),
column
A contains the filtered elements.

For counting the number of values lets assume you have entered the
above formula in C1. Then use:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$2:$A$9,ROW($A$2:$A$9)-ROW($A$2),0))
=1),--($B$2:$B$9=C1))

If you need this for text values (or mixed values) please post back

--
Regards
Frank Kabel
Frankfurt, Germany

"Tinä" schrieb im Newsbeitrag
...

Hi,

I'm looking for a Formula to return the *most frequent * value in a
Dynamic Named Range that will be filtered. I therefore, need the
Formula to include *only* the Visible cell values of the Dynamic
Named
Range called RESULTS.

The Formula needs to :

1) Return the MOST frequent value in (Filtered Visible Cells) Range
;
2) Return the Number of Times this value appears in (Filtered
Visible
Cells) Range.

The Dynamic Named Range details for RESULTS in the Define Name
Refers
To Box:
=OFFSET('SUBJECT'!$Q$9,2,0,COUNTA('SUBJECT'!$Q:$Q) ,1)

Thanks
Tinä

--
Tinä

---------------------------------------------------------------------
---



--
Tinä
------------------------------------------------------------------------
Tinä's Profile: http://www.excelforum.com/member.php...o&userid=15410
View this thread: http://www.excelforum.com/showthread...hreadid=271798

Hi
(1) The '1' indicates that the result of the subtotal function should
be one. As SUBTOTAL is only used with one single cell at a time the
result is either '0' (filtered') or '1' (#visible').

(2)a For using a defined name try the array formula:
=MODE(IF(SUBTOTAL(3,OFFSET(INDEX(range,1,1),ROW(ra nge)-ROW(INDEX(range,
1,1)),0))=1,range+{0,0}))

Note: the +{0,0} part ensures that no error message is shown then your
most common value exists only once.

(2)b: Using mixed data (text + numeric): Now this is more complicated
:-)

I. For getting the most common entry without checking for filtered
values try the array formula:
=INDEX($B$2:$B$9,MODE(MATCH($B$2:$B$9,$B$2:$B$9,0) ))
Note: Returns #NA then the range contains blank cells


And now for filtered row + range name:
=INDEX(range,MODE(IF(ISNA(MATCH(Filter,$B$2:$B$9,0 )),"",MATCH(Filter,ra
nge,0))))

there range is your referred range and Filter is the defined name for
the following formula:
=IF(SUBTOTAL(3,OFFSET(INDEX(range,1,1),ROW(range)-ROW(INDEX(range,1,1))
,0))=1,range+{0.0})




--
Regards
Frank Kabel
Frankfurt, Germany

"Tinä" schrieb im Newsbeitrag
...

Hi Frank,

Thanks for input. I've used this Formula referencing the cells with
the A1-Style and works ok.

Can you explain the use of =1 in this Formula:

=MODE(IF(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$9)-ROW($A$2),0))=1,B2:B9))

I'VE TRIED TO AMEND THE FORMULA SLIGHTLY TO USE THE DYNAMIC NAMED
RANGE
WITHIN THE FORMULA - CAN YOU HELP:

=MODE(IF(SUBTOTAL(3,OFFSET(INDEX(NAMEID,1,1),ROW(N AMEID)-MIN(ROW(NAMEID
),0),1),RESULTS),,))???

WHEN I USE THIS REPLACED WITH MY A1-STYLE REFERENCES - I GET ZERO
For counting the number of values lets assume you have entered the
above formula in C1. Then use:

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$2:$A$9,ROW($A$2:$A$9)-ROW($A$2),0))
=1),--($B$2:$B$9=C1))

*I do have text values * = your column "A" and my Dynamic Named
Range
NAMEID which contains the filtered elements.

Further help appreciated.

Thanks
Tinä

Frank Kabel Wrote:

Hi
for numeric values use something like the following array formula
(entered with CTRL+SHIFT+ENTER):

=MODE(IF(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$9)-ROW($A$2),0))=1,B2:B9))

B1:B9 is the range of interest (containing your numeric values),
column
A contains the filtered elements.

For counting the number of values lets assume you have entered the
above formula in C1. Then use:

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$2:$A$9,ROW($A$2:$A$9)-ROW($A$2),0))
=1),--($B$2:$B$9=C1))

If you need this for text values (or mixed values) please post back

--
Regards
Frank Kabel
Frankfurt, Germany

"Tinä" schrieb im Newsbeitrag
...

Hi,

I'm looking for a Formula to return the *most frequent * value in
a
Dynamic Named Range that will be filtered. I therefore, need the
Formula to include *only* the Visible cell values of the Dynamic
Named
Range called RESULTS.

The Formula needs to :

1) Return the MOST frequent value in (Filtered Visible Cells)
Range
;
2) Return the Number of Times this value appears in (Filtered
Visible
Cells) Range.

The Dynamic Named Range details for RESULTS in the Define Name
Refers
To Box:
=OFFSET('SUBJECT'!$Q$9,2,0,COUNTA('SUBJECT'!$Q:$Q) ,1)

Thanks
Tinä

--
Tinä


---------------------------------------------------------------------
---



--
Tinä
---------------------------------------------------------------------
---
Tinä's Profile:
http://www.excelforum.com/member.php...o&userid=15410
View this thread:
http://www.excelforum.com/showthread...hreadid=271798