If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to Count and Return Most common Value in a Dynamic Named Range
Hi Frank, Assistance and explanation much appreciated. The Formulae you suggested are working well. Thanks Tinä Frank Kabel Wrote: 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ä ------------------------------------------------------------------------ Tinä's Profile: http://www.excelforum.com/member.php...o&userid=15410 View this thread: http://www.excelforum.com/showthread...hreadid=271798 |
#2
|
|||
|
|||
Hi Tina
thanks for your feedback -- Regards Frank Kabel Frankfurt, Germany "Tinä" schrieb im Newsbeitrag ... Hi Frank, Assistance and explanation much appreciated. The Formulae you suggested are working well. Thanks Tinä Frank Kabel Wrote: 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ä --------------------------------------------------------------------- --- Tinä's Profile: http://www.excelforum.com/member.php...o&userid=15410 View this thread: http://www.excelforum.com/showthread...hreadid=271798 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 04:44 PM |
Conditional count in range w/#N?A's | 0013 | Worksheet Functions | 4 | July 12th, 2004 10:32 PM |
Formula Probs: Finding a mean & percent return of IF formulas | hillryane | Worksheet Functions | 0 | January 28th, 2004 06:42 PM |
Question about an argument, in an OFFSET dynamic range formula | Terry B. | Worksheet Functions | 6 | December 10th, 2003 10:53 PM |