Return Range for Formula

lym13

New Member
Joined
Sep 12, 2006
Messages
1
I have a rows of data that starts at column C and are of varying lengths. ie. some data goes to column F while other data goes to column AY. The data is constantly changing and I would like to perform a MAX function on each row of data without having to change the range manually everytime an extra cell of data is added to the row.

Is there a way that I can look from say cell A3 until I find a blank cell and then return the range that contains data ie A3:AY3 and perform the MAX function on that range?

Thanks
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
You can do it by creating a dynamic range. Here's how:

Assuming that data starts in C3, place the cursor in A3 and then --
1. Insert > Name > Define
2. In the top row of the dialog, enter ThisRow
3. Replace the last row of the dialog with this:
Code:
=OFFSET(Sheet1!$C3,0,0,1,COUNTA(Sheet1!$C3:$IV3))
Note: Make sure the sheet reference is correct! If your sheet has more than 1 word in the name, refer to it like ='Sheet 23'!$C3
4. Press Add, then OK.

In A3 of the worksheet, enter =MAX(ThisRow) and fill down.

Denis
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I have a rows of data that starts at column C and are of varying lengths. ie. some data goes to column F while other data goes to column AY. The data is constantly changing and I would like to perform a MAX function on each row of data without having to change the range manually everytime an extra cell of data is added to the row.

Is there a way that I can look from say cell A3 until I find a blank cell and then return the range that contains data ie A3:AY3 and perform the MAX function on that range?

Thanks

Cheaper to run...

=MAX(A3:IV3)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,042
Messages
5,545,688
Members
410,698
Latest member
Wloven
Top