I have a data base with 12 columns of data. I need it to look for one criteria in one row, match it, look for another criteria in another row, match that, and then once those criteria are met, average those rows that met the criteria with the statistics in that row that contains a value.
Such as:
Monica 2 Timed 310 Michele
Tom 3 UnTimed 410 TC
Art 5 Timed 216 TC
Monica 4 Timed 415 Michele
Tom 6 Timed 200 TC
Tom 4 Untimed 216 TC
Art 8 Timed 410 TC
What I need is the formula to search in through the data, look for Tom, then find all the ones that say UnTimed, and then average the number in column 2 that match those criteria.
I have a huge listed of assets - the column i where my info is has vasrious serial numbers.... some are a simple string of letters/numbers and the others look like this for example AG-1234567
I need to somehow pull out the ones that look like that. (AG-1234567). I only need those. Now normally i would just do a filter and pull em out however, this sheet contains 73k worth of items.
I have a big data sheet. I have approximately 2170 rows and 50 columns. In some rows I have a series of consecutive zeros. I need to build a formula that gives me the date ( located in row3) right before the first zero in the series of consecutive 12 zeros.
For instance in row 1340 I have a series of 12 consecutive zeros. The first zero happens in column AC, therefore I need the formula to retrieve cell AD3. however every rows has zeros located in different cells therefore I need a general formula.
I'm using Excel 2007 and I'm looking for a way to extract certain records/rows from a table and to have that data copied to another area of the spreadsheet. I actually want to extract Invoice amounts according to a specific account number so that I eventually end up with all the invoices for a particular client on another tab so that they can be printed out as a kind of client statement.
I know there are ways to filter and then copy the records/rows to another tab but I want it to be automated as far as possible. What I really want to do is to "pull" the records into a new tab by using some kind of formula in the cells where I want the data rather than having to copy it there. I know how to sum data for this kind of thing but what I'm really struggling on here is being able to "Display all the records in a seperate area" first before I eventually add formulas to sum.
I don't mind the records being coppied if the process is automated but don't really want to get into macros as writing macros isn't my strong point.
I've tried using "VLOOKUP" but it will only return values for the first data item it finds (Account number in my case), whereas I really need it to continue to return values for the data item it finds if there are multiple instances of it!
automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
I have a huge list of survey responses which asks people to identify their location and then rate how important 6 facilities in their area is (rating 1 - 4, 4 being most important). So the table has cells are similar to this, except that there are about 5000 responses with about 2500 different locations identified
Location Location Location
[Code].....
I have been able to analyse the average results for the whole dataset by using the following formulas:
=COUNTIF(A2:C4,"camden") Count number of occurrences a location is listed =ROUND(AVERAGE(D2:D4),2) Average response for medical centres =COUNTIF(D2:D4,1) Counts number of times '1' comes up, would repeat '2', '3', and '4' to create a pie graph
But what I would really like to do it be able to count each response for each question by location (for example "Camden"). I've tried both of these but they don't seem to be working.
I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row
Code below
Sub deleterows() Dim i As Integer Dim intCounter As Integer Dim strLocation() As String
p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50 --If there is a (are) number(s) lower than the special number, then I want it to locate the smallest ---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
I would like to create an average function that will take an average of the Column labeled "Gap Time (Hours/Min/Sec)". I only want it to take the average for this for each new start date. These values will change daily so I was hoping that someone may help me write a function that will work when data changes instead of manually taking the average every time data is entered.
I have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using. On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.
So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at. I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.
I have a database of over 10,000 entries. I am trying to get the average time for four specific entries (department, test 1, test 2, test 3). I did an averageifs for the tests individually and calculated the average time individually.
The three tests comprise of the department.
Average of test 1 = 40.8 Average of test 2 = 39.9 Average of test 3 = 94.8
Average of dept using the averageifs is 41.3 Average of dept by taking the average of the values above is 58.8
I am trying to find the average of a cell with a name in that may appear several times in various columns. I then need to find the average of the value against that name in all the columns that it occurs in. Example:
a b c d e f g h i j k
Aldridge Sean 2 3 3 Andrew Rob 4 3 3 Aldridge Sean 3 2 2
[Code] .....
In the above example I want to find the average score for aldridge sean in column b, f and j.
I would like to find the weighted average of the values in the "score" column. Additionally, I would like to only find the weighted average if the student is jack and jane. I have attached my workbook for review.
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)
Ceate a formula for weighted averages according to criteria?
I want to know what was the average price I bought a product at on today's date. The average price needs to be weighted against the quantity of product sold.
I have information I'm trying to evaluate. Average Close time. but I need to evaluate it for each month. Forinstance if my average close time was B. and the close date was c. I would need the average(b:b) but only from fields that had a close date of July in column C. is this possible? I hate to have to break up the data set 12 times. (i'm reporting individually for each month of the year so we can see trends.)
In column A is the staff name, column B is the start date, column C is the end date and column D is the difference.
I need to find the average number days for task completed for each staff for the week. I need a formula that will look for a particular staff name in column A ( which appears many time randomly in column A for each day of the week) and calculate the average days for the total task completed for the week.
i have a excel spreadsheet that contains point of person. Every week a person can or can not compete. That means that there is some days that is blank wouthout points. I want to get the latest 5 points of a person and then out of that 5 i want to get the top 3 point.
I have trouble finding the right formula for a weighted average price. The formula which i used in cell B2 of the attached file doesn't exclude irrelevant numbers. In the formula "= sumproduct(a5:a21;b5:b21)/sum(b5:b21)", i like to exclude cells B8, B9, B15, B19, B20 because the list prices are missing. How can i exclude these irrelevancies in the formula above?
I have a large set of data. I need to be able to search this data routinely using an origin and destination zip code and sum the search results. Is this possible in Excel?
I am trying to create a macro using arrays to calculate average sales from a list of sale amounts that originate from different cities. Each city has its own city code and I want to display the average amount of sales for each city. The attached file is the template that ive created to do this. I am having trouble getting the arrays and loops to work. If anyone can help me out I would greatly appreciate it.CitySales2.xls
I have 4 columns of data, and need to average values that correspond to certain criteria from the other 3 columns. Attached is an example of my data and the desired output. The averages need to come from non-blank cells from Inputs 1 and 2 that match the criteria from the Name column.
