Top x per criteria (Function)

Lets say I have 50 sheets, one for each state.  I also have a sheet
that has a years worth of data.

So the data sheet has something like this (simplified)

State    Month     Amount  User
RI         Jan         5100      1234
RI         Jan         5101	 1235
RI         Jan         5001      1244
RI         Feb         5100      1234
RI         Feb         5111      4343
RI         Feb         5122      2343
RI         Feb         5122      2343
RI         Feb         4900      3234
RI         Mar         5100      2344
TX         Jan         5100      2345
TX         Feb         8999      0989
Etc


I want to find the top 3 Users where 
1. The state equals a cell
2. The month equals a cell.
3. The amount is over a certain amount ( can be hardcoded as 5000)

So each state sheet looks something like this´┐Ż

RI
Feb

User    Amount


And I need it to return the top three amounts (and the User) per
state.  I have been using Match and Large to try to pull them, but I'm
not getting anything to quite work.  Getting close, but not quite.

Having ties is not an issue for what I need.


Thanks a ton in advance for any assistance.
0
nvts (2)
6/16/2004 7:12:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
577 Views

Similar Articles

[PageSpeed] 38

Hi
if your report is on a separate sheet with the 3 conditions in A1:A3
enter the following formulas as array formulas (CTRL+SHIFT+ENTER) in

A4:
=INDEX(sheet1!$D$1:$D$12,MIN(IF((sheet1!$A$1:$A$12=$A$1)*(sheet1!$B$1:$
B$12=$A$2)*(sheet1!$C$1:$C$12=$B4),ROW(sheet1!$C$1:$C$12))))

B4:
=LARGE(IF((sheet1!$A$1:$A$12=$A$1)*(sheet1!$B$1:$B$12=$A$2)*(sheet1!$C$
1:$C$12>=$A$3),sheet1!$C$1:$C$12),ROW(1:1))

copy both formulas down


--
Regards
Frank Kabel
Frankfurt, Germany


JD wrote:
> Lets say I have 50 sheets, one for each state.  I also have a sheet
> that has a years worth of data.
>
> So the data sheet has something like this (simplified)
>
> State    Month     Amount  User
> RI         Jan         5100      1234
> RI         Jan         5101 1235
> RI         Jan         5001      1244
> RI         Feb         5100      1234
> RI         Feb         5111      4343
> RI         Feb         5122      2343
> RI         Feb         5122      2343
> RI         Feb         4900      3234
> RI         Mar         5100      2344
> TX         Jan         5100      2345
> TX         Feb         8999      0989
> Etc
>
>
> I want to find the top 3 Users where
> 1. The state equals a cell
> 2. The month equals a cell.
> 3. The amount is over a certain amount ( can be hardcoded as 5000)
>
> So each state sheet looks something like this.
>
> RI
> Feb
>
> User    Amount
>
>
> And I need it to return the top three amounts (and the User) per
> state.  I have been using Match and Large to try to pull them, but
I'm
> not getting anything to quite work.  Getting close, but not quite.
>
> Having ties is not an issue for what I need.
>
>
> Thanks a ton in advance for any assistance.

0
frank.kabel (11126)
6/16/2004 8:48:54 PM
Ok, I got the Amount to work, having issues with the user name that
goes along with that value.

I copied the test I'm working with into a single sheet, and here is
what I get.

State	Month	Amount	User	RI	
RI	Jan	5100	a	Feb	
RI	Jan	5101	b	5000	
RI	Jan	5001	c		
RI	Feb	5100	d		
RI	Feb	5111	1	d	5122
RI	Feb	5122	2	d	5122
RI	Feb	5122	3	d	5111
RI	Feb	4900	4	User	5100
RI	Mar	5100	5	User	#NUM!
TX	Jan	5100	6		
TX	Feb	8999	7	


I expect the #num, and I'll worry about that after I get it all
working.

Here is my formula chunk. (I swapped $C$1:$C$12=$B4 to $C$1:$C$12>=$B4
as it looked like an error)


=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E3),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(1:1))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E4),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(2:2))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E5),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(3:3))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E6),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(4:4))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E7),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(5:5))




"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:<#vRcwM#UEHA.2484@TK2MSFTNGP11.phx.gbl>...
> Hi
> if your report is on a separate sheet with the 3 conditions in A1:A3
> enter the following formulas as array formulas (CTRL+SHIFT+ENTER) in
> 
> A4:
> =INDEX(sheet1!$D$1:$D$12,MIN(IF((sheet1!$A$1:$A$12=$A$1)*(sheet1!$B$1:$
> B$12=$A$2)*(sheet1!$C$1:$C$12=$B4),ROW(sheet1!$C$1:$C$12))))
> 
> B4:
> =LARGE(IF((sheet1!$A$1:$A$12=$A$1)*(sheet1!$B$1:$B$12=$A$2)*(sheet1!$C$
> 1:$C$12>=$A$3),sheet1!$C$1:$C$12),ROW(1:1))
> 
> copy both formulas down
0
nvts (2)
6/17/2004 3:54:39 PM
Reply:

Similar Artilces:

nz function
Hello, I am have a report that has several subreports in it.. There is a summery section in the header section of the main report. However if one of the sub reports Reports #Error that will be carried through to the summery on the main report. This only happens when the sub report has no data to report so the total on the footer of the sub report show #Error as well. I have placed =NZ(Sum([tot_min]),0) in the sub report and is still shows #Error. I have tried the same in the Summary sectoin of the main report and Still #Error. What I am I doing wrong please... Thanks In Advance Rm...

Date() function will not work
I am trying to run a query in access 97. I get an error message when trying to use Date(). I am trying to use it to return today's date. I get the following error messaage: "The function isn't available in the query expression" Any suggestions? Try this: SELECT Date() as [A Date] FROM <tablename> Date() -> is the date function [A Date] -> is the column name. Change this as needed. HTH -- Rob FMS Professional Solutions Group http://www.fmsinc.com/consulting Software Tools for .NET, SQL Server, Visual Basic & Access http://www.fmsinc.com -=-=-=-=-=-=...

VLOOKUP FUNCTION #4
Hey all...I'm performing a vlookup function and am having a problem. I enter the formula looking up a numerical value in a different sheet within the same excel file. I know the number I am looking up is in the sheet I am looking and has a value in the column I am referencing. However, I get a "#N/A" response. The cells are formatted the same and for all intensive purposes the vlookup should work. In fact it works in some, but not in others, yet I know every one of the numbers I am looking up is in there. Anyone have this sort of problem and know how to fix it? Than...

function to populate a cell where formula is NOT located
I'm using excel 2007. I'm looking to have a logical test in one cell that will populate a different cell if true, or a third cell if false. For example, the logical test located in cell A1: If (A2=3, then Cell B2 = "True", then Cell B2="RED") Another variation is to have the value in a cell determine the new cell location. For example, IF(D1="","", then cell A(Row Number D1)="Populated") Solutions? In your first example, put a formula in B2 to test the value in A1. In your second example, you could use a simple ...

AfxGetApp() return NULL in CALLBACK function.
I am writtingan application using MFC with an CALLBACK function and a DLL that use this CALLBACK function. The problem is: if I try to get my main app by calling: AfxGetApp() within the callback function, it often returns the NULL value. //MyClassView.h class CMyClassView : public CFormView { ........ public: //CALLBACK static int _stdcall CBFunctionProc(LPARAM lParamCB,LPCTSTR strData); int FunctionProc(LPCTSTR strData); // ...... } //MyClassView.cpp int _stdcall CMyClassView::CBFunctionProc(LPARAM lParamCB,LPCTSTR strData) { BOOL bOk = TRUE; static CMyClassView* pThis = (CMyC...

How to Publish a Public Function (vb module) for use in other mdbs
Hi All, I created a Public function in a particular mdb and I want to make it available to other Access db's when running queries. How do I "publish" a module so that it's available as a default whenever I open/create a new database? Is there some sort of blank "template" db that it needs to go into, or something else. Can't for the life of me find it in the VB Help that goes with Access. Thanks! Hi Chris, Here you go: Using Database Library Files in Your Access Application http://www.vb123.com/toolshed/07_access/libraries...

x axis marker ???
I don't know what to call this, but here is what I need: I have a trend line and I need to determine where it crosses zero. I want a vertical marker where the trend line crosses zero so I can determine the value of other parameters at that point along the X axis. In my mind's eye, I put the cursor on the point and tell the computer; "Draw a vertical marker here and label it at the bottom as to the X axis value." If I could do the same along the Y axis, that would be really cool Thank you, Bryan Kelly Time is the medium we use to express out priorities. Brian - Calcula...

how to call parent's operator = function?
Hi, If a parent class and a child class both have an operator = function, how does the child's = function call its parent's = function? Thanks for your help. Tony class A { public: ... A& operator= (const A& a); }; class AA : public A { public: AA& operator= (const AA& a); }; A& A::operator= (const A& a) { ... return *this; } AA& AA::operator= (const AA& a) { *this A::operator= a; // ????? return *this; } "May Young" <tony@srac.com> wrote in message news:40A54E60.4030400@srac.com... > Hi, > > If...

Problem with row to repeat at top
If my pages are spanning horizontally instead of vertical, how can create the same print title by "row to repeat at top" at every pages Many thanks. :confused -- goko ----------------------------------------------------------------------- gokou's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1615 View this thread: http://www.excelforum.com/showthread.php?threadid=27590 You can manually repeat the headings across the top row. Either copy and paste them, or link the cells to the headings on the first page. gokou wrote: > If my pages are spannin...

Lookup function Question HELP
This is a multi-part message in MIME format. ------=_NextPart_000_0032_01C4B158.8F36ACE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm trying to perform a lookup function =3DLOOKUP(H8,sheet2!A:A,sheet2!C:C) This formula only returns the first item of a list of items matching the number in A:A (there could be more than 10 under the catalogue number = in A:A. I've made a list in B:B (1,2,3 etc.) for each of the catalogue = numbers in A:A How would I augment B:B into the formula to coincide with the number in = the "B...

Entourage X / Outlook (4.4.5 Insufficient disk space; try again later)
When sending an email it says: Error Mail could not be sent using account "emai". Explanation 4.4.5 Insufficient disk space; try again later Error 5452 My ibook still has 1.09 GBytes of storage and I never had this message before. ...

Viewing backup copy of Outlook Pers. files
Hi, Each time I insert my CD backup, the system tells me it does not know which program was used to make the backup. It asks me to choose from a list which does not contain the Outlook program, although I work with Outlook 98% of the time. How come ? Does it mean that the backups I made over the last year are all unusable ? How can I view my back ups ? HELP ! PS My system is in English, but I am French speaking, so please help in either langage. Thanks, D. The pst cannot be read directly from a cd. Copy pst to cd, then to hd, ensure read only flag is off before, File/Open/Personal Fo...

Top value
Hi, i have a table like this: john 4500 john 1200 mary 900 jim 5000 john 3700 mary 2300 jim 1400 And i need formulas to extract top 5 values: I need something like this: John 9400 jim 6400 .....etc Can this be done? Thanks! Assign header to both columns and use Pivot Table. Data>Pivot table....Drag and drop name to 'Row field' and the values to Data items area.. set AutoSort options from Right click>Field Settings>Advanced> -- Jacob (MVP - Excel) "puiuluipui"...

[VOTE]: NEED of a page break function
Hi, I think we NEED a page break function in OneNote. In OneNote 2007, there's an option to select A4 as the page size but I can only have a single sheet of "paper" for every page. This is frustrating as I don't want to create new page every time I run out of inking space. Another reason I want to select A4 size (and have multiple sheets with page breaks) is because it is better for printing (to physical paper or pdf). Right now, having the "infinite page" as default, the page breaks just occur randomly when I print my stuff from OneNote to pdf/physic...

Need Help Converting Functions
I am at an account that is in the process of rolling out Access 2002, and I have run into a problem with various functions that used to work in Access 97 but now generate various error messages (permission Denied for one) when called in Access 2002. Two functions in particular are FileCopy and Kill. I hunted around on the Knowledge Base and found article # 294698. It says a group of functions can be disabled/enabled with the Sandbox Mode on Service Pack 6 and earlier. Then it goes on to say that a whole bunch of other functions (FileCopy and Kill among them) are disabled when called ...

Access Runs Non-Existing Function
I've running a function from the On Close of one of my forms for years. In wanting to know what the code is since I haven't seen it for a while I have found that the function doesn't exist. A search of all 4 of my code modules didn't turn up the function name. When I take the code out of the On Close of the form the code doesn't run. "JamesJ" <jjy@darwin_roadrunne0r.com> wrote in message news:%23kyymGrZKHA.1640@TK2MSFTNGP06.phx.gbl... > I've running a function from the On Close of one of my forms > for years. > In wanting to know ...

IIF statement in query criteria, help!
I have a query in which one date need to be filtered based on another field. If the field [last] =1 , the data needs to be filtered showing only data where [ShiftDate]>=[Start1]. If the field [last]=2, it is [shiftdate]>=[Start2]. So in the criteria for field [ShiftDate], I entered "iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])". I don't get an error, but I also don't get any results. If I enter ">=[start1]" or [shiftdate]>=[start1] instead of the iif statement, I get the results expected. I'm pretty sure that I...

Countif unique item with multiple criteria
Hi, How do I use the Count if or sumproduct formula to count cells with unique items. The database is organised as follows Product Year Segment Customer Amt A 2005 F XA A 2005 F XB A 2005 F XA A 2005 F XC A 2005 G XA A 2006 F B 2005 F B 2005 G B 2006 G B 2006 G Now if I want to count the number of Unique customers in Product A, for Year 2005, in Segment F, how is the countif or subproduct formula...

Function to indicate the amount of characters in a cell?
Trying to do a conversion of data into a flat file. Using excel we have a column called Project Description. I want to run a function to ensure that the project descrption in each cell does not exceed 70 characters. Is there a function that I can use for this? For example A project for all to review This title has 28 characters (counting from A to w), other than manual not sure if this can be done Thanks Try this: This function returns the number of characters in Cell A1 =LEN(A1) This function returns up to 70 characters from cell A1 =LEFT(A1,70) Does that help? *********** Re...

function slef
hello, i have a quick question. if i click in a cell with a lot of text, it will show up in the Fx (function) slef. the problem is that it takes up half the screen. is there a way to turn off showing text in the function slef, when clicking in a cell? thanks What's the function slef? If you mean the formula bar? Tools>options>view and uncheck formula bar or just view>formula bar -- Regards, Peo Sjoblom "greg" <greg@nospam.com> wrote in message news:eizKpC0HFHA.1580@TK2MSFTNGP10.phx.gbl... > hello, > i have a quick question. if i click in a cell w...

Disabling a listbox, yet keep scrolling functional...
I've tried many programmatic attempts to disable a listbox (created using the wizard), while keeping the scrolling functional. Now surely Microsoft considered this a quite likely use case - allowing the user to see ALL the contents, but having them non-selectable and the box greyed out as a visual cue - so I assume a straightforward way exists (i.e., one without having to take over drawing, etc.). I'll tell you what doesn't work, e.g.: disabling the control, followed by calling the CWnd base class EnableScrollBar function with the appropriate arguments. Using C++. Thanks ...

Function for identifying the last entry in a table
Certainly there is some function or combination of functions that solves for identifying the last entry in a column. Any help would be appreciated. Example below. column a row 1 aaa row 2 bbb row 3 ccc row 4 ddd row 5 row 6 I would like a function that would use the array A1:A6 to identify the last entry, that being "ddd" which was found at A4. thanks One way: =LOOKUP(2,1/(A1:A999<>""),A1:A999) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) TiChNi wrote: > > Certainly there i...

Square with Red X
Sometimes I get e-mails and instead of showing graphics or pictures, There was a small square with a red X inside the upper left hand corner. Could anyone explain this and is there a way to show the graphics or pictures? Thank you in advance for your assistance. HankL http://www.fjsmjs.com/IE/redx.htm -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "HankL" <hlaskin@bendbroadband.com> wrote in message news:uPxX%238dfJHA.4932@TK2MSFTNGP02.phx.gbl... > Sometimes I get e-mails and in...

Are Pivot Charts only useful with limited number of categories(x-axis)?
I'm brand new to Pivot Charts and trying to figure out what I thought would be simple: I have a Column Chart with $Sales for the Y axis and Salesperson on the X(category) axis. There are hundreds of salespeople, which results in the chart displaying extremely thin lines with totally unreadable category labels( salesperson initials). Is there not a way to specifiy a gap between categories on the X axis? Or some way to space them apart and be able to scroll the chart horizontally? I have a feeling you are all going to tell me scrolling is just a dream :) TIA, -Mike ...

AppleScript to keep Office 2004 or X from opening files and database on wrong drive?
I've noticed that immediately after I've cloned volumes to the backup internal hard drive with Retrospect's "Duplicate" feature, the first time I click on a Word or Excel file alias, it leads to the wrong drive. It seems that that the files on the backup drive at that point might be seen by OS 10.3.x (and Office) as newer/more recent than what's on the primary drive from which they've been cloned.. I was told that there "always" have been "minor issues" with Retrospect and aliases. Unfortunately, I learned from experience that "minor&qu...