Row counter in formula

[Guest]

I add and subtract rows of data each week. One of the simple formulas
includes how many rows there are. How do I include the row number in a
formula? =SUM(F24/22) 22 refers to the first column which is a row counter
which contains 1,2,3,4,5,6..etc as I don't know how to enter a row counter in
the formula that is automatically updated as I add or delete rows.

 

[Max]

One way which might work for you

Assuming your row numbering is in col A,
with perhaps a non-numeric label in A1,
and rows numbered from A2 down (1,2,3 ...)
(Row numbering in col A is assumed updated
each time you add/delete rows)

Try: =SUM(F24/COUNT(A:A))

If the label in A1 is also a number,
use: =SUM(F24/COUNT(A:A)-1)

 

[Arvi Laanemets]

Hi

Define the datarange in column as dynamic range - so it'll adjust himself
whenever you enter new rows of data into table. Then your formula will be
simply
=SUM(YourRange)

How to define a dynamic named range, depends on design of your worksheet
(from where your data start, have you header row(s), are there some other
fixed entries in same column, have you empty cells included into datarange,
are your data values numeric or text, are there values or formulas in
datarange, etc.), and how you plan to use this named range in formulas (do
you refer always to whole datarange, or depends the referred range on
position of cell with formula, etc.) - so for any useful advice more
detailed explanations must be given.

 

[Max]

Try: =SUM(F24/COUNT(A:A))

If the label in A1 is also a number,
use: =SUM(F24/COUNT(A:A)-1)

On 2nd thought,
think the SUM(...) is superfluous

Try just:

=F24/COUNT(A:A)

or

=F24/(COUNT(A:A)-1)

 

[Guest]

thanks for the reply, but i don't understand the response. I don't know what
to put in (your range). i created a column to number the rows as i was
regularly adding rows and deleting rows and the formula that utilized the
number of rows had to be updated/changed by hand. This bugged me. I called
the column "number"

 

[Arvi Laanemets]

Hi

An example:

On Sheet1 you have a table, p.e. in columns A:H, with headers in row1.
Column A is a key column, i.e. whenever there are data on row, the cell in
column A is not empty. And there never are gaps (empty rows) in your table.

You can define dynamic named ranges:
from menu select Insert.Name.Define;
in Names field determine a name for range;
in Refers To field, enter value, reference or formula returning a value or
cell reference;
OK.

Now, for our example, define named ranges p.e.
MyTable=OFFSET(Sheet1!$A$2,,,COUNTIF(Sheet1!$A:$A,"<>")-1,9)
MyKey=OFFSET(Sheet1!$A$2,,,COUNTIF(Sheet1!$A:$A,"<>")-1,1)
MyValue=OFFSET(Sheet1!$F$2,,,COUNTIF(Sheet1!$A:$A,"<>")-1,1)

When you add or remove rows in your table, those ranges will always adjust
automatically, the range MyTable returns the range Sheet1!A2:Hx, MyKey
returns the range Sheet1!A2:Ax and MyValue returns the range Sheet1!F2:Fx,
where x is the number of last filled row in table. (Here is demonstrated one
way to define dynamic ranges, but depending on your table design and your
goals, there are possible other definitions too)

Now, whenever you p.e. want:
a) to count the nimber of filled rows in table
=COUNTA(MyKey)
b) to sum all values in MyValue where MyKey=z
=COUNTIF(MyKey,x,MyValue)
to return a value from column H from row where MyKey=z
=VLOOKUP(z,MyTable,9,0)
etc.