• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Max Count

L

leimst

Member
Hello,


I have a report that has a column with store numbers in it. I need to come up with a formula that will search through the column and return the store that occurs the greatest number of times within that column. A caveat is that I would like to be able to use it on other spreadsheets that have a different number of rows.


Thank you in advance for any help!


Leimst
 
Hi leimst,


I would suggest that you upload a sample spreadsheet, then hopefully someone can help you, take a look at this link


http://chandoo.org/forums/topic/posting-a-sample-workbook
 
I could post the spreadsheet but there's really not that much to it in terms of the data that I am interested in which is just the first column which looks something like:


STORE NUMBER

473

428

452

473

481

428

452

473

481

473

428

In this small scale example, 473 occurs 4 times which is the highest count of any particular Store Number in the list followed by 428 which occurs 3 times, etc.


Does that help?

Thanks, Leimst
 
Leimst

Assuming your data is in A2:A12

=MODE(A2:A12)

Mode returns the most frequently occurring number in a range
 
First of all, thank you Hui for that formula. Have never seen that one before so I'll keep that in my back pocket for later!

However, I neglected to mention that my store numbers are in a text format because sometimes the store numbers have leading zeroes. Also, I went ahead and tried to upload my spreadsheet but the IT dept. at work apparently has every single one of the upload site options blocked! :(.


Anyway, I have the following questions:

1) Is there a way to perform this calculation on text?

2) Can I accomodate varying numbers of rows by utilizing the Offset command?

3) I would like a corresponding cell that gives me to number of occurrences.


Thank you for any help!


Leimst
 
A little elaboration:

[pre]
Code: 
D  A  T  A           D  E  S  I  R  E  D       R  E  S  U  L  T  S

STORE NUMBER        Highest Occurrence         Number of Occurrences
473                 473                        4
428
452
473
481
428
452
473
481
473
428
[/pre]
Edited by Luke M to fix formatting w/ backticks.
 
Hi,


Try this array entered formula - Ctrl,Shift,Enter


=MODE(VALUE(A2:A12))


Then for occurrences


=COUNTIF(A2:A12,C2)
 
1) MODE function for text, credit to:

http://answers.yahoo.com/question/index?qid=20071018131052AA9lFlM


Formula:

=INDEX(A1:A4, MATCH(MAX(COUNTIF($A$1:$A$4, A1:A4)), COUNTIF($A$1:$A$4,A1:A4),0))


2) You could replace all the A1:A4 references with a dynamic named range that automatically adjusts to range size. Perhaps something like:

=OFFSET($A$2,0,0,COUNTA(A:A)-1,1)


3) Once you know the value, a simple COUNTIF will let you know how many occurrences there are.

=COUNTIF(A:A,B2)
 
Luke-that was exactly what I needed...worked perfectly! Thank you so much! I'm not overly familiar with the Offset command but was able to apply it to the Store Number column in conjunction with a named range. Also, I've never used an array formual before and the Ctrl Shift Enter requirement.


Great stuff and thanks again!!!


Leimst
 
The Offset formula in your case tells excel how big the range is, even if numbers are added or deleted from the range. We call this a dynamic range.


Array formulas calculate alot of things all at once. for instance if you had to multiply lots of rows and then add each sum for every 2 rows, an array formula could do that all in one swipe without having to make each formula.
 
Thanks Montrey. Going to have to experiment some more with array formulas to get a better feel for them. I think I now have a pretty good grasp of Offset but not so much with the array formulas. Having been able to implement Luke M's formulas successfully, I did not think it would be difficult to find the second and third, etc. most frequently occurring Store Numbers. Thought it would be as simple as placing a "-1" in the right place but have not been able to make it work.


Thoughts?


Thanks, Leimst
 
More explanation of arrays:

http://chandoo.org/forums/topic/plain-language-definition-of-array-formulas-vs-other-formulas


Finding the Nth largest gets a little tricky if there are ties, but this should get you close:

=INDEX(A1:A4, MATCH(LARGE(COUNTIF($A$1:$A$4, A1:A4),2), COUNTIF($A$1:$A$4,A1:A4),0))


I've bolded the parts that changed. The 2 is what indicated to get the 2nd largest value. The problem with ties is that Formula only looks for 2nd or 3rd largest number, but once it finds it the first time, it will never find the 2nd instance of the same number.
 
Hello,


I have made my first attempt at posting a document, chose Google Docs, and the link is: https://docs.google.com/file/d/0B8AkbgGt13FZTE5hTXlpUFNZZlE/edit. Please let me know if anyone has any trouble accessing.


@Luke - I attempted to use the "=INDEX(A1:A4, MATCH(LARGE(COUNTIF($A$1:$A$4, A1:A4),2), COUNTIF($A$1:$A$4,A1:A4),0))" but couldn't seem to get it to work for me. Any help would be much appreciated.


Thanks,


Leimst
 
Hi Leimst,

I'm afraid I'm unable to access shared documents from my location, so apologies if the answer to my question was included in your workbook. Can you elaborate on what part/function of the formula was not working? Is it just erroring out, or is it the wrong value?

Confirmed that formula is entered as an array?

If something else, copy your formula and post it exactly as you've got it written?
 
Hi leimst,


Try this in your spreadsheet


=INDEX(A2:A1000, MATCH(LARGE(COUNTIF($A$2:$A$1000, A2:A1000),2), COUNTIF($A$2:$A$1000,A2:A1000),0))


Enter, Ctlr+Shift+Enter
 
You must log in or register to reply here.
Back
Top