Changing function name in formula

[kittronald]

I'm trying to create a formula that will allow changing the
function used.

For example:

A B
1 2 Product
2 3
3
4 =B1(A1:A2)

=B1(A1:A2) should evaluate to 6.

How can I get B1 to not be evaluated as text ?


- Ronald K.

 

[Sixthsense]

Hi Ronald,

Place the cursor in B1 cell and press Alt+E+A+F and copy and the paste
or type the below formula B1 cell

=Sum(A1:A2)

Or
=A1+A2

The above formula will evaluate the result as 5.

Hope that helps!

 

[David Biddulph]

I think the OP wanted the product, not the sum.
=PRODUCT(A1:A2) or =A1*A2 will give the product.

David Biddulph

 

[isabelle]

hi,

=EvaluateString(B1&"(A1:A2)")

Function EvaluateString(tString As String)
EvaluateString = Evaluate(tString)
End Function

cell B1 can contain a function like
Product, Sum, Max, Min, etc..

 

[kittronald]

Isabelle,

Thanks for the response and understanding what I was trying to
accomplish.

For calculating one cell, your solution works.

Is there a way to modify it so that I can Fill Down or Fill Right.

For example, if I were to fill right ...

=EvaluateString($B$1&"(A1:A2)")

it should look like ...

=EvaluateString($B$1&"(B1:B2)").


- Ronald K.

 

[kittronald]

Isabelle,

Figured it out.

=EvaluateString($B$1)(A1:A2)

You're The Man ... I mean you're The Woman.

Thanks for the help.


- Ronald K.

 

[Gord]

You have tested this thoroughly?


Gord Dibben Microsoft Excel MVP

 

[isabelle]

wow, i can confirm that neni ;-)

 

[isabelle]

hi kittronald,

=EvaluateString($B$1&"(B1:B2)").

the cell "B1" can not be do "function name" and be a part of the evaluation

 

[kittronald]

Isabelle,

<Sound of air leaving balloon>

Ok, so I'm back to square one.

I was using a different, third party function, that when evaluated,
mysteriously returned a number - which led me to believe the correct
value was being returned.

Basically, I'm trying to mass replace a function without resorting
to using a macro that performs a Search and Replace.

If B1 were the linked cell of a ComboBox that had multiple function
names, how could I achieve the initial solution ?

And how did I earn the title of a Hungarian aunt ?



- Ronald K.

 

[isabelle]

hi,


Le 2011-07-21 00:51, kittronald a écrit :
how could I achieve the initial solution ?

i prepared a small example
http://cjoint.com/?AGvpgCTJdUT

And how did I earn the title of a Hungarian aunt ?

i dont know what "Hungarian aunt " is

 

[kittronald]

Isabelle,

Thanks for the help.

For some reason I couldn't open that file - could you post the
example in this group ?

As for "neni" (hungarian aunt) - http://en.wiktionary.org/wiki/néni



- Ronald K.

 

[isabelle]

hi,

sorry for that, in my country "neni" means (no, none, never)
i participate in this group via Netscape, so can you give me the web address where i can post the file

 

[kittronald]

Isabelle,

Thanks for going through all that effort.

I finally opened your spreadsheet - just had a problem with AV.

Basically, you did what I was doing.

But then I realized that I needed to be able to take that formula
and fill it to the right and down several cells.

However, with what you and I came up with, the function parameters
are text and will not change when filling across or down.

For example, ...

=EvaluateString($B$1&"(B1:B2)")

when selected and filled to the right ...

will still be =EvaluateString($B$1&"(B1:B2)").

However, in order to make this type of function replacement usable,
filling the formula to the right would have to change to
=EvaluateString($B$1&"(C1:C2)"), etc.

At this point, I'm considering creating a macro that does the
following:

1) Select the first cell that contains the generic formula

2) Find and Replace the function in the first cell with a
different function selected in a ComboBox - like you did in your
spreadsheet.

3) Fill the first cell to the end of the first row

4) Fill the first row down to the last row

This appears to be faster than using a UDF to evaluate text to non-
text and a mass Search and Replace.

Of course, there might be a better way ...



- Ronald K.

 

[isabelle]

hi ,

if ranges structure is always the same, the formula could be:

=EvaluateString(B1&"("&ADRESSE(LIGNE()-4;COLONNE())&":"&ADRESSE(LIGNE()-2;COLONNE())&")")

this gives possibility to copy to the right or down

 

[kittronald]

Isabelle,

Vous êtes Français ?

I'll give it a try and see if there's a performance difference
between your formula and creating a macro that changes the formula in
one cell and copies it right and down.

Just so you know what I'm working with, potentially the total
number of formulas like that will likely exceed 6,000,000 ... on just
one worksheet ! And that's just modifying the formula - it takes
another 20 minutes to calculate.

Now I know how an eight core processor with hyper-threading could
be useful.

Thanks for the in-depth support.



- Ronald K.

 

[isabelle]

oops i forgot the translation, i'm in french family and also a french PC, sorry for this oversight
=EvaluateString(B1&"("&ADDRESS(ROW()-4,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())&")")
but i think a macro would be much more faster

 

[kittronald]

Isabelle,

Thanks a bunch.



- Ronald K.