Auto change of Row number within Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning , I would much appreciate some help in creating a formula which would do the following
Cell Resul
A1
B1 1
A simple formula would be - sum(c(a1):c(b1)), where the result in this case would be the sum of cells c5:c10.
Many thanks, Gle
 
Hi Glen,

Look at the INDIRECT() function in HELP

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

millarg said:
Good morning , I would much appreciate some help in creating a formula which would do the following.
Cell Result
A1 5
B1 10
A simple formula would be - sum(c(a1):c(b1)), where the result in this
Click to expand...
case would be the sum of cells c5:c10.
 
Many thanks, it took a combination of indirect, address and many trials and countless errors, fianl working formula i

=COUNT(INDIRECT(ADDRESS(L1924,7,1,1)):INDIRECT(ADDRESS(M1924,7,1,1))

Regards, Glen
 
You could use something like

=COUNT(OFFSET($G$1,$L$1924-1,,$M$1924-$L$1924+1,))

--

Regards,

Peo Sjoblom

millarg said:
Many thanks, it took a combination of indirect, address and many trials
Click to expand...
and countless errors, fianl working formula is
 
Many thanks Peo, that works also, only problem is now I will spend the next 4 hours trying to work out why! :-),
 
The reason I prefer to use this is that it only uses 2 function calls while
your formula uses 5.
I would assume it would slow down things a bit if the workbook becomes big
with lots of formulas. Here's a quick explanation compared to your formula.

You formula uses static column number 7

=COUNT(INDIRECT(ADDRESS(L1924,7,1,1)):INDIRECT(ADDRESS(M1924,7,1,1)))

which is the same as column G (thus me using G1). If you want a dynamic
column index you could use
a cell where to put it or I would instead use A1 and then offset the number
of columns which I would if I
saw that you used a dynamic value for the column.. Now the row numbers start
is in L1924 and the end
in M1924 Assume for simplicity that you used 2 in L1924 and 10 in M1924,
that would mean that we
count the values in G2:G10..

Now my formula

=COUNT(OFFSET($G$1,$L$1924-1,,$M$1924-$L$1924+1,))

the first part is from which row to start. G1 is the cell we start from,
G1 offset by 2 rows would be G3, so you have to deduct 1 for offset to
get to row 2 (G2), if it would have started in 10 we would have deducted 1
since 1+10 is 11 while in your formula we start from row 0, thus the
subtraction.
Now we have the start row. And since M1924 in our simplified example holds
10
we count 9 rows (2,3,4,5,6,7,8,9,10)

$M$1924-$L$1924+1 =10-2+1 which is 9, else my formula would have only
counted 8 rows..



--

Regards,

Peo Sjoblom

millarg said:
Many thanks Peo, that works also, only problem is now I will spend the
Click to expand...
next 4 hours trying to work out why! :-),
 
Back
Top