How can I use numbers to define range for Max() ?
 

I know the absolute offsets of the rows and columns in a seperate sheet
and want to get the max of that range. For example:
=Max(CC, RR, cc, rr)

where all the parameters are numbers allowing me to define the range.


--
excel12345
------------------------------------------------------------------------
excel12345's Profile: http://www.excelforum.com/member.php...o&userid=31975
View this thread: http://www.excelforum.com/showthread...hreadid=517016

How can I use numbers to define range for Max() ?
 

Try:


Function mxinr(c1 As Long, r1 As Long, c2 As Long, r2 As Long) As Double
Dim r, rr As Range
Dim v As Double
Set r = Range(Cells(r1, c1), Cells(r2, c2))
v = Cells(r1, c1).Value
For Each rr In r
If rr.Value v Then
v = rr.Value
End If
Next
mxinr = v
End Function

--
Gary''s Student


"excel12345" wrote:


I know the absolute offsets of the rows and columns in a seperate sheet
and want to get the max of that range. For example:
=Max(CC, RR, cc, rr)

where all the parameters are numbers allowing me to define the range.


--
excel12345
------------------------------------------------------------------------
excel12345's Profile: http://www.excelforum.com/member.php...o&userid=31975
View this thread: http://www.excelforum.com/showthread...hreadid=517016

How can I use numbers to define range for Max() ?
 

Check out the OFFSET() function in XL Help.


In article ,
excel12345
wrote:

I know the absolute offsets of the rows and columns in a seperate sheet
and want to get the max of that range. For example:
=Max(CC, RR, cc, rr)

where all the parameters are numbers allowing me to define the range.

How can I use numbers to define range for Max() ?
 

=MAX(INDIRECT("Sheet2!"&ADDRESS(rrf,ccf)&"":""&ADD RESS(rrl,ccl)))

HTH
--
AP

"excel12345" a
écrit dans le message de
...

I know the absolute offsets of the rows and columns in a seperate sheet
and want to get the max of that range. For example:
=Max(CC, RR, cc, rr)

where all the parameters are numbers allowing me to define the range.


--
excel12345
------------------------------------------------------------------------
excel12345's Profile:
http://www.excelforum.com/member.php...o&userid=31975
View this thread: http://www.excelforum.com/showthread...hreadid=517016

How can I use numbers to define range for Max() ?
 

Thanks to all of you for your help - I actually used a bit from each of
you and solved the problem and learned some valuable information at the
same time. Your time and effort in helping me is much appreciated.


--
excel12345
------------------------------------------------------------------------
excel12345's Profile: http://www.excelforum.com/member.php...o&userid=31975
View this thread: http://www.excelforum.com/showthread...hreadid=517016