Counting Consecutive Months in Top Third

Ok. I need to have a formula which would look at a number of columns
and tell me now many 'consecutive' months someone has been in the top
33% of the numbers in that column to date. IE . . .Ann was in the top
33% in Jan and Feb but not in March so in March I would want the
formula to spit out '2', In April I would want it to start over again
at '1' (If in fact she was in the top 33% in March, if not then it
should say '0'). Hardest part in figuring this out (in my head
anyway)
is how to get it to give me the number as of the current date.
Figures
=NON() would have to be in there somewhere but not sure how. Hope I
explained all that properly.

            JAN      FEB      MAR
Jane     34.5      44.6      77.8
Bob      22.3      12.5      34.6
Bill       32.6       87.7     44.4
Jill        44.3      66.5      34.8
Ann      87.6      55.2      15.6
Eric      74.5      33.3       13.8
Jim       44.7      22.8       22.3
Lisa      56.4      55.4       55.9
Sam     67.1      64.8       70.0

0
3/7/2008 3:39:39 PM
excel 39879 articles. 2 followers. Follow

1 Replies
868 Views

Similar Articles

[PageSpeed] 44

You need to make another table.  For example if your table starts in A1, with names in column B and 
labels in row 1, then in another cell in column B, say B21, perhaps, use the formula

=IF(RANK(B2,B$2:B$10)<=(COUNTA(B$2:B$10)/3),"Top","")

and copy to be as large as your data table.  Then you can use a formula like

=IF(XXX21="Top",1+SUMPRODUCT((C21:XXX21=B21:YYY21)*(C21:XXX21="Top")),0)
where XXX is the last column's letter, and YYY is the last column but one's letter.


HTH,
Bernie
MS Excel MVP


<agrandstaff@hotmail.com> wrote in message 
news:d99c7cc0-24b9-4f63-9c81-ded93ece0a50@q78g2000hsh.googlegroups.com...
> Ok. I need to have a formula which would look at a number of columns
> and tell me now many 'consecutive' months someone has been in the top
> 33% of the numbers in that column to date. IE . . .Ann was in the top
> 33% in Jan and Feb but not in March so in March I would want the
> formula to spit out '2', In April I would want it to start over again
> at '1' (If in fact she was in the top 33% in March, if not then it
> should say '0'). Hardest part in figuring this out (in my head
> anyway)
> is how to get it to give me the number as of the current date.
> Figures
> =NON() would have to be in there somewhere but not sure how. Hope I
> explained all that properly.
>
>            JAN      FEB      MAR
> Jane     34.5      44.6      77.8
> Bob      22.3      12.5      34.6
> Bill       32.6       87.7     44.4
> Jill        44.3      66.5      34.8
> Ann      87.6      55.2      15.6
> Eric      74.5      33.3       13.8
> Jim       44.7      22.8       22.3
> Lisa      56.4      55.4       55.9
> Sam     67.1      64.8       70.0
> 


0
Bernie
3/7/2008 8:17:26 PM
Reply:

Similar Artilces:

Bring my application to top
When I call a subprogram from my mfc MDI application with CreateProcess(...) and wait for the return with if(WaitForSingleObject(hpccmd, INFINITE) != WAIT_FAILED) { The focus often not goes to my application but to the last program on top before the application was started (could be Excell or MS Visual or AutoCad) (My application is mostly maximized when it runs) So what I need is a safe BringApplicationToTop() in its last postion and size --- Laurs Laursen On Apr 22, 9:01 am, Laurs <La...@discussions.microsoft.com> wrote: > When I call a subprogram from my mfc MDI application ...

add hours flown last 12 months
i'd like to add the total hours flown the last 12 months(sheet: totals) counting from the last entry (sheet: logbook). See attachement.. Attachment filename: logbook.xls Download attachment: http://www.excelforum.com/attachment.php?postid=62816 -- Message posted from http://www.ExcelForum.com Hi not really sure which column you want to sum (looking briefly at your attachment). You may try to explain your problem in plain text (most people won't open an attachment) -- Regards Frank Kabel Frankfurt, Germany > i'd like to add the total hours flo...

Calculate Gestational Age based on months and weeks
Hi, SO I want to make a pregnancy wheel: (LMP(Date) - 3months, +7d)= Estimated Date of Delivery (it also has to advance to the next year if >April. Also, I want to be able to automaticallt update the current Gestational age(#weeks/days since LMP to current date) based on today's date and the LMP. I found this code online at a website that calculates Gestational age in online calculator. Can it be converted for use in access? Thanks! <script> // current equation code function PregDates() { var lmpid = $("#lmp").val(); var ddid = $("#duedate...

sumif column of month are the same
a b c d 1 2 12-Jul-08 Sat 2.5 3 20-Jul-08 Sun 9 4 29-Jul-08 Tue 4 5 25-Aug-08 Mon 1.75 6 27-Aug-08 Wed 9.5 7 02-Sep-08 Tue 9.5 8 03-Sep-08 Wed 3.5 9 08-Sep-08 Mon 6 10 14-Sep-08 Sun 9.25 11 15-Sep-08 Mon 1.25 12 19-Sep-08 Fri 5.5 I try to sum the value if the month is the same but fail, Using cell d2 =IF(MONTH(a2)=MONTH(a1),SUMIF(a:a,MONTH(a2),c:c),"") and copy all way down =SUMPRODUCT(--(MONTH(A2:A1000)=MONTH(A2)),C2:C1000) -- R...

XPath: counting unique values
Hello, I would like to count the unique values of a specific element in an XPath statement. Let's say I have the next XML document ... <CLUB> <MEMBER> <NAME>Fred</NAME> <LOCATION>Canada</LOCATION> </MEMBER> <MEMBER> <NAME>Louis</NAME> <LOCATION>Belgium</LOCATION> </MEMBER> <MEMBER> <NAME>Gwendy</NAME> <LOCATION>Belgium</LOCATION> </MEMBER> <MEMBER> <NAME>Steve</NAME> <LOCATION>Portugal</LOCATION> &...

Count Formula #5
I will like to create a formula that will look in column a and if the field in column b matches it will give me a count. so for example if in a1 is blue and b1 is yellow than give me a count of 1 but they both must match blue and yellow? Thanks in advance for you assistance Dave Dave not really sure what you're after but here's two ideas for you =IF(AND(a1="blue",b1="yellow"),1,0) or =SUMPRODUCT(((A1:A100)="blue")*((B1:B100)="yellow")) Cheers JulieD "Dave" <anonymous@discussions.microsoft.com> wrote in message news:2c...

Grouped/Sorted Report by Month
I have a fairly simple access database. I've created a report grouped by Category and Month... with a total per month. My problem is that the months with no total don't show on the report. Is there a way to make the month show with a zero total? ie. January - 24 orders February - 30 orders March - 0 orders April - 28 orders ********This is what I'd like to see ... each month listed. Currently, March doesn't show at all on my report. Any help would be appreciated. SCS wrote: >I have a fairly simple access database. I've create...

Pivot Table
Hi All I have a list of tasks, task start / finish date and costs. Here's an extract Start End Cos Refine Work Plan 03/15/04 03/26/04 500 Define Project 03/22/04 03/22/04 50 Issue Plan 03/23/04 03/24/04 100 Risk Management Plan 03/25/04 03/26/04 100 CM Plan 03/26/04 03/26/04 50 P / M Standards 03/29/04 03/29/04 50 Create Quality Plan 03/30/04 04/02/04 200 Management Strategy 04/02/04 04/02/04 50 Software Requirements 04/05/04 04/05/04 50 Question: I want to create a pivot table with the tasks on the left and cost as the data. However, I want to capture the data by month. ...

Worksheet 501: Request full item count is timing out
I have noticed some discrepancies between my store inventory and my headquarters inventory. In order to bring the two inventories back in sync, I am trying to run worksheet 501: Request a full Item count. However, the worksheet keeps timing out. The worksheet will download, appear to be processing the worksheet for about 20 minutes, then return the error message: <<ExecuteCommand>> Error -2147217871: Timeout Expired UPDATE [ItemDynamic] WITH (TABLOCKX) SET [ItemDynamic].[SnapShotQuantity] = #UpdateRsToTable.[SnapShotQuantity], [ItemDynamic].[SnapShotQuantityCommitted] = #Upd...

Count #5
I have the following table for example Meat Chicken Vegetable is there a way to count in excell as total 3 items thanks =COUNTA(A1:A3) -- Gary''s Student - gsnu200803 "Hassan" wrote: > I have the following table for example > > Meat > > Chicken > > Vegetable > > is there a way to count in excell as total 3 items > > thanks > ...

First Day of the month 03-25-10
Does anyone know how to have excel look at a cell that has a date that is formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as 01/01/2007 in B2, another words view as date as first day of the month. In B2: =DATE(YEAR(A2),MONTH(A2),1) -- Gary''s Student - gsnu201001 "Jen_T" wrote: > Does anyone know how to have excel look at a cell that has a date that is > formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as > 01/01/2007 in B2, another words view as date as first day of the month. On Thu, 25 Mar 20...

COUNT while eliminating duplicate values
I am trying to get a count for the number of employees in a range but want to eliminate duplicate values. Does anyone know the easiest wa to accomplish this ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way: http://j-walk.com/ss/excel/usertips/tip061.htm HTH Jason Atlanta,GA >-----Original Message----- > >I am trying to get a count for the number of employees in a range but I >want to eliminate duplicate values. Does anyone know the easiest way &g...

Changing 'volume' and 'issue' numbers in monthly publications
A previous post suggested saving the current newsletter as 'publisher template' then entering the new information. However, when I do this Publisher does not update the 'issue' number. How do I accomplish this without starting from scratch each month and even then, get the 'volume' and 'issue' numbers to automatically update or change them manually? -- Midge How would the program know what system you use for the designations? Do it manually. You only have to change it on the cover. -- JoAnn Paules MVP Microsoft [Publisher] "Midge" <Mi...

Data counting problem
I have a column of numbers 1 through 3000. Another column has a few numbers scatered throughout this range. I want to have a third column that runs the entire height of 3000 cells with either ones or zeros corresponding to the first column where if the number exists in the second column it gets a one and if it doesn't it gets a zero. Can anybody help me? I've been looking into CountIf stuff, but I can't seem to get the criteria to be any of the numbers in column 2. Thanks in advance. -- GlitchCog ------------------------------------------------------------------------ GlitchCog...

Print Group headings on top of page
Hi, I'm running Access via Xp Office Pro on Windows 7. I have a report that is broken out by group using a block format and was generated by the print wizzard. I noticed that the group values (in my case a status description) is only printed the one time. If the group crosses over a page, I want to print the current value of the group value at the top of the next page. Is that possible? And if so, how do I do it? Thanks, Dennis Dennis wrote: >I'm running Access via Xp Office Pro on Windows 7. > >I have a report that is broken out by group usi...

Auto Numbering a cell on consecutive worksheets
I have a workbook with several worksheets. Each worksheet is an numbered invoice. As I add worksheets, is it possible to have the invoice number automatically displayed in consecutive order? What are you doing to add worksheets? Post back with a detailed step-by-step process of what you do to add a worksheet. HTH Otto "Wally" <Wally@discussions.microsoft.com> wrote in message news:3AD11A82-8264-46A5-8128-2B9840B5BB1B@microsoft.com... >I have a workbook with several worksheets. Each worksheet is an numbered > invoice. As I add worksheets, is it possible to have th...

count related records
2 tables - 1) tickets and 2) messages. Tickets contains the main data and messages contains all the comments made relating to that ticket. How do I count the number of records (or messages) in the related table? So, I have 1 ticket in the system and 4 comments have been made in that ticket. I want to be able to take that number and append it to another ticket table showing "4" in that field. Appending the count to another ticket table is not the thing to do! Say you append 4 and then add another message. The count is now 5 but you only have 4 in the other table....

XPathNodeIterator.Count Performance Issues
Hi, I'm trying to compare two XML documents and i'm using XPath queries to select nodes. XPathNavigator's Select method runs fast enough and returns an XPathNodeIterator object. When i try to access this iterator's Count property the process extremely slows down. If i just iterate throgh 18.000 nodes and call XPathNavigator.Select to find equivalent node from the other document it doesn't even take 1 second. But in the same loop, when i try to access XPathNodeIterator.Count, (by accessing i mean just assigning its value to a variable) it takes about 5 minutes. I tried to ...

Use two combo boxes to control a third
I have a form and a subform. In the subform is a combo box (#3). I want to filter the list of available options for this combo box based on the selections made on two other combo boxes (#1) and (#2). I was able to get this to work by putting the code as a condition on the query for combo box #3 when all three combo boxes were on the same subform. But I wanted combo box #1 on the main form, while I kept combo #2 and #3 on the subform. I can get combo box #3 to respond to the criteria found in Combo #1, but not both #1 and #2. Is this even possible? Thanks in advance for the help! If I w...

How do you stop blanks being counted as zero's
I have a database with two columns of single figures. I've been usin the IF function to identify whether a cell contains a zero. If Yes 1 i returned, if No 0. For some reason Excel 2000 seems to count the blank cells as zero's! Have i set up my page wrongly or is there some other reason? Anyone -- Message posted from http://www.ExcelForum.com Not sure why it's doing what it's doing. Count will only count numbers, CountA will count numbers and text, but both ignore blanks. Try using a CountIF function instead e.g. =COUNTIF(A1:A10,0) This will count the Zero's in a ra...

Counting Workdays Function
When i try to call the fuction i get a error I am using CalcWorkDays([DateDiverted],[dtmEnd]) The error says The expression is typed incorrectly or it too comples to be evaluated Does anyone know what wrong this I am a beginner VB Function CalcWorkDays(DateDiverted As Date, dtmEnd As Date) As Integer 'Calculated the number of working days between two dates 'DateDiverted - the first day to include in the range 'dtmEnd - the last day to include in the range 'Returns the number of working days between the two dates 'Both dates are counted if they are working days Dim in...

Counting Problem #4
Hi everyone, My problem is with the COUNTA funciton. I have a formula "=IF(COUNTA(K4:Q4)>0,1,0)", set up so that if at least one cell has letter/value, then formula will return a result of 1. The cells, whic this equation counts, are also formulas as well. (Ex =IF('Protocol!$K4="","",'Protoco !$K4)) I want to be able to count the cells that have data, yet not coun those cells which have a blank result. Is there anyway to do this i Excel? Thanks and Have a great weekend, Pet -- peter_river -----------------------------------------------------------...

Top Affiliate Program
Up to $10,000 Earning from Each Single User. Sign Up Now!... http://www.forex-affiliate.com/Affiliates/main.aspx?ref=5121 ...

Count cells with data
I receive large databases each month. I have been able to format the data using PROPER. Though when I follow the instuctions from "3 formatting shortcuts" it calls for dragging the fill handle to the end of the new column to show all the converted cells. My databases are hundreds of names long! Is there a way to count the cells with text in them in advance - without scrolling down to the bottom - and then put the range into the =PROPER (range of cells with data) command? -- Thank you kindly You are using a second column to convert to Proper? You can Double-Click the Fill...

Number Counting
This is probably something simple, but I can't figure it out. I have columns with two digit numbers (00 thru 99). I want to write a formula that looks at this column and if any number is repeated, total the number of times it's repeated. Thanks so much for the help!! =countif(data-range,comparison-value) "DNA" wrote: > This is probably something simple, but I can't figure it out. > > I have columns with two digit numbers (00 thru 99). I want to write a > formula that looks at this column and if any number is repeated, total the > number of times it&#...