Material Year/MonthHeight in cm
1000000006200902114.00
1000000006200902120.00
1000000006200902110.00
1000000007200903107.00
1000000008200901115.00
1000000008200901111.00
1000000008200903117.00
and over about 1000 rows.
On sheet 2, i have list of material numbers (about 60 in total)
what i need is a formula that will lookup each material number in the long list
and give me the average height for a particular month.
i.e. in example material 1000000006 average for period 200902
= (114+120+110)/3 =114.67
so i'd end up with on sheet 2 columns A-F
with headings below with relevant formula for each month.
Material-Jan-Feb-Mar-Apr_may
I have faced with a problem with Excel when I wanted to find the average for a stock price.
I have 2 columns: 1st Column: Dates from 1st May 2007 till 23th December 2008 (Rows:A3:A403) 2nd Column: Stock prices from 1st May 2007 till 23th December 2008 (Rows:B3:B403)
I have a report that has thirteen tabs. Tab one is Jan; two, Feb; three, Mar, etc. The last tab is where I'm consolidating all the data into a report.
In Row 2 of Jan-Dec are the days of the week. In Row 30 I have the data.
I am figuring the average of each day of the week for each month as well as a total average year-to-date.
Here is my formula for Jan and Feb: =SUMIF(JAN!$2:$2,"MON",JAN!$30:$30)/COUNTIF(JAN!$2:$2,"MON") =SUMIF(FEB!$2:$2,"MON",FEB!$30:$30)/COUNTIF(FEB!$2:$2,"MON")
Works great after the month is over - but now I'm in March and the formula is adding all data form row 30 that falls under MON and dividing my all MONs even if it does not have data.
How do I nest in the COUNTIF statement to only count MON IF there is data in row 30.
I need to add the total of staffs hours worked for one day, but the problem is that I don't recieve the data as hours but as symbols(letters of the alphabet) representing time worked. Eg "A" is 3.5 hours, "B" is 4hours "C" is 4.5 hours ect, ect.
In the example the top table is a one month time sheet for each staff and there working shifts. The bottom table is the part that I need a formula for. I need a total for each symbol for each day so I can total the hours at the bottom where it says total hours. I have given an example on how the bottom table should look when the formula is completed.
For my business, I am tracking how many sales I am doing per hour. That one was easy. NOW I need to track the average number of customers that I have for each day of the week, Sunday-Saturday. I need for one page to look at another page and, every time the word 'Sunday' is there, take that row of hourly jobs and average them with every other 'Sunday' and give me the total average number of customers on each Sunday, Monday, etc.
Attached is my worksheet for review. If you look at the 'Tracking' and 'Balances' tabs, you'll get the idea......
I have 2 columns of data (col A is time, col B is a value), in a range A4:B1000 (can be less, but the biggest the range will get is to B1000). Example:
Im looking for a way in VBA to look for a time entered in cell C1 (example 10:38:00), find that in column A, then return the average of values in column B that are offset 271 - 300 rows. This is entered in D1. So in my example, 10:38:00 is in cell A9, return the average of B280:B310.
For those of you that love difficult ones, here's one..
I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...
So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...
I've tried everything from sumproduct to vlookups and can't seem to get it right...
I have a table with average temperatures(120,125,130...220) on the vertical axis and equipment sizes across the horizontal access (size 1,2,3...11). At the intersections is the amount of heat given off the equipment. This table is used as a reference for another sheet in the same workbook.
Basically what the sheet is for is to look up the average temperature of a piece of equipment (given the size and exact heat given off). In order to do this, I need to interpolate using the 2 closest energy values and their associated temperatures. There's a better explanation in the file and it makes for sense when you're looking at it. Attached is the table.
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv) If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.
I was going to create a summary page and reference column d.
The following just gives me the min, I want the min average if possible:
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.
so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G: VAT payment HMRC payment Pay VAT
I have a table on the side that shows: Column Y Column Z VATHMRC HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age ------------------- Dog - Rover - 3 Goldfish - Tom - 1 Gerbil - Chewit - 4
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".
My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
