Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
Hi all
I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple" I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column. Regards to all Reppy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Wed, 20 Jun 2012 19:59:15 -0700 (PDT), Reppy wrote:
Hi all I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple" I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column. Regards to all Reppy If Rng is the range containing your strings, then: This formula must be **array-entered**: =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Thursday, June 21, 2012 11:27:31 AM UTC+8, Ron Rosenfeld wrote:
On Wed, 20 Jun 2012 19:59:15 -0700 (PDT), Reppy wrote: Hi all I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple" I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column. Regards to all Reppy If Rng is the range containing your strings, then: This formula must be **array-entered**: =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. Hello Ron I must be missing something as when i follow your formula i get the message of too few arguments. In your formula you use 'rng' twice in brackets. I actually only have one column to apply formula to. e.g. P23:p300 where all responses are from a drop down list. There are five choices and i m looking for the most frequent response. Sorry to be a bother but can you tell me what i am missing? Cheers Reppy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
H
On Thursday, June 21, 2012 12:12:34 PM UTC+8, Reppy wrote: On Thursday, June 21, 2012 11:27:31 AM UTC+8, Ron Rosenfeld wrote: On Wed, 20 Jun 2012 19:59:15 -0700 (PDT), Reppy wrote: Hi all I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple" I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column. Regards to all Reppy If Rng is the range containing your strings, then: This formula must be **array-entered**: =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. Hello Ron I must be missing something as when i follow your formula i get the message of too few arguments. In your formula you use 'rng' twice in brackets. I actually only have one column to apply formula to. e.g. P23:p300 where all responses are from a drop down list. There are five choices and i m looking for the most frequent response. Sorry to be a bother but can you tell me what i am missing? Cheers Reppy Hello Ron I have worked it out and it works great. thanks heaps. Reppy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Thu, 21 Jun 2012 02:30:20 -0700 (PDT), Reppy wrote:
Hello Ron I have worked it out and it works great. thanks heaps. Reppy Glad to help. Thanks for the feedback. Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Thursday, June 21, 2012 6:16:12 PM UTC+8, Ron Rosenfeld wrote:
On Thu, 21 Jun 2012 02:30:20 -0700 (PDT), Reppy wrote: Hello Ron I have worked it out and it works great. thanks heaps. Reppy Glad to help. Thanks for the feedback. Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula. Yep Ron - a typo. Sometimes i am sure that the fingers have a mind of there own. It was not till i left and returned later and saw the typo. Cheers and thanks again, it will help me a great deal. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Thursday, June 21, 2012 6:16:12 PM UTC+8, Ron Rosenfeld wrote:
On Thu, 21 Jun 2012 02:30:20 -0700 (PDT), Reppy wrote: Hello Ron I have worked it out and it works great. thanks heaps. Reppy Glad to help. Thanks for the feedback. Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula. Ron I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have the formula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go. Cheers Reppy |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Wednesday, June 20, 2012 9:59:15 PM UTC-5, Reppy wrote:
Hi all I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple" I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column. Regards to all Reppy modify for column instead of row and enter using CSE Ctrl Shift Enter =INDEX(B2:K2,MATCH(COUNTIF(B2:K2,B2:K2), COUNTIF(B2:K2,B2:K2),0)) & " ("&COUNTIF(B2:K2,B2:K2)&" times)" or =INDEX(D2:D2500,MATCH(MAX(COUNTIF(D2:D2500,D2:D250 0)),COUNTIF(D2:D2500,D2:D2500),0)) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Thursday, June 21, 2012 8:27:40 PM UTC+8, Don Guillett wrote:
On Wednesday, June 20, 2012 9:59:15 PM UTC-5, Reppy wrote: Hi all I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple" I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column. Regards to all Reppy modify for column instead of row and enter using CSE Ctrl Shift Enter =INDEX(B2:K2,MATCH(COUNTIF(B2:K2,B2:K2), COUNTIF(B2:K2,B2:K2),0)) & " ("&COUNTIF(B2:K2,B2:K2)&" times)" or =INDEX(D2:D2500,MATCH(MAX(COUNTIF(D2:D2500,D2:D250 0)),COUNTIF(D2:D2500,D2:D2500),0)) Hello Don Thanks heaps that worked great. Yourself and Ron are legends. Thanks guys this has been of immense help!! Regards Reppy |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Thu, 21 Jun 2012 04:59:42 -0700 (PDT), Reppy wrote:
I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have the formula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go. If your entries are the only thing in column A, and if there are no intervening blank cells, you could use a dynamic name (one that adjusts depending on the extent of the data). For example: Define Name Name: Rng Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A)) If your data is not like that, let me know, and we can come up with something else. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Friday, June 22, 2012 7:12:55 AM UTC+8, Ron Rosenfeld wrote:
On Thu, 21 Jun 2012 04:59:42 -0700 (PDT), Reppy wrote: I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have the formula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go. If your entries are the only thing in column A, and if there are no intervening blank cells, you could use a dynamic name (one that adjusts depending on the extent of the data). For example: Define Name Name: Rng Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A)) If your data is not like that, let me know, and we can come up with something else. Hello Ron Just logged on and saw your reply. I think this will work as well. I am going to have a look at it and give it a go tomorrow and see how this varies to the formula suggested by Don. It should work as there are no intervening blank cells. The blanks are just further down the list waiting for more data as i add it. This formula is somewhat shorter. I also was not aware that you could apply offset in this manner. There certainly is so much to learn. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Friday, June 22, 2012 7:12:55 AM UTC+8, Ron Rosenfeld wrote:
On Thu, 21 Jun 2012 04:59:42 -0700 (PDT), Reppy wrote: I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have the formula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go. If your entries are the only thing in column A, and if there are no intervening blank cells, you could use a dynamic name (one that adjusts depending on the extent of the data). For example: Define Name Name: Rng Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A)) If your data is not like that, let me know, and we can come up with something else. Hello Ron Just wanted to let you know that this formula also works well. As it is somewhat shorter than the previous ones i might use this one. It is quite amazing he we arrive at the same answer with varying formulas. Thanks again Reppy |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to count text entries and return text with most entries
On Fri, 22 Jun 2012 19:50:24 -0700 (PDT), Reppy wrote:
Hello Ron Just wanted to let you know that this formula also works well. As it is somewhat shorter than the previous ones i might use this one. It is quite amazing he we arrive at the same answer with varying formulas. Thanks again Reppy You're welcome. Thanks for the feedback. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Multiple Text Entries | Excel Worksheet Functions | |||
Count instances of text in date entries | Excel Worksheet Functions | |||
count text entries | Excel Worksheet Functions | |||
Count single Text in cells with multiple text entries | Excel Discussion (Misc queries) | |||
count cells that contain text entries | Excel Worksheet Functions |