Home 
Search 
Today's Posts 
#1




Question on "Value" function
In trying to use Excel to help solve a mathematical puzzle, I would like to
calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? 
#2




Hi
this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Application.Evaluate("=" & op1.Value & operand.Value & op2.Value) End Function And now use in your spreadsheet in D1: =my_calculate(A1,B1,C1)  Regards Frank Kabel Frankfurt, Germany "Renny Bosch" schrieb im Newsbeitrag ... In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? 
#3




Thanks Frank, I was afraid it was something like that.
Renny "Frank Kabel" wrote in message ... Hi this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Application.Evaluate("=" & op1.Value & operand.Value & op2.Value) End Function And now use in your spreadsheet in D1: =my_calculate(A1,B1,C1)  Regards Frank Kabel Frankfurt, Germany "Renny Bosch" schrieb im Newsbeitrag ... In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? 
#4




On Thu, 30 Dec 2004 13:04:03 0800, "Renny Bosch" wrote:
In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? You are missing the fact that the VALUE function does not work on strings unless they represent a number. In other words, it does not work on formulas, or operators (such as the '+'). Excel does not have a built in function to do what you wish. However, if you Google for Longre, and/or morefunc.xll, you will find the morefunc (free) addin which contains a function called EVAL, which can convert a formulaic string to it's result. However, be sure that you are concatenating strings, and not a combination of numbers and strings. So your formula would be something like: =EVAL(CONCATENATE(TEXT(A1,"@"), B1, TEXT(C1,"@"))) or =EVAL(TEXT(A1,"@")& B1 & TEXT(C1,"@")) ron 
#5




You would need VBA for this, of course if it is as simple as in your example
with 2 cells and just the operand in one you can use =CHOOSE(MATCH(B1,{"*","+","","/"},0),A1*C1,A1+C1,A1C1,A1/C1) if not you can download Lauremt Longre's excellent addin it has a function called EVAL used as =EVAL(CONCATENATE(A1,B1,C1)) it's here http://longre.free.fr/english/ English description here http://www.rhdatasolutions.com/morefunc/ Regards, Peo Sjoblom "Renny Bosch" wrote: In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? 
#6




However, if you Google for Longre, and/or morefunc.xll, you will find the
morefunc (free) addin which contains a function called EVAL, which can convert a formulaic string to it's result. However, be sure that you are concatenating strings, and not a combination of numbers and strings. So your formula would be something like: =EVAL(CONCATENATE(TEXT(A1,"@"), B1, TEXT(C1,"@"))) or =EVAL(TEXT(A1,"@")& B1 & TEXT(C1,"@")) Actually this will work with number, operand, number =EVAL(CONCATENATE(A1,B1,C1)) Regards, Peo Sjoblom 
#7




On Thu, 30 Dec 2004 13:47:04 0800, "Peo Sjoblom"
wrote: However, if you Google for Longre, and/or morefunc.xll, you will find the morefunc (free) addin which contains a function called EVAL, which can convert a formulaic string to it's result. However, be sure that you are concatenating strings, and not a combination of numbers and strings. So your formula would be something like: =EVAL(CONCATENATE(TEXT(A1,"@"), B1, TEXT(C1,"@"))) or =EVAL(TEXT(A1,"@")& B1 & TEXT(C1,"@")) Actually this will work with number, operand, number =EVAL(CONCATENATE(A1,B1,C1)) Regards, Peo Sjoblom Thanks for pointing that out. I had tried A1:C1 and got an error. ron 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Access Module coded converted to Excel Function  Excel Discussion (Misc queries)  
Function in XL or in VBA for XL that pulls numeric digits from a t  Excel Discussion (Misc queries)  
#VALUE in cell but pop up function box show right number  Excel Discussion (Misc queries)  
Excel function help facilities  Excel Discussion (Misc queries)  
I cant use englisch function names in a swedich version of excel  Excel Discussion (Misc queries) 