Formula's dealing with unknown number of rows

[Guest]

I have a numerous sets of data that varies from set to set in the amount of
rows each contains (one set will contain 2135 rows and one will have 3784
rows, etc.). Each set has 12 columns. I have some formulas set up that do
various calcutions on the data.

As things are now I am having to copy and drag the formula's down to match
the end of the data. I also have to enter a formula that calucates the sum
of one of the columns. This is very time-consuming.

Is there a function/formula/option that will fill in my formula all the way
to the end of the rows of data, no further? Is there a
function/formula/option that will then calculate a the sum of a column of
"x"-amount of rows?

Assume the sum of column E.

 

[Arvi Laanemets]

Hi

I have a numerous sets of data that varies from set to set in the amount of
rows each contains (one set will contain 2135 rows and one will have 3784
rows, etc.). Each set has 12 columns. I have some formulas set up that do
various calcutions on the data.

As things are now I am having to copy and drag the formula's down to match
the end of the data. I also have to enter a formula that calucates the sum
of one of the columns. This is very time-consuming.

Is there a function/formula/option that will fill in my formula all the way
to the end of the rows of data, no further?

In Excel, a formula can only display a result - it can't modify contents of
any cell, or change the worksheet in any other way. So no formula can fill
cells with formulas.

a) You can enter the formula into topmost cell of column, move cursor to
lower right corner of this cell so that cursor changes to cross, and then
double-click on this corner. The formula is copied down until there are any
entries (values or formulas) in previous/next column. When some cell in
neighbour columns is empty, copiyng is stopped of-course midway.
b) You can enter formulas into column for "future use". P.e. when in every
row with data must be some value in column A, you can use formulas like
=IF(A2="","",YourFormula). When you p.e. know, that there never will be more
than 5000 rows in table, you can prepare those 5000 rows with such formulas
before. But when there are many columns with formulas, and a lot of rows,
then the workbook may be slowed down considerably.

Is there a function/formula/option that will then calculate a the sum
of a column of "x"-amount of rows?

You can use dynamic ranges as formula parameters. P.e. when your table on
sheet YourTable has always some non-empty value in column A, and there is no
empty rows in your table, in 1st row are column headings, and you want to do
some calculations with column C (sum or count or ...), then define a named
range
YourDynamicRange = OFFSET(YourTable!$C$1,1,,COUNTA(YourTable!$A:$A)-1,1)
Now you can write a formula p.e. for sum of column C as
=SUM(YourDynamicRange)


Arvi Laanemets

 

[Guest]

Thank you it worked great.

Hi




In Excel, a formula can only display a result - it can't modify contents of
any cell, or change the worksheet in any other way. So no formula can fill
cells with formulas.

a) You can enter the formula into topmost cell of column, move cursor to
lower right corner of this cell so that cursor changes to cross, and then
double-click on this corner. The formula is copied down until there are any
entries (values or formulas) in previous/next column. When some cell in
neighbour columns is empty, copiyng is stopped of-course midway.
b) You can enter formulas into column for "future use". P.e. when in every
row with data must be some value in column A, you can use formulas like
=IF(A2="","",YourFormula). When you p.e. know, that there never will be more
than 5000 rows in table, you can prepare those 5000 rows with such formulas
before. But when there are many columns with formulas, and a lot of rows,
then the workbook may be slowed down considerably.





You can use dynamic ranges as formula parameters. P.e. when your table on
sheet YourTable has always some non-empty value in column A, and there is no
empty rows in your table, in 1st row are column headings, and you want to do
some calculations with column C (sum or count or ...), then define a named
range
YourDynamicRange = OFFSET(YourTable!$C$1,1,,COUNTA(YourTable!$A:$A)-1,1)
Now you can write a formula p.e. for sum of column C as
=SUM(YourDynamicRange)


Arvi Laanemets