Changing function name in formula

K

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.
 
S

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!
 
D

David Biddulph

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

David Biddulph
 
I

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..
 
K

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.
 
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.
 
K

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.
 
I

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
 
K

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.
 
I

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
 
K

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.
 
I

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top