I want to be able to keep an average grade for my students through the course of a year. Work is marked with a grade A - U. I can use VLOOKUP to convert this to a numerical value but I can't work out how to generate an average from these numbers because every week there will be a new unit of work marked.
On the sample spreadsheet I have attached, the problem I have had is trying to get an average of the numerical values in row 7 because the cells without numbers have a VLOOKUP formula in them and mess up the calculation. And as the range of numbers will increase each time I mark a piece of work I can't use a fixed formula to find the average from say 10 numbers...
I'm trying to Calculte the Averages of just the Teachers Salaries in my spreadsheet. If possible i would like to put the average at the end of each of Teachers information.
I have a spreadsheet that we are using as a Skills Matrix for the team. The area that the team member deals with is listed in Row 3. The scores for each person are recorded in Columns D to M. The process steps being scored against are in Rows 8 to 38, and are seperated by the area of responsibility.
I am trying to work out the average scores of each person depending on their area of responsibility. I can work out the totals easily enough using:
This is becuase it adds the 2 averages instead of working out the total average score. Is there an easy way around this?
i.e. For team members who have CM in Row 3 I need an average of certain cells, for team members who have V in Row 3 I need a different average and for those team members with both CM and V I need an overall average.
I have a database with monthly tabs and a summary sheet, on the summary sheet I calculate an average length of stay for each month (tab) but I need to find the yearly average and I don't know if there is a formula that would calculate the true average by using the monthly averages.
The data was taken in 15 min intervals and is organized by date. I have one column with the date and time and another column with the data. I need to find the average for each day. I have almost a years worth of data. Is there any formula I can enter to find the values in a given day and return the average of the values (without having to select the data for each day)? I want to be able to copy the formula down a column with the value per day.
I need vba macro code or excel function to calculate average for some intervals in my excel sheet which have more than one value... I need all those values to be get averaged and placed in one cell...
I have intervals like 0-2, 2-4, 4-6.... 22-24.
In these intervals, few have got more than one values like
Below is my data point for each month. The goal I need to hit is 99%. So I need to figure out what minimum monthly percentage I need for the rest of the year, I will need to reach a goal of 99%, and if I can't reach it, return an error. Lastly, i want to be able next month to go in and fill in the AUG percentage with an absolute number (i.e. 89%) and then I would like the rest of the percentages to automatically update by figuring out the new minimum monthly average given the new value for August. I thought that I might be able to do that if there is a function that says "If cell is a number, leave it alone, if it's a formula, then include that cell in the calculation of the minimum monthly average.
Jan 89% Feb 88% Mar 83% Apr 89% May 90% Jun 86% Jul 82% Aug Sep Oct Nov Dec
I am currently using the following formula to calculate the average weekly demand of a product:
=SUM($G8:$CF8)/COUNTIF($G8:$CF8,">=0"))
It works fine, but sometimes the data I'm analysing doesn't span the full horizon (78 weeks). What I therefore want, is for the COUNTIF part to recognise the last populated Cell in the array, and to use the length of the array up to that point to calculate the average. Put another way, if I only have 52 weeks of data (the last populated Cell is $BF8), I want the COUNTIF to calculate based on 52 weeks and not 78 weeks.
I have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")
I made the following code to merge 2 workbooks together.
The code is to be executed when the user has Workbook A opened. (All sheets in workbook KPISWD are supposed to be moved after all worksheets in workbook KPICustomers).
I keep getting a debug error on the code that is supposed to do the actual move and loop until it is finished with all of the sheets in Workbook B.
Code: Dim KPICustomers, KPISWD As String KPICustomers = ActiveWorkbook.Name
Workbooks.Open Filename:= _ "W:FacturatieKPI per periode SWD.xls"
i have done a search on this topic and found many similar answers to many similar questions. All specify using Application.Run "workbookname.xls!macroname".
In my code, the filename has an assigned value as the master code goes out to many secondary files - the user selects the particular one they want at the start of the macro. The variable assigned is called "UpdateKAMsFile".
how do i get the macro called KAMsRandomColour to run in the workbook designated by UpdateKAMsFile?
so to start off im not able to attach things due to security reasons, what i need is either 1 of 2 macros. if its possible, both are related. one possible is as follows: i need it to go through a certain column (say I for example) and look at the information in it, this information can vary from peoples names along with dates and other stuff, i want it to look through these and if anything has a date today and prior i need the cell to be highlighted. problem is sometimes it might have 2 dates, or no dates. it should have todays date, their name, other information, and future date of something. but not everyone does, this is the macro i dont think that can exists.
2nd macro possibility the other macro uses the first sheet, AFTER been highlighted, normally by hand, and takes it to another workbook and puts in in certain spots. so the first sheet has names of everyone in column K. what i need is it to look at column I and if its highlighted take entire row to other book, and put into sheet under the person name in their tab. the second book has a tab for each person (at this time 18 tabs) which can fluxuate, and each tab is the persons first and last name, without spaces. since when i put sums on main page it didnt want to work with the spaces i had to omit them. again im not sure this is possible.
I would like to know if there is a way to copy a spreadsheet and paste it into a new tab on another spreadsheet. We currently download 2 lots of spreadsheets from SAP and would like to create another spreadsheet to act as a 'central' preadsheet. What I would like to happen is that the data from the 2 downloaded spreadsheets will be automatically pasted into 2 new tabs on the central spreadsheet, then run some formatting macros (which I can do fine).
I am working on a spread sheet and am trying to use rolling averages of the last 3 months. I am using worksheets for each month with a final worksheet containing the 3 mo. rolling average. I tried using a formula with OFFSET and it worked fine if all the data was in 1 worksheet, but did not work for multiple worksheets averaging to another worksheet in the same workbook.
I am trying to work out the average volume of our daily downloads. I have used Vlookup to automate this. When there is no data it returns #N/A which is no problem. The problem arises when i try to work out the average for the month or peaks (MAX sum).
I have tried several variations of sumif/if/isna etc etc and am not having much luck. Below is an example of what i am trying to average out: #N/A620#N/A
That is 4 different days download data - I want it to ignore the 0's and #N/A and return the average for the days we actually had a download.
If I have a varying number of figures(say between 4 and 10) which I want the average of the first half of the set and an average of the second half, is there a function which I can use to calculate this? ie if there is 8 numbers in total then I need the average of the first four and last four... but if there is ten figures total then I need the average of the first five and last five.
I am inputing each day the total amount of sales. I am manually doing the averages for past 12 weeks on Monday sheet. I would like when I enter the amount for that week that the cells B84, C84, E84, F84 are automatically updated for the past 12 weeks. I have tried a few different approaches but I keep getting #ref or circular reference
We are trying to get an Average between 2 columns that have the same conditions. We have been using an array formula succesfully on data that is on 1 column and I have trying to incorporate an Offset into the formula with no luck. Here is our latetest attempt, WITHOUT THE OFFSET. The data is in Column P and Column N
=AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650>.01)*('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650<.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650)))
I want a cell that takes each row in column B and subtracts column A from it... (10-5 for row 1). I want it to do that for all rows and then average that number. This would be easy if I were not using a pivot table with a lot of data.
For example: Column A Column B 5 10 2 9 6 8 1 4 6 7 1 1 6 2 6 8
I havea list of values that I need to work averages out for. Unfortunately, sometimes the values will be a zero and these should be ignored.
I can work out the averages easily enough using the average function, but can't quite figure out a way of ignoring the zeros. Sample data would be :- week 1 - 3.42 week 2 - 0 (zero)
Using the standard average function, I get an average of 1.71 when I actually want it to have an average of 3.42.
I would prefer a function for this but would settle for a macro even though it would increase the program run-time.
B6,J6,R6,Z6, and AH6 and a total average on cell AP6
How would I go about making a formula for that. The reason why I ask is because all the info I have seen refers to the cells with the averages need to be in sequence or range and nothing about the cells being seperated. I also tried using the countif method, but same prob, the cells need to be within a horizontal or vertical range
I need to calculate average for last 12 valid entries in a column. I say valid because if there are blanks or zeros in the cell they need to be discounted.
The solution needs to work on percentages as well.
To explain further Col A Col B Jan 2007 1 Feb 2007 5 Mar 2007 5 Apr 2007 0 May 2007 Jun 2007 20 Jul 2007 0 Aug 2007 Sep 2007 10 Oct 2007 0 Nov 2007 10 Dec 2007 18 Jan 2008 8 Feb 2008 6 Mar 2008 0 Apr 2008 May 2008 10 Jun 2008 1
The requirement would be to calculate average from Jun 2008 backwards for 12 months where there aren't zero or blank entries.
Column 1 contains the number of times an ad has shown Column 2 contains the average position of the ad from column 1
Like so,
Ad Impressions.........................Avg Position Google: 14,220,023..........................3.2 Yahoo: 346,656...............................1.2 MSN: 123,234..................................8.9
Total: 14,689,913............................???
Doing the following,
= Average(B2,B3,B4)
This is not the real average as this is directly influenced by the number of ad impressions...
How can i make a real calculation to give me the true total average position of my ads?
Have a five column worksheet with the column headings run column A1 - E1 and there are currently 112 players filling out the data with many more to come
The first one, I do i remove #DIV/0! from cell O7 in the worksheet "My overview"?
The second one, I need to get averages of the QA score for the 3 months from January to March in the worksheet "My Overview" Cell E14 for the corresponding person on each sheet. I understand that there need to be values in place to get an average but is there a way to overcome this?
I created an Excel tool into which really thousands of records are based on weighted averages calculations. And discovered Excel struggling in term of permormance.
The issue is: the user needs to uploads a couple of times in a day new data for further analysis, and the only way to make the Excel spreadsheat humanely usable (performance) was to calculate everything upfront.
To optimize the tool performance, I had to:
- First I made all weighted average calculations upfront when data is uploaded the first time. Leaving formulas in the tool was not viable, since this tool run live analysis, every click would have been a pain of 30 sec at best to re-calculate new conditions,
- Second optimization was to not leave formulas into columns > as soon as a calculation is done, the macro copy/paste values
The formula I chose is sumproduct based weighted averages..
Here is my question:
1. Does it exits specific formulas in Excel to handle nicely large amount of weighted averages to calculate ?
2. Should I consider to use an external application (an Access table, SQL or whatever) to perform those weighted average calculations quickly/quicker.
