VBA Macro Code For Calculating Averages Those Which Have More Than One Value?
May 2, 2014
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
16-1804/11/201416:29:2616503.9902
04/11/201417:52:4917390.0293
18-2004/11/201419:52:5517204.8086
Now I need 16503.9902 and 17390.0293 need to get averaged and placed in one cell like,
16-1804/11/201416:29:2616947.00975
18-2004/11/201419:52:5517204.8086
View 14 Replies
ADVERTISEMENT
Feb 19, 2008
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.
Is there code that would allow me to do so.
I attached a sample file to view.
View 9 Replies
View Related
Jun 7, 2013
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:
=IF(ISNUMBER(FIND("CM",D3)),SUM(D11,D12,D13,D14,D18,D19,D20,D23,D24,D25,D26,D27,D28,D29,D30),0)
+IF(ISNUMBER(FIND("V",D3)),SUM(D33,D34,D35,D38),0)
But if I use the same for the average scores is works fine until a person covers two duties:
=IF(ISNUMBER(FIND("CM",D3)),AVERAGE(D8:D10,D12:D17,D19:D22,D24:D30),0)
+IF(ISNUMBER(FIND("V",D3)),AVERAGE(D31:D32,D34:D37),0)
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.
View 1 Replies
View Related
Oct 1, 2013
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.
View 2 Replies
View Related
Aug 9, 2006
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.
View 4 Replies
View Related
May 10, 2006
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...
View 3 Replies
View Related
Jan 20, 2014
generating a formula that takes the weekly values in a quarter and uses them to generate a quarterly average. Quarters are split up as follows.
Q#1: 09-11 (First week of September-last week of November)
(Used to generate price for January)
Q#2: 12-02 (First week-Last week)
Q#3: 03-05 (First week-Last week)
Q#4: 06-08 (First week-Last week)
I've attached an excel sheet with some dummy data.
View 3 Replies
View Related
Aug 27, 2007
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
Goal 99%
View 9 Replies
View Related
Apr 28, 2008
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.
View 9 Replies
View Related
Jan 19, 2007
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")
View 6 Replies
View Related
Mar 5, 2014
I need to calculate SUM and AVERAGE of rainfall for each and every year separately and must be displayed separately in a separate column. For your easy understanding, I have done manually and attached the excel sheet.
View 6 Replies
View Related
Apr 9, 2014
I have a bunch of workbooks in a folder and I'm basically trying to take the average of the same specific range for each file. I have somewhat of a method for doing this where I separate it into several steps and grab bits of code for each step (there's descriptions on the code). How I can improve or streamline it?
Code:
'STEP 1
'run this first to combine multiple files/workbooks into one file
'Change MyPath to the folder location
Sub Merge2MultiSheets()
[Code]....
View 1 Replies
View Related
Mar 6, 2014
I'm a novice when it comes to macros but was wondering would it be possible to develop one to get continuous averages for a set of data. It's a very big data set with my first observation starting at A1 and last at over A19,000. The data is broken up half hourly so the first average would be from A1:A48 the second from A49:A96 and so on. Can a macro be created to repeat this format rather than me having to continuously highlight the different figures to get an average?
View 9 Replies
View Related
Mar 4, 2007
What I’m trying to do is based on a character level in cell “G1” I’m looking for “Z1” to show the experience need for the next level. For example:
Level=Experience
1=0
2=1000
3=3000
4=6000
5=10000
6=15000
7=21000
8=28000
9=36000
10=45000
11=etc…
Experience is calculated by 1000 x Previous Level + Previous Experience.
In this above, to reach fifth level experience is calculated as (4*1000+6000).
I know this can be done with a formula, but can a macro or VBA compute this also? For example:
If G1 indicates that a character is at level 5, I want Z1 to show the experience need for level 6. This means I need code to calculate experience from level 1 to level 6 like this:
IF(LEVEL>1,((1*1000+0)+(2*1000+CURRENT EXPERIENCE)+(3*1000+ CURRENT EXPERIENCE)+(4*1000+CURRENT EXPERIENCE)+(5*1000+CURRENT EXPERIENCE)),0) RESULT=15000 for LEVEL=6
That’s the formula equivalent of what I’m looking for code to do. Can this be done?
View 9 Replies
View Related
Jul 23, 2014
I have months ( 1 to 12 ). Every month, a set of tasks need to be executed which takes "x" number of hours.
So if I have 1 machine working 3 hour in the month 1, the total time spent is 3 hours. Fairly simple right !
Say on month 2, a set of tasks take 4 hours. total time that my first machine takes is 4 hours. But i want to introduce 3 new machines in this month, which will execute the first month's tasks. So total time spent here is ( 3*3 + 4*1) = 13 On month 3, task time is 6 hours. And I am introducing 2 new machines.
So total time is 2*3 ( time taken for 2 new machines to perform first month's tasks ) + 3 * 4 ( 3 new machines introduced last month will now execute second months task) + 6 * 1 ( time the first machine will spend on month 3 s tasks ) = 24
View 2 Replies
View Related
Jul 8, 2009
In my code, I'm calculating a very simple ratio and have the result displayed in a message box. However, I want to have this value only show 1 or 2 significant figures (e.g. if I take 9 / 7, I want to show 1.3 and not 1.28571).
View 2 Replies
View Related
Jan 22, 2013
I am having a big prob with the speed of this macro:
Code:
Sub temp3()
For i = 1 To 63Sheets("Data to convert").Range("i1").Value = iFor j = 1 To 6Sheets("converter").Range("k1").Value = j
Do Until Application.CalculationState = xlDoneDoEventsLoop
If Sheets("converter").Range("m38").Value 0 Then MsgBox "ERROR"NextNext
End Sub
When I manually loop through these values it calculates within a second but the macro is getting caught on the while calculating loop and it just seems to never get out if it. I have tried setting calc to manual and then putting a calc command in but it still catches on the loop. I have used this while calculating code many times and have never had an issue.
View 7 Replies
View Related
Apr 16, 2014
Excel function or macros for calculating the average of waterSD column where the TT column is less than or equal to 100(red markings). functions like IF TT IS LESS THAN OR EQUAL TO 100, THEN CALCULATE AVERAGE OF WATERSD....
I had done manually on the right side in the attached excel sheet. only those yellow markings.
Because already I have some macro which do this process but it is not accounting for the TT column less than 100. it starts from 200 TT values. but i need to include 100 TT also. That's is where now we have yellow markings. I will provide you the macro code if you can edit that where it will start calculating form 100TT value, it will be really great. The code is,
[Code] .....
Attached File : average for watersd OF 100TT.xlsx‎
View 3 Replies
View Related
Apr 12, 2014
I'm trying to convert the values entered by users into specific cells into a specific percentage. When I use this macro in Excel 2010, I can enter the value to be calculated directly into the cell and the macro runs automatically. When I open the file in Excel 2007, the macro does not calculate properly unless the value to be calculated is entered into the formula bar.
For example, in Excel 2007, if I enter '30' in cell E11, it should come out as '20%'. Instead it comes out as '0.2%' unless I enter '30' into the formula bar, which then calculates properly. It is far more efficient to be able to enter the value directly into the cell.
[Code] .....
View 2 Replies
View Related
Feb 17, 2014
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek()
If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
[Code]....
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
View 2 Replies
View Related
Jun 5, 2007
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.
View 12 Replies
View Related
Jan 13, 2009
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.
View 2 Replies
View Related
Feb 2, 2009
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.
View 2 Replies
View Related
Mar 8, 2013
I have a spread sheet with col's that I want to average their totals, but would like them to be rounded up or down.
View 3 Replies
View Related
Apr 7, 2013
I am using the formula =AVERAGE('Year 13'!BV:BV) to work out the average value in column BV.
However, I would like the value to change if I filter one of the other columns, is there any way to do this?
View 2 Replies
View Related
Jul 13, 2007
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
View 10 Replies
View Related
Jan 6, 2006
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)))
View 10 Replies
View Related
Jan 31, 2012
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
View 2 Replies
View Related
Oct 2, 2006
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.
View 9 Replies
View Related
Mar 1, 2007
In the following cells I have an average:
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
View 9 Replies
View Related
