Return Range for Formula

[lym13]

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

 

[mortgageman]

See this website for the first part of your question.

http://www.cpearson.com/excel/excelF.htm

When you have your range, you can easily do a max on that range.

Gene, "The Mortgage Man", Klein

 

[SydneyGeek]

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:

=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]

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)

 

[SydneyGeek]

Cheaper to run...

=MAX(A3:IV3)

Doh! Should have kept it simple...

Denis