Remember Me?

 Renny Bosch Posts: n/a 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?

 Frank Kabel Posts: n/a 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

=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?

 Renny Bosch Posts: n/a 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

=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?

 Ron Rosenfeld Posts: n/a 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) add-in 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
 Peo Sjoblom Posts: n/a 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,A1-C1,A1/C1)

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?

 Peo Sjoblom Posts: n/a However, if you Google for Longre, and/or morefunc.xll, you will find the
morefunc (free) add-in 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
 Ron Rosenfeld Posts: n/a 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) add-in 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Adam Excel Discussion (Misc queries) 1 December 23rd 04 02:48 PM Nate Oliver Excel Discussion (Misc queries) 0 December 14th 04 04:57 PM Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 03:15 PM RPS Excel Discussion (Misc queries) 1 December 8th 04 02:36 AM PE Excel Discussion (Misc queries) 2 December 7th 04 01:00 AM

All times are GMT +1. The time now is 09:23 PM. Copyright ©2004-2020 ExcelBanter.