# Summing/averaging A Column Of Varying Number Of Rows

May 18, 2007

how to code VB to where when data is sorted a formula can be placed at the bottom a column to sum or average the above data?

I have tried relational positioning e.g

each time a row is created, integer n5 = n5 + 1

Application.CutCopyMode = False
ActiveCell.Offset(rowoffset:=1, columnOffset:=12).Select
ActiveCell.FormulaR1C1 = "=sum(r[" - " & n5]c:r[-1]c)"
ActiveCell.Offset(rowoffset:=0, columnOffset:=7).Select
ActiveCell.FormulaR1C1 = "=average(r[" - " & n5]c:r[-1]c)"
Application.CutCopyMode = False
n5 = 0

Where n5 is augmented each time a row is created then set to 0 to count the next list

## Summing/Averaging Last 5 Rows (cells)

Sep 14, 2009

I was trying to sum/average last values in a range. If the range is updated, it should sum/average the last 5.

Tried to do it myself but succeded to do my head in only.

## Combine Rows Of Varying Length And Varying Number Of Occurrences

Jun 28, 2012

There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.

In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are

5700 entries that show up 2x
504 that show up 3x
24 that show up 12x

## Selecting Varying Number Of Rows

Dec 11, 2007

I need to create a line of VBA code to have the macro select several rows to be cut and moved. The problem is, at this stage of the macro,the number of rows vary. Could be 1, could be twenty. I have established DD as the Dim for the number of rows, but I am not sure how to write the command so that 1 through DD are selected, cut, and moved. I may be having a senior moment, but I can't think of how to couch the term.

## Computing With Last 252 Rows Of Varying Length Column

Nov 26, 2009

Using Excel 2003, Column E has a varying and unpredictable number of rows, which only the final 252 rows are of significance. I need to compute the following formulas, here written in English as I can't determine the proper terminology to accomplish this task in Excel:

Final Row with a number in Column E/average (final 252 rows with numbers in column E)+

Final Row with a number in Column E/average (final 126 rows with numbers in column E)+

Final Row with a number in Column E/average (final 63 rows with numbers in column E)

Once again, what makes this not straightforward for me is the column may have wildly varying numbers of rows.

## Transpose Macro Varying Rows From 1 Column

Aug 22, 2009

I've trawled a few forums for a while now trying to find a macro that matches my specifications, or one I can adapt but so far have been unsuccessful, so here goes:

I have one column of data (col. A) of customer names and addresses each with an empty row in between them. The lines of data relating to each customer vary from 6 rows to 14 rows, then an empty row, then the next customer and so on - for several thousand rows.

I am wanting to create a macro that will transpose each customer to either their own row in sheet2, or to column B and then delete column A at the end.

All I can find is macros for a specific number of rows rather than varying. One way would be to have a code that 'pads' out those customers with fewer than 14 lines by inserting rows to make them 14, and then a code to transpose 15 rows at a time.

## Macro For Summing Columns Of Varying Lengths

Mar 16, 2007

I have a worksheet (downloaded from Paypal) and the columns I need to sum are of varying length (sometimes 12 rows and maybe 200). I am having trouble finding a way to find the end plus one of the column to write the formula in.

## Excel 2013 :: Averaging Values Of Rows From Particular Column

Aug 6, 2014

Is it possible to perform average function on subsequent group of rows and make a new set of rows ?

For example: As below, in column 1 the average of values of first 3 rows (i.e, 1, 2 & 3) is 2. similarly average of values in next 3 rows (i.e, 4,5 & 6) is 5 and so on....

Is this possible to get a new set of rows by averaging values of rows from a particular column. without applying average formula in each row of column 2. i m using MS Excel 13.

Column 1 ______ Column 2
1 _____________ 2
2 ______________ 5
3 ______________ 8
4 _____________ 11
5
6
7
8
9
10
11
12

## Summing From Column And Rows

Jan 29, 2010

CCTRCCTRCCTRCCTR112#112NB114#114NWSales group112112114114MONTHQTRYTDNielsen ID#NB##JAN109Q10920091/2/20092894.1717.161231.644704.9JAN109Q10920091/5/20093188.9544.22766.313039.35JAN110Q11020101/6/20092867.92945.571890.774520.44JAN110Q11020101/7/20091927.89210.58484.94960.61RESULTSTABLE 1TOTALS11211410961459742110595211857RESULTSTABLE 2TOTALS112#112NB114#114NW109608361199877441104796115623769481
Table 1 and Table 2 are examples of the results that I need - I have a formula that works for table 2, but when I need to consolidate the numbers as in Table 1 - it won't work - it only views the first 112 or 114 and doesn't sum them together.
the formula that I'm using for table 2 is:
=SUMIF(REV!\$B\$5:\$B\$574,"="&\$A\$50,INDEX(REV!\$E\$5:\$DD\$574,0,MATCH(\$C15,REV!\$E\$4:\$DD\$4,0)))

how do I adjust this formula to sum all 112's and 114's in row 3? it sums the columns good, but I need that extra component to make this project work.

## Resample Data: Total Number Of Points Reduced By Averaging The Data Not By Simply Deleting One Of Every Four Rows

May 16, 2006

I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.

## Summing All Rows In A User Inputted Column

Feb 2, 2009

I need to sum all the rows, except the header row, of a column inputted by the user. The column will be located in a worksheet dubbed SourceSheet.

Here's a few pieces of code related to what I am trying to do. The header row is always row 1. So the rows I'm summing are always 2 - myrows.

## Excel 2013 :: Averaging Rows Of Data Within Larger Blocks Of Datasets - Skipping Rows

Jul 16, 2014

I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.

Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.

I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.

Is there a formula/script for me to skip the correct number of rows to the next data block?

I have attached my spreadsheet to this thread. I am using Excel 2013

FeS_Kinetics.xlsx

## Varying Validation Drop-downs Based On Varying Cell Values

Aug 7, 2013

I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:

List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER

Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”

So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.

=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))

## SUM On Varying Number Of Cells In VBA

Jan 14, 2013

I am writing sum of few cells from the row above, to a single cell and then merging the cells.

I need to achieve this in vba but the problem is that the number of columns I need to use in the SUM formula can change month by month, though I know how many columns are there in each month and I am using A1 style to write formula in my code. I couldn't find any way to write formula in vba in such a way that you can change column name to variable (so that first sum covers columns 2 - 6 (or 2 + 4), second covers 7 (OR 2 + 5) - 10 (OR 2 + 8) etc.).

## Averaging Rows Of Data?

Jun 26, 2013

I would like to find the average of a column of data where the rows of data changes where my starting cell is always B53 but the end row of data can change after each simulation. The average function to be displayed at the bottom of the last row of data.

## VBA - Call Macros With Varying Names And On Varying Modules

Oct 2, 2013

I want to call a macro with a varying name that is within a module with the same name.

I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc

On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.

The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test1.Test1
End Sub

The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test2.Test2
End Sub
etc

However, if I try it so that the calling of the macro varies as below , it does not work:

Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub

## Sum Total For Varying Number Of Cells

Nov 30, 2009

----------A----------B---------C
1-------------------100
2--------11/29----150-------250
3--------11/29----300-------300
4-------------------200
5-------------------300
6-------------------150
7--------11/30----100-------750

Have column A with date, column B with subtotal, column C with Total. The Total (C) is made up of one to five subtotals (B). Subtotals to be included in the Total are those that come after the previous Total up to the row with a date. Only a row with a date has a Total. Is there a formula that can do this?

## Averaging Middle 10 Numbers Of A 12 Number List

Aug 1, 2014

How I could get an average of a 12 number list while at the same time removing the highest and lowest numbers. They aren't in any order either.

1087.2
1041.35
1040.35
1049.65
800.45
734.15
835.7
1157.15
1145
1098.5
788.6
1265.85

## Averaging Values In Column B For Recurring Dates In Column A

Mar 10, 2014

I have a set of data with daily total returns (TR) for 218 companies on the FTSE350 between 2007-2013. The Data looks as follows:

Company ..............Date(daymonthyear)..... TR
1........................... 112007 ..........................10.4
1 .......................... 212007 ..........................10.2
1 .
1 . .
1 .......................... 30102013 .........................12.3
. . . .
. . .
. . .
2............................112007 .......................... 4.5
2 ............................212007 ..........................4.6
2 .
. .
. .
Etc.
. .
. .
218.......................30102013 .....................13.2

I want to perform a dickey fuller test to see if there is a unit root, but for this I can’t have repeated time values, thus I need to create a daily average TR for each day (Thus summing up all TR values for all companies that have data on TR available on that day and averaging them).

How in excel can create an average value for TR every time a series of numbers in the “date” column take the same value? The best thing possible would then be if this could appear so that I have a data set that looks like

Date (daymonthyear)........ TR
112007 ..............................13.2
.
.
.
.
30102013............................ 9.4

## Linking Rows With Varying Increments

May 5, 2006

I have a set of number in worksheet IN:

A B C D E Columns
41.0141.0241.0341.0441.0
42.142.242.342.442.
43.0143.0243.0343.0443.0
44.144.244.344.444.
45.0145.0245.0345.0445.0
46.146.246.346.446.

Datarows increment by 1. I need to map these to another workbook worksheet named OUT
to rows that increment by various amounts 1,2,3 ....10) i.e..............................

## Macro For Rows Of Varying Lengths

Jul 13, 2007

I paste in information in Columns A-E with varying lengths for each report that I need to generate (They generally range anywhere from 10-500 rows). I have formulas already in place in the first row of Columns F-L that need to be copied down as far as columns A-E go. I recorded a macro for this when I try to run the macro for another report with more or less row entries it copies down as far as the original recording was set. How do I make it so the rows will copy down just as many as I need and not some arbitrary number of rows? This is my first post so if any clarification is needed that's no problem.

## Averaging Multiple Columns / Rows If Two Criteria Are Met

Apr 30, 2014

I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.

I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).

Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.

Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.

The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?

Location
Parameter
2008
2009
2010
2011
2012
2013

LocA
alpha
0.24
0.84
0.47
0.27
0.14
0.33

LocA
beta
4
9
9
8
2
9

LocB
alpha
0.24
0.33
0.85
0.54
0.56
0.65

LocB
beta
8
7
6
7
2
9

LocC
alpha
0.24
0.36
0.65
0.66
0.85
0.92

## Consolidating/Averaging Multiple Rows & Columns

May 29, 2007

I am trying to compile a spreadsheet for my job that will find an average of all the break times and meal times that all the employees take. I have the employee list and their respective timeclock punches for the week, so I took that and found their meal and break times for each individual day, but I'm having trouble when it comes to consolidating and averaging the data. The first column lists the employees, but their names are repeated depending on how many times they punched the clock that week, and since not every punch was a meal or break, my time columns contain a lot of zeroes that aren't needed. I need a way to just average each employees' meal and break times.

## Spread Annual Number By Month With Varying Inputs

Dec 18, 2009

I'm almost done with the budget at work and now I need to spread the annual figure by month. Initially I was thinking of spreading it evenly by 12 months, i.e. \$12k annual budget = \$1k per month.

However the boss doesn't want it that way. So I received inputs from certain managers as to when the bulk of the budget will be spent. Now I need to develop some type of formula so that once I input that amount let's say in June, then the rest of the month's will adjust accordingly.

For example, using the example above, if I manually input \$6k in June, then the remaining \$6k from the budget will be spread out evenly through the other 11 months. Is there some clever way to do this or is just a matter of multiple IF statements?

## Macro, Autofill On Varying Numbers Of Rows

Jan 13, 2003

I am putting together a macro to import a CSV file onto excel. I will be manipulating columns of data (concatinating some, dividing by 100 etc.)

What I am having trouble with is that each file imported will have different number of rows.
I tried, in recording a macro, to use autofill (entered formula and double-click fill handle to drop the fourmula into each cell) hoping this would show me how to do it but alas, it just coded the range as the starting and stopping cells (didn't show a count formula or some such.

## Adding And Averaging Every Other Column In One Row?

Jul 27, 2013

I've been searching for the answer but either I do not understand or it isn't out there. I want to add every other column in one row resulting in an average of all the columns in that one particular row.

## Exclamation Collate Data From Varying Number Of Sheets To One Sheet

Nov 28, 2009

Have some code I have found from this site shown below. It works great however I need some help in editing it to change what it does. Basically the code currently takes all the data from set cells from all sheets after a set point and adds a new sheet and copies data from each of the sheets between this point and the new sheet and then pastes it to this final sheet.

What I wish to change is I already have a sheet which I will be using to analyse data from so how would the code look to be able to point to such a sheet within the same workbook? The sheet will be within the first 2 sheets of the workbook so from the code below you can see I have already set it to not include this sheet. I wont be needing a new sheet to be created at the end, I just want the data to paste to my analysis sheet which we can call "analysis".

## VBA To Number Rows In A Column But Starting From Specific Number

Mar 10, 2014

I would like to create a vba script when ran an input.box comes up and asks you to "enter number you want to start numbering from" and when the number is entered in the input box. e.g "7654", excel starts numbering each cell in column A from 7654 until there is an empty cell in column B.

## Automatically Averaging Consecutive Data In A Column

Jan 10, 2014

How can I automatically average consecutive data (4 rows) in a column and jump to the next 4 rows...until the end of my column?

## Dynamic Averaging Based On Adjacent Column?

Nov 2, 2013

I have a column with random values that are generated my my machinery.I should not sort them, because they provide me with the performance graph. Lets say i have column "A"

0
2
9
9.5
30
90
22
15
9
0

As the max value of this is 90, and its 10% of MAX is 9, which occurs at 3rd and 9 th positions.I need to find the average of its adjacent range, with zeros not considered.It has to calculate the max value in the column, find 10% of max and do average of adjacent column from the sl. no of first occurrence of 10% value in first column to the sl no of last occurrence of 10% value in first column.

the exact 10% value may not always exist. 10% of 90 is 9, but if the max value is 94, the 10% would be 9.4 which doesn't exist. then it has to take the nearest available predecessor in that case.i get values in decimals mostly and only some of them are perfect integers, thus this consideration goes with it,and also, my range actually is B14:B10013

i would most probably have the 10% value within the first 50 "non zero" entries and reoccurring somewhere probably within the last 50 "non zero" entries.