How can I vary the row number or column alphabet depending on my n

[Tigerxxx]

Example- Varying row number.
If I need to look up cell A(x) where,
A is the column alphabet.
x is the variable row number.
How can I input x as a variable?

OR

Example- Varying column alphabet.
If I need to look up cell (x)1 where,
x is the variable column alphabet.
1 is the row number.
How can I input x as a variable?

Thank you

 

[T. Valko]

Generally speaking...

=INDEX(range,R,C)

Where:
R = row number
C = column number

Note that both R and C are *relative* to the range. For example:

=INDEX(F5:J10,2,5)

Returns the value at cell address J6. J6 is on the 2nd row in the 5th column
relative to the range F5:J10.

Both R and C can be calculated in many ways limited only by your imagination
and/or skill level.

You can also use this technique on a one dimensional array (single row or
single column).

=INDEX(A:A,10)

Returns the value of cell A10

=INDEX(1:1,10)

Returns the value of cell J1

You can use a range as an entire column (A:A) or row (1:1) as I did above or
you can use a specific range. Just remember that R and C are *relative* to
the range.

 

[Tigerxxx]

Thanks for the response Valko.

Actually I was looking to sum up specific number of cells by varying the row
number or column alphabet.
Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on
another condition or sum rows A2:A7 based on a third condition. Hence I
wanted to be able to vary the row number.
Please advise if you think this is possible.
Thanks!

 

[T. Valko]

Ok, you keep saying "based on a condition" but you don't say what that
condition might be.

So, to sum the range A2:An where n is the variable:

Sum of A2:A4

=SUM(A2:INDEX(A:A,4))

Or:

C1 = 4

=SUM(A2:INDEX(A:A,C1))

Note that if cell C1 is empty the *entire* range will be calculated.

 

[Tigerxxx]

Hi Valko,

Thanks...this helps!
The formula you wrote-
=SUM(A2:INDEX(A:A,C1))
....will certainly help me in the case of variable row number.

To simplify my request could you advise on a similar formula which would
represent a variable colum aplhabet?

Thanks again!

 

[T. Valko]

It would follow the same basic syntax:

To sum B11

A3 = 4

=SUM(B1:INDEX(1:1,A3))

Excel doesn't evaluate the columns based on their letter heading. They get
evaluated based on the column number. On the surface we see column A as "A"
(in A1 reference style) but under the covers inside Excel see's it as column
1. We see column Z as column "Z". Excel see's it as column 26.

 

[Tigerxxx]

Thanks a lot for your help Valko!

It would follow the same basic syntax:

To sum B11

A3 = 4

=SUM(B1:INDEX(1:1,A3))

Excel doesn't evaluate the columns based on their letter heading. They get
evaluated based on the column number. On the surface we see column A as "A"
(in A1 reference style) but under the covers inside Excel see's it as column
1. We see column Z as column "Z". Excel see's it as column 26.

 

[T. Valko]

You're welcome. Thanks for the feedback!