Step Average Across Every X Columns
Mar 11, 2008
Is there a way to loop through a range of numbers, pick the first 24 columns, do some kind of caclulation for each column and row, the start again on column 25 for another 24 columns, perform the same calculation, go to the next set of 24 columns, etc?
I have tried using, for example, AVERAGE(OFFSET(range,0,0,24,24) and that correctly returns the average for the first 24 columns (range being the range of data I am using like D366:HK389). However, unless I manually change the starting column for the next set, it doesn't return the correct result. I have attached a sample data spreadsheet. There are 9 Locations in the spreadsheet where each Location contains a 24x24 matrix.
View 4 Replies
ADVERTISEMENT
Mar 18, 2008
I have 4 columns. I want to add 100 to all rows in the first column, 150 to the ones in the second, etc. Is this possible? I have Excel 2007.
View 9 Replies
View Related
Jun 5, 2008
I don't know what exactly is happening, but every step of the macro is happening every 1 second. Literally, the BPM is 60. It will execute a step in the macro every second, when in other macro's i've made, it zips right through as if it was on fast forward.
View 9 Replies
View Related
Mar 3, 2012
Is this possible? If
A B C
0 0 0
0 0 0
1 4 5
2 4 8
0 0 0
0 0 0
0 0 0
2 6 9
8 9 3
3 5 6
0 0 0
In the raw data, the 0s split up the data into different steps. I am trying to create a function that selects the data from the ABC column and puts it into new columns for each step. I.e:
New Columns with:
1 4 5
2 4 8
and
2 6 9
8 9 3
3 5 6
In the raw data files, there will be 10 steps, with a varying length of data.
View 2 Replies
View Related
Apr 26, 2014
i am trying to complete a spreadsheet which most of it is done but one column i have i have to average everyother square in each column which i did but when i total those columns at the end i get the dreaded #div/01 which means any value that i dont have in a particular column it will not add is there anyway to fix this. i am attaching a sample of the spreadsheet so you can see exactly what i am trying to do .
View 6 Replies
View Related
Nov 10, 2009
I have twelve columns of data nine of which are filled. I'm trying to come up with a formula that averages a column only if it has data in it, I have attached a sample file. As I add data the average should change without changing the formula.
View 2 Replies
View Related
Jul 30, 2009
I have data that I need averaged. The problem is that my data is taken into chunks of time, and I want to average each individual column after each break of time. My data looks like this: ...
View 6 Replies
View Related
Feb 1, 2010
Looking for an average formula.
All found formulas average within a string (ex. A1:F1).
My spreadsheet contains a series of three columns.
Example:
Column A reflects a number stat for a month (January).
Column B reflects another number stat for the same month (January)
Column C reflects the difference between Columns A & B.
Column D reflects a number stat for the next month (February)
Column E reflects another number stat for the same month (February)
Column F reflects the difference between Columns D & E.
and so on.
I would like to average the rows stats reflected in Column A, D, G, J, and so on.
Also, I would like to average the rows stats reflected in Column B, E, H, K, and so on.
Also, I would like to average the rows stats reflected in Column C, F, I, L, and so on.
View 9 Replies
View Related
Jul 25, 2007
I am calculating the average for a range of cells in a row. =AVERAGE(D7:P7). I need to hide some of the columns and calculate the average based only on the visible columns. How can I do that?
View 5 Replies
View Related
Jul 1, 2014
I have a pivot table showing actual and forecasted sales per month for diffrent regions/sales channels/segment.
I would like to calculate the average monthly sale, for forecasted and actual data, within the pivot instead of doing it outside in columns to the right of the pivot table.
View 1 Replies
View Related
Jan 18, 2014
I am trying to get the conditional average of 2 data columns.
data_1 category data_2 category
2.5 Yes 1.2 No
4.2 No 2.6 No
3.5 Yes 3.9 Yes
4.1 No 2.5 Yes
What I need is the average across the 2 data columns (data_1 and data_2) for category Yes and category No. I know I can use averageif to get the conditional average of 1 column, but that doesn't work with 2.
View 5 Replies
View Related
Jul 29, 2014
I would like to average a column based upon another column value. Namely, if one column is a duplicate, I would like to take the column to its right value and average all the duplicates. For example
comp 1409 99.7
comp 1409 99.5
comp 1409 100
comp 1308 65.7
comp 1509 99.5
I would like that to become
comp 1409 99.73
comp 1308 65.7
comp 1509 99.5
View 6 Replies
View Related
Oct 7, 2008
how to calculate a six month rolling average for hours in AD column and AE column, this average would be entered in to AF1. the date column is AC the date is not consecutive it does miss days out.
View 14 Replies
View Related
May 4, 2009
I am trying to determine the average reduction amount of appraised property values. I have two columns in my spreadsheet. Column H has appraised value of property. Column I has the accepted value of the property. Sometimes the accepted value is the same as the appraised value, sometimes it is reduced, and sometimes it is rejected.
I want to be able to find the average reduction amount when the accepted value is less than the appraised value and is not rejected.
Column H always shows a numerical value (i.e. $250,000), but Column I may have a numerical value or show "rejected".
View 9 Replies
View Related
Dec 30, 2008
I am trying to include numbers in an average if two separate columns prove true. For example, I want to include all numbers in column "C" in the average if the cell in column "A" matches a name and the cell in column "B" is equal to the number 1.
View 2 Replies
View Related
Jul 26, 2006
How do I get the average sale if column N=sale amount if ...
View 9 Replies
View Related
Oct 4, 2008
I have 10 sheets in my workbook that represent 10 employees. Column G, rows 5 through 739, represent the amount of time each employee spends doing a certain task for each day of the year. All these cells have zeros in them until a we come to that day and a time is entered for that day. The result in each cell is a certain amount of minutes spent on that task.
I can average each employees time individually on it's own sheet with this formula: {=AVERAGE(IF(G5:G739=0,"",G5:G739))}, but what I'm having a problem doing is averaging all 10 employees together on a separate sheet. I'm trying to figure out company wide how much time is spent on this particular task each day.
My depth of understanding here isn't too great, I'm learning as I go. I think I have to incorporate that formula while also selecting each sheet. I'm starting to get the BSOD in my head with this one.
View 3 Replies
View Related
Aug 3, 2013
I am trying to find the average of a cell with a name in that may appear several times in various columns. I then need to find the average of the value against that name in all the columns that it occurs in. Example:
a
b
c
d
e
f
g
h
i
j
k
Aldridge Sean
2
3
3
Andrew Rob
4
3
3
Aldridge Sean
3
2
2
[Code] .....
In the above example I want to find the average score for aldridge sean in column b, f and j.
View 9 Replies
View Related
Oct 19, 2006
I have two columns:
A B
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1
I need a formula that takes the difference of the two columns (1-10, 2-9, etc.) and then finds the average for all of the differences. I don't want to have a third column with the differences and then take the average of those.
View 9 Replies
View Related
Jul 19, 2007
I have three columns in my worksheet.
Column 1 reports the name of three people, John , George and Jim, column 2 the year for which they are paid (e.g. 2002, 2003 etc) and column 3 the amount of money they received each year (e.g. John received 18$ in 2005, $15 in 2004 and 11$ in 2003). I need a function to give me the average salary earned by each of these people in the period 2003-2004 e.g. John earned $15 in 2004 and $11 in 2003, so the average value is 13. Is there a function to do it automatically (so taking the conditional average---average for john given that year is 2004 or 2005)?
I want this average to be in column 5 (column 4 will contain the names john, george and jim)
View 9 Replies
View Related
Feb 18, 2008
I have data in 3 different columns (A, C, and E) to name a few. I want to average each of these columns, but if any of them include zero values, I want to exclude that from my calculation.
Example:
Column A = 10
Column B = 0
Column C = 3
Right now, my "average" formula, is showing the average as 4.33. (average a5, b5, c5)
The real average I'm looking for is 6.5. What is the best way to setup my formula?
View 9 Replies
View Related
Feb 12, 2007
code for an assignment, I have a table of values, the 5 colums are arrays, one with 20 student names and the other 4 are grades for 4 subjects, each colum being one subject.
I need code to find the average of each student and get it to display in a separate colum and averages for each subject also getting it to display in another row.
View 9 Replies
View Related
Apr 20, 2007
I have a file that I want Column D to average by column A and C. I also want to ignore any rows that dont have data in column B in the average. See sample file.
View 5 Replies
View Related
May 25, 2008
I am trying to average a range which continually changes depending on the amount of registries entered in a given week. for example, one week there may be 5 registries which would fill five columns while another week there may be 15 registries, once again filling 15 columns. I would like to find the average of x amount of columns.
My initial approach was to select the first blank cell to the right of the data and then find the last filled cell (which should be the first column of data.
with this range i tried hopelessly to use the average function (did not work):
Sub averagemake()
Dim iLastColumn As Integer
Dim Rng As Range
Sheets("All Data(Values)").Select
Range("A6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
iLastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-iLastColumn]:RC[-1])"
End Sub
View 5 Replies
View Related
May 7, 2006
I'm trying to run a loop that doesn't go through every value between the lower and upper bounds so am using Step at the end of the For line:
For j = 0 To s Step loop_step
'Does stuff in here - not relevant
Next j
The amount that is being stepped through is set as a variable previously - called loop_step, and works fine for small values (e.g. stepping through 10 at a time) but comes up with an error for 1000. I haven't checked how large the value needs to be before it creates an error, but I was wondering if anyone knew if there's a limit on the number you can step through? Otherwise I have no idea what's causing this problem!
View 6 Replies
View Related
Mar 22, 2013
If my spreadsheet has columns A-H.
A is Date
B is Room Number
C is Location
D,E,F all have numbers results in them.
G is the average of D,E,F for each person
H is group average.
If I want to define a group average in column H where it calculates the average of a group that attended the same location, date and room, for each group that attended would that be possible?
So For Example (Columns DEF left out for clarity)
Date
Rm
Location
G Inidivual Average
[Code]....
So because room number changed it calculated as a different group. Is this possible?
View 3 Replies
View Related
Jan 23, 2014
I have three columns of data, one containing numerical values. I want to average the numbers based on the other two columns of data. Attached is an example file with the desired output I'd like. What is a formula that will do this?
AverageExample.xlsx
View 7 Replies
View Related
Feb 27, 2014
I have the following code to go through various values in a column and colour code them. However I am facing two issues here:
1. I need to generalize the code such that it runs through column G till column S in different worksheets
2. Few columns between G and S do not have any data and the code is getting stuck at the line below:
myResult = WorksheetFunction.Average(Range("G:G"))
View 1 Replies
View Related
Mar 6, 2013
Cell C6 contains a drop down from which a month can be selected, range is Oct-12 to Sep-13
Column AB will contain a YTD sum of actuals, Column AC will contain sum of budget.
For example,
Actuals: If Mar-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6
Budget: If Mar-13 is slected in cell C6, I would like call AC10 to sum E6+G6+I+K6+M+O6
2nd scenario
Actuals: If Jun-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6+P6+R6+T6
Budget: If Jun-13 is selected in cell C6, I would like cell AC10 to sum E6+G6+I+K6+M+O6+Q6+S6+U6
I've been reading up about an offset function but can't get to grips with it - should I use that, or an index/match mix?
View 1 Replies
View Related
May 26, 2008
I get a report that I have to manually edit everyday. I've done some VBA, but not sure where to start with this one.
The attachment has two sheets, first is what I get and the second is what I want. I'll explain the process i have to go thru now and maybe I can get some help to automate it.
First
-Add the tech number to the first column for each job for each tech.
7988 us the first tech number.
-Then delete every row that isn't a job.
-Add a column with the install date.(report data near the top.)
-Add a column and populate it with the system, which is the sheet name.
Thats why its example.
View 14 Replies
View Related
