I have to calculate bonus payments for people working O/S. I have a 5 year calendar in month blocks (60 months/columns). In each month there is a percentage 'time O/S' figure.
If an employee is O/S for 3 consecutive months they get bonus 'A', 6 consective months bonus 'B', etc. How can I evaluate the 60 columns, returning the number of times 100% occurs in 3 consecutive months/columns. I have found examples of similar solutions but they will return a value of 3 if there are 5 consecutive months of 100%...
ie. 100 100 100 100 100
= 100 100 100 *** ***
& *** 100 100 100 ***
& *** *** 100 100 100
= 3
but I need it to equal 1
In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.
The values in column BN are text with either of those names.
I need to return two values in cells BO7 and BO8 respectively:
1) The number of consecutive days back from the most current day that have been "Over" the limit
2) The date going from the most current day that the first "Over" occurred
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like
Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
In one column, i get the ID. In the next column of the same row i get the status. My question is to find the number of occurrence of a specific ID with a specific status on another sheet. Attached worksheet sheet "count" has the source with ID at column C and status at column D. My expected result is in another sheet "expected result". I think a macro is needed, but i can't really figured out the way to do this counting.
I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".
Countif can be used to count the no. of occurrence of a certain value within a range. However, if the range is being filtered, can the no. of occurrence be counted?
Will it be possible to Count continuous occurrence of each signs 1-X-2 and arrange them in corresponding column From Q To AE as shown below in example table: 1
Draw result is shown in cells A2:N11 and result of Count continuous occurrence of each signs 1-X-2 is shown in cells Q2:AE11
For example we take a look row 7 where first sign-2 has occurred 3 times, as
Column Q is for to place result of sign1 And column R is for sign-X So far count of sign-2, which is 3 times, must be paced in cell S7, And then count of sign-1, which is 1 time, must be placed in cell T7, And then count of sign-2, which is 5 times, must be placed in cell V7, And finally count of sign-X, which is, also 5 times, must be placed in cell X7,
Will it be possible to Count continuous occurrence of each signs 1-X-2 and arrange them in corresponding column From Q To AE as shown below in example table: 1
Draw result is shown in cells A2:N11 and result of Count continuous occurrence of each signs 1-X-2 is shown in cells Q2:AE11
For example we take a look row 7 where first sign-2 has occurred 3 times, as
Column Q is for to place result of sign1 And column R is for sign-X So far count of sign-2, which is 3 times, must be paced in cell S7, And then count of sign-1, which is 1 time, must be placed in cell T7, And then count of sign-2, which is 5 times, must be placed in cell V7, And finally count of sign-X, which is, also 5 times, must be placed in cell X7,
I have a list of names (300 now but growing every month by 100-200). In the row with the name contains data I need to view: date, $, #, etc...
I want to quickly see whos name appears the most, 2nd most, 3rd most, etc.....(at the end of the year I will have 2000+ names, most names will only be listed 1x, I suspect nobodies name will be listed more than 60x.)
I also need the ability to view the relative data of the person's name that is listed most, 2nd most, etc....
I have a spreadsheet that lists all the work done by employees within a specific area. Some employees cover multiple areas.
I am now needing to work out the average work completed by each area. I need a formula that will count the number of employees by each work area.
I know this is something I have done before, but my mind has gone blank and I can't for the life of me work it out again (it's one of those days).
Row 3 of the spreadsheet contains the codes for the work areas they cover (CM, V & TC) and some employees only cover one (which would be a simple CountIf) but some have multiple.
What is the formula to, for example, count the number of people who have CM in row 3 even if they also have other entries in that cell.
In an employee attendance file I am trying to count the number of times an employee has taken 3 or more days of leave together (continuously) in a month. My attendance file looks something like this
sat sun mon tue wed thus fri
[Code] ..........
In the example above E001 has taken 3 continuous days leave twice so formula should return 2, for E002 & E003 the answer would be 1 each.
I know there's a pretty compley formula out there that counts the occurence of say Fridays in 2009 - does anyone have this? I had it before in a file but ranged valued the results showing the count of each day of the week for each month in a given year.
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like:
1 1 1 0 1 0 0 0 1 0 0 1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly. Does anyone see what's going wrong here or is there a better formula to this?
I'm trying to come up with an array formula to count the consecutive declines or increases from the last value in a column, going from bottom to top. The real world application is a list of daily stock prices down a column, and counting from the most recent day, how many consecutive days the price declined or increased.
I got some hints in the following post but can't get it evaluate the resulting logical array in reverse order (which is needed since column addresses are evaluated top to bottom, not bottom to top).
Count consecutive increase in values
Example for consecutive increases leading to last value 49 50 51 52 53 49 55 56
I need a formula to return 2, the number of increases from 49 to 56.
I am a remodeling contractor trying to monitor trips my employees are making to the lumberyard. I am able to export to excel from our accounting program a column of dates that invoices are made and another column that tells me whether trips were made before 8:30, between 8:30 and 3:30, after 3:30, or whether materials were delivered involving no trip. So I can count "time of day" trips. I also want to know if multiple trips were made in a single day, or if trips were made 2, 3, 4, 5, etc. days in a row.
Column 1 Column 2 Monday, July 6 2009 Before 8:30 Monday, July 6 2009 Between 8:30 and 3:30 Tuesday, July 7 2009 Between 8:30 and 3:30 Wednesday, July 8 2009 Before 8:30 Friday, July 10 2009 After 3:30 Monday, July 13 2009 Before 8:30 Wednesday, July 15, 2009 Before 8:30
In this example, I have 2 trips made on a single day; 1 instance of trips made 3 days in a row; 1 instance of trips made 2 days in a row (Friday, and then again on Monday since we don't work weekends) and 1 instance of 1 trip made on an isolated day. Additionally, we have 4 trips made before 8:30; 2 trips between 8:30 and 3:30 and 1 trip made after 3:30. Columns for a single job could range to 30 or 40 trips or more, and we have 6-12 jobs running at any one time, so I could be looking at data for all of those jobs once a month if I can figure out how to make it easy to do. I have figured out how to count up the time of day trips (but included the example here for a fuller picture of what I'm trying to do) but cannot figure out how to count the number of 2,3,4,5 etc.-day-in-a-row trips that are being made.
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like;
1 1 1 0 1 0 0 0 1 0 0 1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
I am preparing a attendance sheet. I am using 1 & 0 for present(=1) & absent(=0). I want to find out if a student has been absent for three consecutive days and if there is three consecutive 0 then the formula should return the value 0 ( the student gets 0 if he is absent for 3 consecutive days ) otherwise it should add all the 1s in the row. i.e
I have an excel spreadsheet to record employee holiday and sickness figures.
It is set on as a grid e.g. column A stores all the dates and then employee names are used as column header.
One of the triggers I need to use is where, an employee has been absent 28 consecutive days. When an employee is absent I simply enter 'ABSENT' against there name.
Countif will count the number of time absent appears in the column however I need it to only recognise it if it is only 28 days in a row.
I need a formula that will calculate the most recent consecutive years.
The actual data has multiple ID# and the consecutive years need to be matched to the ID#
So far I have =COUNTIFS(ID_NUMBER, A2, EXPIRATION_DATE, ?????)
A friend of mine suggested that I count backwards from the most recent date and stop counting when there is a break of one year. That is where I get stumped. It doesn't need to be a year day and date, I just need to compare the YEAR portion of the date.
The example data is provided below the consecutive year value on the sheet was calculated by hand and is the correct value that needs to be returned.
ID # Transaction Date Transaction Type Registration Package Amount Paid Expiration Date Years Paid Inspected Consecutive Years 57 8/8/1996 NEW SHIRT+DISC $15.00 8/8/1997 8 Y 3 57 9/3/1997 RENEW DISC $15.00 8/8/1998 8 Y 3 57 9/8/1998 RENEW SHIRT $15.00 8/8/1999 8 Y 3 57 6/13/2001 RENEW SHIRT $15.00 6/13/2002 8 Y 3 57 5/6/2005 RENEW DISC $15.00 5/6/2006 8 Y 3 57 5/12/2009 RENEW DISC $15.00 5/12/2010 8 Y 3 57 3/7/2010 RENEW DISC $15.00 5/12/2011 8 Y 3 57 1/4/2011 RENEW SHIRT $20.00 5/12/2012 8 Y 3
I have a user form with 125 checkboxes and if checked would like them to display one message box containing a specific message for that the checkbox slection, but all messages in one message box.
I have one command button that will list each message box individually, using code like this - If CheckBox12 = True And CheckBox5 = True Then MsgBox "You would need to see your manager"
Each message box individually is time consuming and it would be better if they all displayed in one box. Of the 125 check box options, 7 or 8 will only be selected at any given time.
I need an equation that will return a 1 in column F if data is available (cell is populated) in 2013 or last two years (2012 & 2013) or last three years (2011 & 2012 & 2013) or last four years or last five years. A zero counts as data.
