Calculate Averages The Integer In First Column Out Across All Rows
Dec 22, 2011
I have a first column, which contains random integers typically with gaps of between 10 to 50 blank cells. The data may have up to 5,000 rows in total.
In the next column along I would like a calculation which averages the integer in the first column out across all rows until another integer is reached in the first column.
E.g. In the first column, the first number is 60, then 9 blank cells till the next entry. In the second column the first 10 cells each contain the number 6 ( = 60/10).
View 1 Replies
ADVERTISEMENT
Oct 6, 2011
I have 3 columns (B,D,F) that have 15 minute data intervals and I need to calculate the hourly averages for each column but here is the catch, I need to do this from July-1-2011 00:00:00 to Sept-31-2011, 24:00:00 (that's 26,217 data points and 6,554 =average() calculations......
IS there syntax I could use that would calculate the average every 4-cells so I could get the hourly average without having to type =average(XX:XX) 6,554 times?
Example of set-up:
29-Sep-11
23:00:00
0.820412164= AVERAGE(B8735:B8738)
0.762574274 =average(D8735:D8738)
84.98389991 =average(F8735:F8738)
29-Sep-11 23:15:00 0.819168591
0.762781167
[Code]...
View 6 Replies
View Related
Jan 29, 2010
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:
Try this formula. .....
Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
View 9 Replies
View Related
Feb 20, 2008
Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.
My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?
Const intCells=9 'actually, this is no constant but a variable
Const i=4 'actually, this is no constant, but a nested for- loop-value
Const intLinesInBetween = 12 ' amount of lines, which separate my values
Dim j As Integer 'simple counter, runs through all cells
Dim rngUpperRow As Range 'Range of the upper row = normalized time vector
Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted
Dim intCells As Integer ' amount of experiments in range............
View 4 Replies
View Related
Dec 31, 2008
On another thread I found a solution to creating a weighted average given a pair of columns of numbers but I have column B consisting of values given as High, Medium and Low and the weighted column C of 1, 2 or 3.
How can I use the formula below to accomplish this, or is this maybe not the right way to created a weighted average?
=SUMPRODUCT(B2:B11,C2:C11)
View 7 Replies
View Related
Aug 21, 2009
Code:.....
The names are not static and can be moved, meaning that John can be 1, 2, 3, or 4 (all depending on how the name is chosen). I want to find the average on where John ranks. In this instance, he would be 2 [(1+3+2)/3].
My question is - How can I create a formula that will look for where John appears in the column (B1) and call the previous cell (A1)? This formula needs to be repeated on each column, added and averaged.
View 5 Replies
View Related
Jan 5, 2012
I have a large sheet of daily river flows and precipitation amounts, over 40+ years.
I want to calculate the average flows in each month of each year (ie 11/1968, 12/1968, 1/1969 etc). Dates are in Col A, flows in Col D.
How to average a particular month over all years like so:
{=AVERAGE(IF(MONTH(A2:A15282)=1,D2:D15282))}
But I don't know how to average per month of each year, or how to make a formula that will allow me to quickly calculate the averages of 100s of months.
View 6 Replies
View Related
Mar 19, 2013
I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.
View 3 Replies
View Related
Mar 20, 2008
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
View 9 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
Apr 22, 2009
I have a problem with a data set that I'm working on. Basically, I've got a data set that was downloaded from a weather station. I need weather data like average temperature, maximum temperature, minimum temperature for each day over a 90 days period. This is simple enough.
The problem, however, is that the weatherstation has been sampling every 5 minutes. In the data sheet, each day is represented by 12*24 rows of data (12 samples per hour times 24 hours). The total dataset across all days is something close to 26000 rows!
Of course, I can spend hours doing all this by manually by average(), min() and max() and then selecting the cells I want it to apply to each time, but the thought of doing so makes me wince. I would have to do it 90 times per column across hundreds of cells each time and I'm after much more than just temperatures.
Is there a less labour intensive way of doing this?
View 12 Replies
View Related
Dec 17, 2012
I set a column width to some integer like 10, and the next time I open the file it has changed to something like 9.89. Is there some configuration that prevents this from happening and leaves my setting alone?
View 4 Replies
View Related
Apr 17, 2007
I have written a macro to record some information to cells on a spreadsheet. Below is my code. As you can see I already have row setup in the code, but I now want to add column as an integer. So that if something were recorded in col m, then it would add 1 to colu and the next information would be added in col n.
Col = 13 (M) to start with.
Dim row As Integer
Dim colu As Integer
If Trim(txt(8, 10, 25)) = Workbooks("Book1").Worksheets(1).Range("K" & row) Then
MyScreen.Area(8, 4, 8, 5).Copy
MyScreen.Area(22, 40, 22, 41).Paste
SendKey "<ENTER>"
If MyScreen.Area(7, 57, 7, 57) = "A" Then
Workbooks("Book1").Worksheets(1).Range(colu & row) = Trim(txt(7, 57, 74)) 'getting my error here.. something wrong with colu
colu = colu + 1
End If
View 9 Replies
View Related
Jun 18, 2012
Essentially, I have a column of integer values which I would like to iterate through. The integers in the column vary from 12 to -12 with the exception of 0, so 24 possible integer values here.
For each possible value, I would like the activecell.offset(0,7) value added to a variable.
So if I iterate from cell A1 to cell A10, each time a value of "1" appears, I would like the cell 7 spaces to the right of the "1" cell added to a "total" variable for "1" e.g.: "totalOne".
The same applies for every value within my range. So if there are three instances of the value "8" between A1 and A10, then each value 7 cells right of those "8" values will be added to a total value for the value 8.
I started to use:
select case range("u4:u150"). value
case 1
Dim value1 = value1 + ActiveCell.offset(0, 7).Value
However, at this point I realised that a switch statement stores the range I have given it as an array, so my 'case 1' cannot work as it is trying to compare an integer with an array.
Finally, if it stores an array, surely it wouldn't be able to get cell.offset values as it would have no references to those locations given that the array is just a group of integers, not actual cell references? Will I have to use lots of "for each" statements? hope not!
View 8 Replies
View Related
Oct 2, 2006
I have multiple sheets each containing multiple columns of numbers, and sometimes when the numbers are not present there is a text error string such as "#N/A N.A.", or "#N/A N Ap". An example of the data is as follows:
4.06
1.28
3.411
#N/A N.A.
2.627
1.157
4.9
4.9
2.963
I would like to put geometric averages of each column from each tab in a summary tab.
* I tried using =GEOMEAN(...), but it can not handle the quantity of data that I have nor can it take non-numeric fields.
* Also Tried something like this =EXP(AVERAGE(IF(ISNUMBER(C:C),LN(C:C)))) as an array, but unless cells are specified explicitly, ie c10:c300, I get an answer of 1. I can not specify the cells because I have a variable height of each column.
View 6 Replies
View Related
May 8, 2014
I have M106 = 4
I need a simple formula to convert that into column Alphabet + 1
So 4 will be Column E (A,B,C,D and 4th will be D+1= E)
View 3 Replies
View Related
Nov 3, 2008
I am working on a spreadsheet which has a column which shows square footages (Column AE) and I also have a column which shows costs (Column Y).
What I would like to do is to bracket these footages in to 6 bands (0 - 5000, 5000 - 10000, 10000 - 15000, 15000 - 20000, 20000 - 25000, 25000+) and then average the costs within a particular band.
View 9 Replies
View Related
Jul 21, 2007
I am building a Workbook which takes data from SheetA and inserts it into SheetB.
Part of the data is only entered when a positive value exists.
I then do an export from SheetB.
The problem is that I need to get the column number and pass it to the cell reference based on the field name in row 1.
Dim sFindstring As String
Dim rFindcell As range
Dim iR As Integer
Dim iC As Integer
sFindString = " Find this string in the cell"
'Using cells find the findstring
Set rFindCell = Cells.Find(What:=sFindString, After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'OK so look here
iR = 3
'I am trying to pick up the column number
iC = rFindCell.Column
rFindCell throws an object or with block variable not set error. Is there some property that I need to set.
View 3 Replies
View Related
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
May 3, 2013
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
View 5 Replies
View Related
Apr 4, 2009
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
View 27 Replies
View Related
May 12, 2014
I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
View 4 Replies
View Related
Mar 11, 2013
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
View 9 Replies
View Related
Mar 6, 2012
I have Column A and B
I want to calculate the total of Column B where by there Column A shows figure "2929".
View 2 Replies
View Related
Apr 16, 2007
I received a reply somewhere
Dim i As Integer
is better used as
Dim i As Long.
Integers get cast to long in the OS, and then back to Integers when passed nback, so it is a unnecessary overhead.
but didn't want to hi-jack that thread
QUESTION:
why would we still use "integer" then?
I tested with
Sub testInteger()
Dim starttime As Double
starttime = Timer
Dim i As Integer
For i = 1 To 32766
Dim j As Integer
For j = 1 To 32766
Next j
Next i
View 9 Replies
View Related
Sep 12, 2009
I have been working on a basic input sheet for progress reporting at work to standardize the information and acheive a half decent automated report.
I am have trouble validating the textbox to only allow an integer to be entered in the box. On result of text being entered on add, a msgbox should prompt the user to enter only text.
How might I do this?
Private Sub CommandButton6_Click()
Dim iRow As Long
Dim ws As Worksheet
View 9 Replies
View Related
Dec 8, 2009
how can i sum integer like
1548=1+5+4+8=1+7=8
View 9 Replies
View Related
Jan 8, 2010
I have 134 parts and each part is available in 5 price groups - these are static items that need no calculation as they are completed on another worksheet. I have 400+ "items" each item is made up of various parts. I need to calculate the cost of each item. Hard to explain without example so I have attached an example.
View 2 Replies
View Related
Jun 2, 2009
I have a table with changing length of the rows and columns. I need to calculate the sums of the rows, the columns ad the diagonal. I managed to write a working code for the columns, but have no success with the rest. Here is what I have:
View 4 Replies
View Related
Mar 15, 2014
I would like to calculate the moving average for the first ten rows in a column where each new entry is added to the cell above the last entry.
For example
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
Therefore in the current list the average is AVERAGE(A5:A14)=6.5.
The next entry added to the list will be in cell A4 making the list look like this
A4, 1
A5, 2
A6, 3
A7, 4
A8, 5
A9, 6
A10, 7
A11, 8
A12, 9
A13, 10
A14, 11
A15, 12
The average of the first 10 cells is now AVERAGE(A4.A13) = 5.5
I would like to enter one formula in say cell A1 that will calculate the moving average in the first ten cells each time a new entry is added. Naturally as needed, new rows are added below cell A1. I hope my problem is clear.
as I have been unable to solve it myself. I have tried using offset but have been unable to resolve the issue.
View 2 Replies
View Related
