Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 30th 04, 09:04 PM
Renny Bosch
 
Posts: n/a
Default 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   Report Post  
Old December 30th 04, 09:13 PM
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Old December 30th 04, 09:19 PM
Renny Bosch
 
Posts: n/a
Default

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   Report Post  
Old December 30th 04, 09:22 PM
Ron Rosenfeld
 
Posts: n/a
Default

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
  #5   Report Post  
Old December 30th 04, 09:25 PM
Peo Sjoblom
 
Posts: n/a
Default

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)

if not you can download Lauremt Longre's excellent add-in
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   Report Post  
Old December 30th 04, 09:47 PM
Peo Sjoblom
 
Posts: n/a
Default

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
  #7   Report Post  
Old December 31st 04, 02:07 AM
Ron Rosenfeld
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Module coded converted to Excel Function Adam Excel Discussion (Misc queries) 1 December 23rd 04 02:48 PM
Function in XL or in VBA for XL that pulls numeric digits from a t Nate Oliver Excel Discussion (Misc queries) 0 December 14th 04 04:57 PM
#VALUE in cell but pop up function box show right number Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 03:15 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 02:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 01:00 AM


All times are GMT +1. The time now is 12:41 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017