I need help with an Excel formula.
In Worksheet #1 I have roughly 800 state abbreviations listed. The column is M.
I need to set a formula to pull the top five most occurring states and automatically place them in Worksheet #2 in cells A19:A23.
I thank you in advance for your assistance!
Last edited by KINNEY0201; 08-23-2010 at 09:53 AM.
Say you enter the most frequent occurence in C1 with formula:
adjust ranges to suit.
Please Login or Register to view this content.
then in C2 enter:
adjust ranges to suit and confirm this one with CTRL+SHIFT+ENTER not just ENTER and copy downPlease Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
The column M is in a different sheet. I tried adjusting, but ended up getting N/A as a result. Here was the formula:Originally Posted by NBVC
Say you enter the most frequent occurence in C1 with formula:
adjust ranges to suit.Please Login or Register to view this content.
then in C2 enter:
adjust ranges to suit and confirm this one with CTRL+SHIFT+ENTER not just ENTER and copy downPlease Login or Register to view this content.
=INDEX('FA 08-20-10'!$M$2:$M$2000,MODE(MATCH('FA 08-20-10'!$M$2:$M$2000,'FA 08-20-10'!$M$2:$M$2000,0)))
Do you have blanks? Blanks not allowed.
If you have blanks at bottom of list and you want it to be a bit dynamic..
In an empty cell, say D2 enter formula: =MATCH(REPT("z",255),M:M)
then replace formula in C2 with:
and formula in C3 with:Please Login or Register to view this content.
confirmed with CTRL+SHIFT+ENTER and copied down.Please Login or Register to view this content.
I'm sorry but I don't follow. There are blanks throughout, just not at the bottom. The original spreadsheet needs to stay intact and unsorted. Isn't this possible. And as far as cell references. I need the 5 top results to be located in cells A20:A24.
Ok,
Go to Formulas menu and click Define Name from the Defined Names section
Enter LastRow in the Name field and enter formula: =MATCH(REPT("z",255),$M:$M) in the Refers to field.
Then in A20 enter formula:
confirm it now with CTRL+SHIFT+ENTERPlease Login or Register to view this content.
in A21 enter formula:
and confirm with CTRL+SHIFT+ENTER and copy downwards to A24Please Login or Register to view this content.
Which cell or location should I be when entering the "define cells"?
Doesn't matter.
Did not work. Once I corrected formula to refer to sheet one, it returned the top state as "state" which is the heading of the column.
=INDEX('FA 08-20-10'!M2:INDEX('FA 08-20-10'!M:M,$D$2),MODE(IF(ISNUMBER(MATCH('FA 08-20-10'!M2:INDEX('FA 08-20-10'!M:M,LastRow),'FA 08-20-10'!M2:INDEX('FA 08-20-10'!M:M,LastRow),0)),MATCH('FA 08-20-10'!M2:INDEX('FA 08-20-10'!M:M,LastRow),'FA 08-20-10'!M2:INDEX('FA 08-20-10'!M:M,LastRow),0))))
Last edited by shg; 08-23-2010 at 01:03 PM. Reason: deleted quote
Possible to attach your workbook?
Unfortunatley not due to the content. Student information.
I was able to get the formula below to work perfectly to produce the #1 most common state, however, I am unable to figure out how to get the next four states below it. I tried copying down but it just repeated the same state. Any suggestions?
=INDEX('FA 08-20-10'!M:M,MATCH(MAX(COUNTIF('FA 08-20-10'!M:M,'FA 08-20-10'!M:M)),COUNTIF('FA 08-20-10'!M:M,'FA 08-20-10'!M:M),0))
Did you adjust the Defined Name, LastRow's range to the other sheetname too?
i.e. it should be: =Match(Rept("Z",255),'FA 08-20-10'!$M:$M)
With your suggested formula? Yes.
Any idea on the last formula I listed? How to get it to list the next highest 4 states? At this point, it looks like the easiest route, but then again, I'm not the expert, that's why I'm here for help.
Last edited by shg; 08-23-2010 at 07:41 PM. Reason: deleted spurious quote
See my sample (I think working) workbook, using your sheetname and the results are in Sheet1.
See if you can find any differences with your workbook....
Find an example of what you want attached.
I did it in a few steps which keeps the formulas smaller.
I hope this example will help you solve your problem.
Wouldn't work. I removed the data from mine and left the needed columns and attached here. If you wouldn't mind, take a look at the formula that is already entered and see if we can work from that to bring the next 4 states into play.
Thanks,
See the attached.
I did not see any Defined name for LastRow
Do the states not change when the main list changes? For example, I took out all of the IL to see what would happen, but Illinois still remained in the top 5????????
Last edited by shg; 08-23-2010 at 07:40 PM. Reason: deleted spurious quote
It should not... I just deleted all the IL's and now my 5th item in the result list is CT.
Did you, in fact delete all of the IL's?
Also, go to the Formulas tab and in the Calculation Option drop down, make sure it has Automatic selected.
Thanks for the example, however, I need to figure out a way to not use other columns for the formulas and enter the actual formulas into the 5 cells that will show the results.
If you are allowed to make intermediate data the attached might be an easier answer.
Those real long formulas make my head hurt so I try to do the problem in small steps.
I guess I'm too old to follow a formula that goes to a second line.
Yes, I deleted all IL's. And Calculation Option is already set to automatic. Any other possibilities?
No there aren't as far as I know...
Are you in the same book I re-attached or are you in your original?
I tried copying your 5 cells with formulas into my original spreadsheet.See the attached.
I did not see any Defined name for LastRow
You made sure that they are not now referring to an outside workbook source?
You have the proper formula in the Define Range "LastRow"?
You reconfirmed each formula with CTRL+SHIFT+ENTER?
OK, I finally figured it out. All I had to do was make it reference my original spreadsheet and not yours.
One more question........if I wanted to imbed an exception formula into the formulas you provided, where would they go. For example, I need to only count states of students who meet certain criteria. Specifically, they must meet all of the following:
('FA 08-20-10'!B:B="FTIC")
('FA 08-20-10'!B:B="TRANS")
('FA 08-20-10'!E:E="STU")
With these new conditions, the formulas are becoming a lot more inefficient.... Perhaps it might be wiser to invest in some helper columns to do some of the work and reduce the need for heavy array formulas...
But, assuming you are indeed in XL2007, you can try these and see the affect.
ANDPlease Login or Register to view this content.
Please Login or Register to view this content.
Tried the first........got "N/A". Do I need to use them both or something in order for it to work? Thanks in advance for your help.
Last edited by shg; 08-23-2010 at 07:40 PM. Reason: deleted spurious quote
Alright, it probably isn't working.. the MATCH() parts in the formula are getting messed up..
I really do think you need to add a couple of helper columns to get this achieved as efficiently as possible.
What I suggest...
in N2 (or free column in the FA 08-20-10 sheet enter:
copied down.Please Login or Register to view this content.
in adjacent column, O2 (or free column) enter:
then in main sheet: in a free column in row 19, enter formula:Please Login or Register to view this content.
copied down 4 more cells.Please Login or Register to view this content.
then in A19:
confirmed with CTRL+SHIFT+ENTER and copied down 4 more cells.Please Login or Register to view this content.
We will just keep trying to fix the match parts of the formula as we are trying to avoid helper columns. Thanks very much for your help, however, you got us real close!
Last edited by NBVC; 08-23-2010 at 03:49 PM. Reason: Deleted quoting of whole post.
Is it possible to fix the MATCH problem or any other way to do it? I cannot create any templates or add formulas to the FA 08-20-10 sheet because each week I will be dropping brand new data into it. I appreciate all of your help!
Last edited by shg; 08-23-2010 at 07:39 PM. Reason: deleted spurious quote
Kinney, please stop quoting whole posts. It's just clutter.
Use the REPLY button, not the QUOTE button.
Entia non sunt multiplicanda sine necessitate
Sorry..........................
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks