Hi,

I want to perform operations on address ranges, such as
MAXA(Value1:Value2), in which the range addresses themselves are
variables, the results of other operations.

There is no problem returning the range addresses using functions such
as CELL or ADDRESS(...) but the returned values don't seem to want to
plug into the next function.

For example, I use the formula
=ADDRESS(VLOOKUP(Pos_MinRear;Synopsis!$D$103:$G$10 8;4);3;;;"Synopsis!")

to return the string

'Synopsis!'$C$103

and a similar formula to return

'Synopsis!'$C$105

from which range I want to then find

MAXA('Synopsis!'$C$103:'Synopsis!'$C$105)

I'm sure there is a simple solution I have overlooked. Who can give me
a leg up with this?


regards
NC

This format works...
=MAXA(Synopsis!$C$103:$C$105)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"NickCory"
wrote in message
Hi,
I want to perform operations on address ranges, such as
MAXA(Value1:Value2), in which the range addresses themselves are
variables, the results of other operations.
There is no problem returning the range addresses using functions such
as CELL or ADDRESS(...) but the returned values don't seem to want to
plug into the next function.
For example, I use the formula
=ADDRESS(VLOOKUP(Pos_MinRear;Synopsis!$D$103:$G$10 8;4);3;;;"Synopsis!")

to return the string
'Synopsis!'$C$103
and a similar formula to return
'Synopsis!'$C$105

from which range I want to then find
MAXA('Synopsis!'$C$103:'Synopsis!'$C$105)

I'm sure there is a simple solution I have overlooked. Who can give me
a leg up with this?
regards
NC

Hi, Jim

Thanks for replying.

Yes, I know that format and it works fine with a fixed vector or array
but this time I'm trying to insert cell addresses into the MAXA(...)
formula that are variables coming from other formulae. Dependant upon
the results of the preceeding calculations I have to test a different
range. This range, which just happens to be a vector, can be defined by
any pair of values from the set [$C$105 ... $C$108]. I hadn't figued
out how to insert arguments into the MAXA(...) formula that are not
fixed addresses. The cell format doesn't offer an option to define them
as addresses, nor are they identified as such by a leading character.

ADDRESS(...) returns as a text string the addresses of the cells I want
to test, so I thought that would be a promising way of collecting the
addresses I need. But then I found, and I should have expected this,
that MAXA(...) doesn't recognise these strings as cell addresses, only
as the text strings they are.

Meantime, I have discovered the way to tell MAXA(...) that these
strings are to be handled as cell addresses. It is to preceed them with
the function INDIRECT. By using the syntax :

MAXA( INDIRECT(variable cell address1) :
INDIRECT(variable cell address2) )

I was able to get the answer I was seeking. I found it using the Excel
built-in help after searching on the keyword "address", so that was ok.
The colon plays a major role in determining whether the whole range is
tested - a typo that puts in a semicolon leads to just the beginning
and end cells being tested. (This is Germany- the comma is used as the
decimal separator so in Excel it is replaced by a semicolon to separate
the arguments of functions.)

Problem solved!



regards,
Nick
nr. Berlin, Germany



Jim Cone wrote:
This format works...
=MAXA(Synopsis!$C$103:$C$105)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"NickCory"
wrote in message
Hi,
I want to perform operations on address ranges, such as
MAXA(Value1:Value2), in which the range addresses themselves are
variables, the results of other operations.
There is no problem returning the range addresses using functions such
as CELL or ADDRESS(...) but the returned values don't seem to want to
plug into the next function.
For example, I use the formula
=ADDRESS(VLOOKUP(Pos_MinRear;Synopsis!$D$103:$G$10 8;4);3;;;"Synopsis!")

to return the string
'Synopsis!'$C$103
and a similar formula to return
'Synopsis!'$C$105

from which range I want to then find
MAXA('Synopsis!'$C$103:'Synopsis!'$C$105)

I'm sure there is a simple solution I have overlooked. Who can give me
a leg up with this?
regards
NC

Nick,
Thanks for the update.
Jim Cone


"NickCory"

wrote in message
Hi, Jim
Thanks for replying.
Yes, I know that format and it works fine with a fixed vector or array
but this time I'm trying to insert cell addresses into the MAXA(...)
formula that are variables coming from other formulae. Dependant upon
the results of the preceeding calculations I have to test a different
range. This range, which just happens to be a vector, can be defined by
any pair of values from the set [$C$105 ... $C$108]. I hadn't figued
out how to insert arguments into the MAXA(...) formula that are not
fixed addresses. The cell format doesn't offer an option to define them
as addresses, nor are they identified as such by a leading character.

ADDRESS(...) returns as a text string the addresses of the cells I want
to test, so I thought that would be a promising way of collecting the
addresses I need. But then I found, and I should have expected this,
that MAXA(...) doesn't recognise these strings as cell addresses, only
as the text strings they are.

Meantime, I have discovered the way to tell MAXA(...) that these
strings are to be handled as cell addresses. It is to preceed them with
the function INDIRECT. By using the syntax :

MAXA( INDIRECT(variable cell address1) :
INDIRECT(variable cell address2) )

I was able to get the answer I was seeking. I found it using the Excel
built-in help after searching on the keyword "address", so that was ok.
The colon plays a major role in determining whether the whole range is
tested - a typo that puts in a semicolon leads to just the beginning
and end cells being tested. (This is Germany- the comma is used as the
decimal separator so in Excel it is replaced by a semicolon to separate
the arguments of functions.)
Problem solved!
regards,
Nick
nr. Berlin, Germany



Jim Cone wrote:
This format works...
=MAXA(Synopsis!$C$103:$C$105)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"NickCory"
wrote in message
Hi,
I want to perform operations on address ranges, such as
MAXA(Value1:Value2), in which the range addresses themselves are
variables, the results of other operations.
There is no problem returning the range addresses using functions such
as CELL or ADDRESS(...) but the returned values don't seem to want to
plug into the next function.
For example, I use the formula
=ADDRESS(VLOOKUP(Pos_MinRear;Synopsis!$D$103:$G$10 8;4);3;;;"Synopsis!")

to return the string
'Synopsis!'$C$103
and a similar formula to return
'Synopsis!'$C$105

from which range I want to then find
MAXA('Synopsis!'$C$103:'Synopsis!'$C$105)

I'm sure there is a simple solution I have overlooked. Who can give me
a leg up with this?
regards
NC