Count No Of Unique Months For Finding Average
Mar 11, 2014
I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.
I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.
For example, if the HC per month would be:
May 2013 - 12
June 2013 - 10
July 2013 - none
August - 5
in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?
Month
Type
L/H
Prod type
HC
[Code].....
View 9 Replies
ADVERTISEMENT
Oct 12, 2009
I'm scrambling my head to put this together. Could somebody show me how this is done?
I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08.
I am able to count the months using the datedif function, is there a match or other function that i need to use?
View 9 Replies
View Related
Jul 28, 2009
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment.
Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes.
I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.
View 3 Replies
View Related
Dec 23, 2013
finding a formula to calculate the number of months between two dates. I've attached an Excel spreadsheet with sample data. Some are in the future, and some are in the past.
Excel Help.xlsx
View 9 Replies
View Related
Jan 22, 2014
I am trying to update our sickness spreadsheet for work to make it a little more 'intuitive' and less cumbersome to use. There are dates (by day) accross the top, with staff names below. If someone is off sick, the manager writes the number of hours lost to sick in that day, under the correct day, then uses back color / font color to show its sick.
Our work uses a 3 periods of absence or more over a rolling 12 months and the staff are issues with a warning, same with over 8 days off sick. If they are off for longer than 14 days they are long term sick.
I would like excel to show whether each staff member has met any of those criteria in the last 12 months by either highlighting their name or putting text into an adjacent cell.
I have tried using offset, named ranges and anything else I could think of to automatically increase the range +1 day very day, but I cant !
Attached File : plan of staff for 2014.xlsx
View 7 Replies
View Related
Mar 7, 2009
The formula works but under very strict conditions. The spreadsheet has columns for all 12 months, a yearly total and an average column. The “If – Then” is for the average column for entire year. No matter what the month it will calculate the average using the appropriate number of months to divide by, now here’s the catch; each previous month has to have a number greater than “0” and luckily I have but if a previous month has a "0" it blows the formula out of the water. I had to split the into IF-Then into two sections because of the embedding limitations so when I have a lot of items that I need to do this for you can see I am having to reproduce this formula multiple times and tracking gets a little confusing. Example......
View 3 Replies
View Related
Feb 13, 2014
Now I'm trying to do 12-month rolling average for my company data?
View 1 Replies
View Related
Oct 7, 2008
how to calculate a six month rolling average for hours in AD column and AE column, this average would be entered in to AF1. the date column is AC the date is not consecutive it does miss days out.
View 14 Replies
View Related
Oct 14, 2009
I'm trying to make a formula that will calculate the average of the previous 12months. The goal is to tie the formula to a reference cell that contains a date. Each time the date is changed by a user the calculation will be updated accordingly. Here is the CSE formula that I thought would work:
{=AVERAGE(IF($B$5:$B$53=B$2,OFFSET($C$5,(COUNT(C5:$C$53)-1),0,12,1)))}
I've also attached a sample file to illustrate the problem. The 'range' portion ($C$5,(COUNT(C5:$C$53)-1)[/b]of the Offset function was setup simply to get the 12 months which preceeded the reference date.
View 4 Replies
View Related
Jul 9, 2012
I wrote code that works just fine, however, I realized there were parts of the data I was using that had 0's in it for holiday.
Bottom line, this is code that calculated the weighted average of prices for 12 months (12 rows across) and Drows across. I'm trying to use the "do while is empty" approach but can't get the syntax to work. Here's the code
Code:
For r = 1 To drows
Totwavg = 0
Tothours = 0
priceavg = 0
For n = 1 To ncontracts
[Code] .....
View 1 Replies
View Related
Jan 7, 2014
In the file attached there is only the data to look at, though file is little bit lager.
Now in cell G10 I've got the stock forecast in 3 months time, this stock forecast is calculated from a date rounded to the closest month (according to my criteria), then the formula looks at the reference number cell D1 to work out the formula. All the steps are in the file.
In order to be more accurate my boss and I want to use the exact date, so that delete all the round month process. My intention is to use the day of the date and then make a fraction of the months to calculate the average. Taking into account that a month have as an average of 30.4166 days.
In the file I did an example, to clarify my messy explanation.
Cells B2, C2, D2 won't be needed in order to do so.
Please have a look at the file. At the end my intention is to to such a formula or a similar one. The formula should be an automatic one when typing dates in cell A10
The result of it is in row 31 and 32.
trial.xlsx
View 1 Replies
View Related
Aug 24, 2009
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies
View Related
Jan 4, 2006
Is there a way to count the number of times an entree is entered in a spreadsheet.
example In row c i have dates entered
11/1
11/5
12/6
12/9
11/3
etc.
Is there a way to count the number of times that the month of dec was entered.
View 14 Replies
View Related
Jun 11, 2013
What I am trying to do is take the dates in Column B and count each date as a month, and put those totals into the January through December boxes. I've put in the amounts that should be shown with the data I provided.
View 2 Replies
View Related
Sep 26, 2013
I have a spreadsheet that I am using for capacity planning. We want to automatically figure out which months have 4 weeks and which months have 5 weeks.
The weeks in a month are defined by work week (Mon to Fri), and if the work week has 3 or more days, it is considered a week of that month, if it has two or less days, it is not counted as a week during that month (it will be counted part of the following month).
ie: April 2014 would be a 5 week month, May 2014 would be a 4 week month and June would be a 4 week month.
Apr-14
May-14
Jun-14
Jul-14
Aug-14
Sep-14
Oct-14
Nov-14
Dec-14
Jan-15
Feb-15
Mar-15
[code].....
View 9 Replies
View Related
Nov 28, 2013
I have a column of dates, and wanted to count how many of them are within 3 months of todays date.
I wrote this obvious formula, or so I thought, as it displays zero results, when here should be loads.
=COUNTIF(A:A,>(TODAY()-90)")
View 3 Replies
View Related
May 15, 2009
The below formula is a small piece of a formula but it's where it's causing the error to occur. When I'm trying to count the months within the SUMPRODUCT formula it works fine, but when there is a "TOTAL" it's returning an error.
AB11/04/2009#VALUE!22/04/2009 33/04/2009 44/04/2009 51/05/2009 62/05/2009 73/05/2009 8TOTAL 95/04/2009
Spreadsheet FormulasCellFormulaB1=SUMPRODUCT(--(MONTH(A1:A9)=4))
View 9 Replies
View Related
Mar 24, 2007
Sub maxTemperature()
Sheet6.Select
Dim myrange As Range
Range("b1").Select
myrange = ActiveCell. CurrentRegion
cellcount = myrange.Count
imax = ActiveCell.Value
i = 2
Do
Do While i < cellcount
If Cells(i, 2) > imax Then imax = Cells = Cells(i, 2).Value
i = i + 1
Loop
Range("a25").Value = imax
isum = 0
For i = 1 To cellcount
isum = isum + Cells(i, 2)
Next cellcount
ave = isum / cellcount
Range("a26").Value = ave
End Sub
View 8 Replies
View Related
Mar 20, 2014
I have a problem again with one of Date Dif function " =DATEDIF(I5|J5|"m") " in attached file i have this function applied to calculate number of months in between two given dates but it seems this function is not working properly or i am missing some info in this to make it run properly. How to fix this function or give me a proper one which can solve my problem to calculate accurate months between these dates in attached file...
Excel Date problem.xlsx‎
View 14 Replies
View Related
Mar 27, 2014
Find attached , expected result in on w2 needs to be 9 that I have punched manually : Team(A)01.xls‎
View 14 Replies
View Related
Oct 31, 2012
i have first date in cell a1 and second date in cell b1. I want formula to to how many months are in between these two dates,, for example
first date: 1 jan 12
swcond date: 5 apr 12
result: 4
View 9 Replies
View Related
Jan 22, 2007
Attached is the spreadsheet where I was trying in to accomplish the following.
1. I have two date columns (From date and To date) eg. 1-Jan-07 to 31-Dec-07.
2. I also have other columns having month-yyyy. eg. March-07, Aug-08 etc.
3. I need to compare Mar-07, Aug-08 etc. with From and To date. If monh-yy falls within these dates, I should populate "1" in that cell.
View 3 Replies
View Related
Mar 10, 2009
I have 2 rows of data
1. title
2. value
eg
Row 1: Title1, Title2, Title1, Title4, Title2, Title1
Row 2: 2 , 3 , 0 , 1 , 2 , 6
In the above I am looking for the average of Title1, where the value is greater than 0. There are 3 occurances of Title1, but only 2 have values. The answer i am after is Sum is 8, average is 4 (ie 8/2 and not 8/3)
View 3 Replies
View Related
Nov 20, 2009
I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.
View 7 Replies
View Related
Jan 2, 2009
I’m using the following formula:
SUMPRODUCT(--(Issue="Yes"),--(Month=Aug),--(Building=$A$9))
To find the number of instances where there was an issue in a certain building during a certain month.
What I can’t figure out is how to find the Average number of such instances for the past 3 months, the last 6 months & the trailing twelve months. If I change the formula to:
SUMPRODUCT(--(Issue="Yes"),--(Month>April),--(Building=$A$9))
I get the correct sum for the months of May, June, July & Aug but I need an average number of instances per month for the last three, last 6 and TTM but not to include the current month.
View 9 Replies
View Related
Mar 18, 2013
I'm after a formula to find the smallest unique value in a range
For example..
1
1
2
3
Small(Range,1) = 1
Small(Range,2) = 1
But i need the second row to show '2' as the result.
View 7 Replies
View Related
Aug 8, 2014
I want a formula that counts all days of a month "listed in a column" but excluding sunday.
View 14 Replies
View Related
Jul 30, 2014
I have a number of rankings, all from different spreadsheets that I wish to combine and average out.
Thus far I have pulled all the relevant worksheets into 1 spreadsheet. The issue I'm having is that each worksheet contains slightly different names.
So as an example, Sheet1 may have 4 columns: Bob Hope | 4 | SE | PM |
Sheet 2 may have 2 columns: | Bob Hope, PM, SE | 8 |
Sheet 3 may have 3 columns: | Bob Hope, SE | PM | 9 |
What I need to do is have a master sheet which just pulls through the name and then the average of the scores, i.e.: | Bob Hope | 7 |
I have approximately 400 people that I need to do this with.
View 3 Replies
View Related
Jul 9, 2008
I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.
I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.
View 11 Replies
View Related
Jun 1, 2009
I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average dosen't contains all integers
eg if range is A1:A10
then data is like
79
80
98
TBA
98
TBA
TBA
N/A
N/A
N/A
now ave for this range can't be calculated directly as many values are strings
what i need is
using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value
here's da avg dunction which i had used in macro
View 6 Replies
View Related
