Dynamic formula

[p6er]

I have a cell, which generates a certain range. I now want to use this range in a formula, which is also generated.

E.g.:

cell C1000 contains the text $A$1:$A$1000
cell D1000 should now either calculate MAX or MIN via a
=IF($B$1000<1234, MAX(cell("contents",$C$1000),MIN(cell("contents",$C$1000))

As I got a #VALUE error, I tried to minimize the complexity in D1000 to see whether it works at all:
=MAX(cell("contents",$C$1000))

but I still get the error. If I replace the cell funciton with the actual range, it works fine, so the range is correct. Even usage of the address function within the MAX function doesn't give me a result.

I then assigned D1000 the text of the complete MAX function via
=CONCATENATE("=MAX(",TEXT(T3,"$A$0"),":",TEXT(U3,"$A$0"),")")

with T3 containing a 2 and U3 containing a 1000. But now I don't know, how to execute this command


Any ideas how to dynamically generate functions.

Thanks,
Peter

[Dave Peterson]

Concatenating strings will leave you with strings--not formulas that will be
evaluated.

But it kind of looks like this would work:

=if($b1000<1234,min(indirect(c1000)),max(indirect(c1000)))



p6er wrote:
>
> I have a cell, which generates a certain range. I now want to use this
> range in a formula, which is also generated.
>
> E.g.:
>
> cell C1000 contains the text $A$1:$A$1000
> cell D1000 should now either calculate MAX or MIN via a
> =IF($B$1000<1234,
> MAX(cell("contents",$C$1000),MIN(cell("contents",$C$1000))
>
> As I got a #VALUE error, I tried to minimize the complexity in D1000 to
> see whether it works at all:
> =MAX(cell("contents",$C$1000))
>
> but I still get the error. If I replace the cell funciton with the
> actual range, it works fine, so the range is correct. Even usage of the
> address function within the MAX function doesn't give me a result.
>
> I then assigned D1000 the *text* of the complete MAX function via
> =CONCATENATE("=MAX(",TEXT(T3,"$A$0"),":",TEXT(U3,"$A$0"),")")
>
> with T3 containing a 2 and U3 containing a 1000. But now I don't know,
> how to *execute * this command
>
> Any ideas how to dynamically generate functions.
>
> Thanks,
> Peter
>
> --
> p6er
> ------------------------------------------------------------------------
> p6er's Profile: http://www.excelforum.com/member.php...o&userid=28571
> View this thread: http://www.excelforum.com/showthread...hreadid=482348

--

Dave Peterson

[B. R.Ramachandran]

Hi,

=MAX(INDIRECT($C$1000)) and =MIN(INDIRECT($C$1000)

should calculate the maximum and the minimum of the range defined in the
cell C1000. So you can use this syntax in the large formula you have shown
at the beginning of your post.

Regrds,
B. R. Ramachandran

"p6er" wrote:

>
> I have a cell, which generates a certain range. I now want to use this
> range in a formula, which is also generated.
>
> E.g.:
>
> cell C1000 contains the text $A$1:$A$1000
> cell D1000 should now either calculate MAX or MIN via a
> =IF($B$1000<1234,
> MAX(cell("contents",$C$1000),MIN(cell("contents",$C$1000))
>
> As I got a #VALUE error, I tried to minimize the complexity in D1000 to
> see whether it works at all:
> =MAX(cell("contents",$C$1000))
>
> but I still get the error. If I replace the cell funciton with the
> actual range, it works fine, so the range is correct. Even usage of the
> address function within the MAX function doesn't give me a result.
>
> I then assigned D1000 the *text* of the complete MAX function via
> =CONCATENATE("=MAX(",TEXT(T3,"$A$0"),":",TEXT(U3,"$A$0"),")")
>
> with T3 containing a 2 and U3 containing a 1000. But now I don't know,
> how to *execute * this command
>
>
> Any ideas how to dynamically generate functions.
>
> Thanks,
> Peter
>
>
> --
> p6er
> ------------------------------------------------------------------------
> p6er's Profile: http://www.excelforum.com/member.php...o&userid=28571
> View this thread: http://www.excelforum.com/showthread...hreadid=482348
>
>