Variable number in cell address reference

mikesz45

New Member
Joined
Sep 14, 2006
Messages
1
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!!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,687
Members
410,698
Latest member
Wloven
Top