Finding an average age from a list of dates. This is just a small sample:
7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013
I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.
Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
[code].....
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
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)
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 RelatedI’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.
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.
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.
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
I'm trying to find the average for a person with scores in column 4 on 3 separate sheets. The following function returns #VALUE.. Because the person may be in a different row on each sheet, I'm thinking I need to use the VLOOKUP function.... Also, as an aside, the person may have a zero on one sheet that would affect the average score...
=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0))
I have to find the average of a set of data with a lot of different criteria. I think an array formula would work wonderfully but I just cannot seem to get it.
My example: I have a large set of data that I must get multiple averages from that fall within differing sets of criteria. For my example we'll just say that I have to find the average of the number column that occurs on or after Jan-12 (January 2012) and is type "p1" or one that has no date and is type "p1" and to not average numbers that are equal to 0. So logically it would look like this: If date
I am trying to average PT ScoreS from multiple groups based off of different criteria. Please see below tables:
KEY:
MS = Class CO = Company PLT = Platoon SQD = Squad
A/1 = Alpha Company, 1st Platoon (Example of Company and Platoon PT Score Average)
A/1/2 = Alpha Comapny, 1st Platoon, 1st Squad (Example of Company, Platoon and Squad PT Score Average)
F14 is the average of all PT Scores
A
B
C
D
E
F
1
NAME
MS
CO
PLT
SQD
PT SCORE
[Code] ....
I need to find the equations of different groups of PT Scores. I want the averages/equations to go below the different groups.
A
B
C
D
E
F
G
H
I
J
K
1
MS 1
MS 2
A
A/1
A/2
A/1/1
A/1/2
A/1/3
A/2/1
A/2/2
A/2/3
[Code] .......
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].....
I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.
10
32.4
11
38.6
12.5
23
[Code] .........
I have 5 years of data in 2 columns:
Col A. Col B.
8/2/2004 Value 1
to
7/31/2009 Value n
I have a table set up as follows
1 2 3 4 5 6 7 8 9 10 11 12
2004
2005
2006
2007
2008
2009
I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table...
I tried the following to no avail... I'm just getting a zero value:
=AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))
I am currently using Excel 2003. I have a worksheet with two tabs.
First tab has a list of bank Names. Second list has Bank Names and balances.
I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.
Please find the attached MS Excel 2010 file <average set.xlsx>.
There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.
Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.
The Column H and Column I also same as like above , but here negative set of numbers starts first.
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies View RelatedI have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list
View 8 Replies View RelatedI have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View Relatedfinding 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
I was wondering is there a way to find all the dates in column A and if they are two or less days then the system date then change the font to bold?
I have to detect all staff members with the passport expiration dates between two given dates, i.e.
A 12/4/2012
B 5/3/2012
C 7/3/2012
D 6/3/2012
E 9/12/2012
F 12/11/2012
G 11/16/2012
H 10/12/2012
etc
So, I need to have excel detect all staff members with the expiration dates between let's say 9/1/2012 and 12/31/2012.
I have a problem on matching up two sets of data. One set has a start date/time, and an end date/time. The second set has a single date/time.
Set 1:
Process, Start Date/Time (dd/mm/tt hh:mm) and end Date/Time
e.g
Process 1, 01/01/2009 12:45, 01/01/2009 12:56
Process 2, 24/01/2009 13:45, 24/01/2009 14:02
Process 3, 24/01/2009 14:38, 24/01/2009 14:43
Process 1, 01/02/2009 12:55, 01/02/2009 12:56
Process 2, 08/02/2009 12:35, 08/02/2009 15:21
Set 2:
Event, Date/Time
e.g.
Event 1, 01/01/2009 12:55..................
I have an excel sheet which contains date in a cell .I need to find the date in sheet and keep it in a variable. I came across this code which finds dates but it prompts the user to enter a value and then searches.But i need the macro to run automatically and find any date present in the sheet without any prompt for the user.
Sub FindDate()
Dim strdate As String
Dim rCell As Range
Dim lReply As Long
strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _
Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)
If strdate = "False" Then Exit Sub
strdate = Format(strdate, "Short Date")
On Error Resume Next
Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
On Error Goto 0
If rCell Is Nothing Then
lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then Run "FindDate":
End If
End Sub
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
I have been trying to create a formula (with little success) to calcuate an average figure between two dates i.e.
Column A Column B
01/12/06 40
02/12/06 39.5
03/12/06 39.9
04/12/06 41
I want the average of 01/12/06 and 04/12/06 (or any 2 dates), I can write a formula to pull in the 40 and 41 and have an average of 40.5 but I need it to calculate the average of all values within the range so the end result is 40.1.
I am having difficulty finding the difference between two times and dates in Excel. I have researched this on Google and tried several formulas but none have worked for me. The format the data is currently in can be seen below:
A1: 1/3/2014 11:00:02.230 B1: 2/7/2014 08:42:37.637
A2: 2/12/2014 07:51:58.663 B2: 2/13/2014 05:36:31.893
(Note all times are military time format)
I want to display the difference in C1 in a format along the lines of "dd hh:mm". The data for seconds and fractions of a second is included in the source data but is not needed in the final results.
If the date and time falls on the same day, I have no trouble doing a simple "B3-A3" to determine the answer. For example:
A3: 2/5/2014 09:56:06.273 B3: 2/5/2014 10:39:51.34
Gives me an answer of: 0:43
Trying to do the same for A1 and B1 gives me this: "03 21:42" when I format it to "dd hh:mm".
Is there a formula that would give me the correct answer in both of these cases?
I have a workbook with two sheets.
Sheet1 contains details of all of the jobs I do.
Sheet2 is my customer list.
Customers appear only once in sheet2, but may appear mutiple times in sheet1.
In sheet1 I have customer name, lead date, job date et al.
In sheet2 I have customer name, first date, last date et al
I am trying to populate first and last date in sheet2 from the lead date and job date in sheet1.
Sheet1 has a range of 500 jobs
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.