Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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! --- Sixthsense On Jul 20, 3:45*am, kittronald wrote: * *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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
I think the OP wanted the product, not the sum.
=PRODUCT(A1:A2) or =A1*A2 will give the product. David Biddulph On 20/07/2011 08:04, Sixthsense wrote: 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! --- Sixthsense On Jul 20, 3:45 am, wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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.. -- isabelle |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
You have tested this thoroughly?
Gord Dibben Microsoft Excel MVP On Wed, 20 Jul 2011 15:49:26 -0700 (PDT), kittronald wrote: 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
wow, i can confirm that neni ;-)
-- isabelle Le 2011-07-20 19:04, Gord a écrit : You have tested this thoroughly? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
hi kittronald,
=EvaluateString($B$1&"(B1:B2)"). the cell "B1" can not be do "function name" and be a part of the evaluation -- isabelle |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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 -- isabelle |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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%C3%A9ni - Ronald K. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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 -- isabelle |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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 -- isabelle |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
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 -- isabelle |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing function name in formula
Isabelle,
Thanks a bunch. - Ronald K. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the range for averages with out changing the formula. | Excel Worksheet Functions | |||
Worksheet Function Keeps Changing, need help | Excel Worksheet Functions | |||
NOW() function that is un-changing | Excel Discussion (Misc queries) | |||
Changing other fields using IF Function!? | Excel Worksheet Functions | |||
Can I use TODAY Function in formula without it changing the next . | Excel Discussion (Misc queries) |