Variable number in cell address reference

[mikesz45]

I have multiple, simple formulas in one spreadsheet. I would like to use:
=sum(A1:AX), =average(N1:NX), etc.
where "x" is the contents of another cell in the same worksheet. Thanks!!

 

[Richard Schollar]

Hi Mike

Welcome to the Board!

You can use INDIRECT:

=SUM(INDIRECT("A1:A" & B1))

Where B1 holds the row number you want to sum to. Same with Average.

Richard

 

[Joe4]

Take a look at the INDIRECT function.

If you wanted to sum to whatever row was indicated in C4, it would look like:

=SUM(INDIRECT("A1:A"&C4))

 

[barry houdini]

...or

=SUM(A1:INDEX(A:A,C4))