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(range)-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ä said:
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(NAMEID)-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
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ä
---------------------------------------------------------------------
---------------------------------------------------------------------
