I have a sheet with traffic-light conditions: g (green), a (amber) and r (red). So, for instance, I have a row with the following traffic-lights:
agrag
Now I want to do some calculations with these traffic-light conditions so I create a v-lookup table:
g 5
a 4
r 0
This would mean that the row would now read:
45045
So far so good. Now I want the average of this row. Usually this is easy, I just do a =AVERAGE(A1:E1) which would give me a result of 3.6. But because there a not actual values but text values which are translated by a vlookup So I would need to do a
=AVERAGE(VLOOKUP(A1;H1:I3;2;FALSE);VLOOKUP(B1;H1:I3;2;FALSE);VLOOKUP(C1;H1:I3;2;FALSE);VLOOKUP(D1;H1 :I3;2;FALSE);VLOOKUP(E1;H1:I3;2;FALSE))
which gives me the expected result of 3.6. Although this works it quickly becomes rather messy when I have thousands of rows that contain hundreds of traffic-light conditions for each row.
So, and here is my question, why can't I specify a range. So something like:
AVERAGE(VLOOKUP(A2;H1:I3;2;FALSE);VLOOKUP(E2;H1:I3;2;FALSE)). If I try this I get an error.
I want to calculate the average of a range...if it meets a certain text criteria.
For example, if the product is a "Course", then take the average of pages all those courses together.
ProductNumber of PagesExam316Course46Exam232Course32Exam245Course53Exam155Course246Exam118Course154Exam82Course434Exam80Average # of Pages for Courses = Average # of Pages for Exams =
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?
What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
I have an issue using the average formula (example attached). I need to calculate the average grade of a group of students. I have used VLOOKUP to convert grades to numbers. Then averaged these results and converted back. It appears to work for most results, but some are not working. I have messed about with cell formatting etc, but cannot seem to figure out why its not working for some rows but does for other rows?
i have data on excel which has the following fields:
Invoice No,Inv.Date,Month,Customer,Part Number,Description,Qty.I want my final pivot table to have the following results for every part no. and description
Total monthly sales
total year sales
and for each model sale calculated as sale quantity / week
I have tried the following way but it did not work:
just next to the pivot table under the heading"AVG" i used the following formula to get the sale/week on cell o6=grand total sale /(applicable no. of weeks).and when i am copying the formula to the cells lying below the formula is being applied as that of the first cell so getting the same result for all the rows.is there a way to calculate sale/ wk within the same table
I need to find the average of a list of text cells. My list is in column A, and in column B I've assigned each of the text cells with a numeric value. I'm then doing the average on Column B which will return a numeric value which will represent one of the text values in column A.
What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.
(some examples from the file) NR2 NR DATE 100707987121951 100702347121960 100707750121960 100707721121960 100702422121960
[code]....
So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.
I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)
1. Vlookup Regions in Sheet2 (eg. AP-Asia/Pasific) 2. Check which row they are in 3. then take the average of next 6 cells (AVERAGE 1,310, 6,744 etc..)
very basic Excel user (using Excel 2007). Trying to see how I can use the VLookup function from multiple worksheets to get an average. I know it can be done, but just not sure how to go about doing that. My spreadsheet is attached - basically what I want to do is to get an average for the individuals listed in the "Consensus" tab for the figures that appear in Worksheets "1", "2" and "3".
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
In sheet 2 b2, I want to calculate the average of the values in the last column of sheet 1. So if the last column is D then all the figures in D are added up and an average given. But if the last column is M then it gives me the average of that column.
how to do a weighted average with a text criteria. I have 3 columns and many rows. The first column has either "buy" or "sell". The thing is I need to find the weighted average based on the product of the other 2 columns for buy and sell separately e.g.:...............
I have a spreadsheet with dates running down column J, and a number running down column K. I want to average the numbers in column K for all the rows that are less than 30 days old.
I have a formula for determining if a date is less than a month old: =IF(DATEDIF(J2,TODAY(),"d")<31,TRUE,FALSE)
I've attached a sample worksheet. I have a series of time values in ascending order (column A) and then 5 sets of data that correspond to the time values. I wish to be able to enter a minimum and maximum time range then selectively average the numbers from the sets of data that fall within the time range, but I can't think of a simple way of doing this.
What could be the syntax (in a code) for averaging a range in col C. the range values are given in E3 and E5. (E3=508 & E5=1200) These values changes each time I open the workbook.
I am writing this application wants me to calculate running averages and Std Dev. I will copy a row of values into a spreadsheet. The next row will be a new average of each column. So far so good. But the next time I calculate the new average of the column, I do not want to count the row that represents the last average I measured. SO, I'd like to format the rows that are average values (i.e. bold) and then when I calculate the next average, exclude any value whose font is bold. Currently I am using the following line of code to calculate average:
Is there an easy way to limit the values used to calculate average to only those values that are NOT bold?
Sub ColAve () Cells(LastRow + 1, ActiveCell.Column).Value = WorksheetFunction.Average(ActiveCell.EntireColumn) End Sub
I have some data, consisting of two columns. The 1st column consists of time values in the form hh:mm:ss, and the 2nd column consists of a pressure reading, taken every couple of seconds. I wish to find the average pressure from the start time, to a 2nd time, then the average pressure from this 2nd time to a 3rd time, then the the av. pres. from this 3rd time to a 4th time, and so on. The time i want to take the average over varies, sometimes 3 minutes (i.e. ~180 pressure readings), sometimes 4, sometimes 5 minutes. I have to take the average over different ranges dozens of times -
I'm trying to get excel to calculate the average time spent on appointments by officers in a small team. Each of them sees people each day, and as we're very busy I'm eager to get the time spent on each appointment down, by giving them a target of the number of people to see each day. At the moment we gather some stats on this, which show how many people are being seen by each officer.
The attached example sheet shows for one week a list of columns with officer's initials at the top, and says how many people were seen by each person each day during that week and then a how many people overall for the week.
I'd like to be able to generate statistics on the amount of time each officer has spent on average with customers, and base this on the fact that people have 360 minutes per day (they do 6 hours of appointments and another hour for admin) to see people.
I think the spreadsheet attached gives a good idea of the system and what I want to do.
It is a database in which each row represents a unique individual.
Columns A - G contains information which is irrelevant for this question Columns H - CQ contains answers to the individual's questions (Yes/No or "-" for N/A) Column CS contains a array formula which determines the % of "Yes" answered only in Question 1 (Columns H to X). The formula finds all "S1*" (within H1 to CQ1) and if the corresponding "S1" has a "Yes" (within eg. row 2 - H to X) it adds it and divides by the total number of "S1" (17 in this example excluding the "-")
So I have the array formula in column "CS" which does the above.
What I want to achieve:
I want to get the average of all the individual rows in column "CS". This is shown in "CS1 (48.32%". I am looking for a way I can achieve this with just one formula - A formula which first calculates the individual %s and averages the %s within the same formula.
