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? 


0
rbosch (2)
12/30/2004 9:04:03 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
345 Views

Similar Articles

[PageSpeed] 32

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" <rbosch@adelphia.net> schrieb im Newsbeitrag 
news:e%23rFYMr7EHA.2804@TK2MSFTNGP15.phx.gbl...
> 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?
> 


0
frank.kabel (11126)
12/30/2004 9:13:48 PM
Thanks Frank, I was afraid it was something like that.

Renny


"Frank Kabel" <frank.kabel@freenet.de> wrote in message 
news:e$smvRr7EHA.1408@TK2MSFTNGP10.phx.gbl...
> 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" <rbosch@adelphia.net> schrieb im Newsbeitrag 
> news:e%23rFYMr7EHA.2804@TK2MSFTNGP15.phx.gbl...
>> 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?
>>
>
> 


0
rbosch (2)
12/30/2004 9:19:51 PM
On Thu, 30 Dec 2004 13:04:03 -0800, "Renny Bosch" <rbosch@adelphia.net> 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
0
ronrosenfeld (3122)
12/30/2004 9:22:56 PM
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? 
> 
> 
> 
0
PeoSjoblom (789)
12/30/2004 9:25:09 PM
> 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
0
PeoSjoblom (789)
12/30/2004 9:47:04 PM
On Thu, 30 Dec 2004 13:47:04 -0800, "Peo Sjoblom"
<PeoSjoblom@discussions.microsoft.com> 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
0
ronrosenfeld (3122)
12/31/2004 2:07:13 AM
Reply:

Similar Artilces:

Code Check Request
Dear all, I have two tables, POs and Orders. POs represent purchase orders from our client, and Orders represent internal orders. One PO can be related to many orders. Each order has a Sales Rep (data in a different table, ID in Orders as FK). The PO number is not a unique ID in the client order data (as one PO can cover many separate client orders). Graphically, the structure looks something like this (* indicates a unique ID): POs Orders Reps Client Order ID* ORDER ID* PO Code <---------> PO NUM SALES REP ID <------- SALES REP ID* In theory, each PO...

Unwanted zero values in a graph
Hi! Here I am once again. I have some graphs in a spreadsheet, and the sourcedata is being filled with values as time goes on and I key in new values every fisrt day of the month. Hence the cells containing future readings is empty. The graph jumps from the latest value lets say 40, and stoops down to zero on the X-axis, and follows it the for the rest of the year. How can I get my graphs to stop showing any line after the latest typed value? I have tried every possible combination when cliking the graph, choose Options on the Tools menu, and the tab named Graph, but in vain. Nothing happe...

Updating subform control based on another control value in the SAME subform.
Greetings, Calculated result is a field that is updated based on the two other fields, this is done in the subforms query and as based on an expression cannot be overtyped (which is good!). All I want to do is once a value appears in the field is to populate another field (see code below). Runs without errors but see no results. Any help would be appreciated. Private Sub CalculatedResult_AfterUpdate() Dim CResult As Integer CResult = Me.CalculatedResult.Value Me.ActualResult.Value = CResult Me.ActualResult.Requery End Sub Thanks Rob SOLVED "Rob W" <Whllrob@aol.com&g...

Need to return a value!
HI, I have eight columns of data. Each cell either has N/A (returned from another sheet) or a number, only one column will have a number. My question is, how do i show the number in another column?? Current SalesP Total Cost (L) 24,595.00 #N/A 18628.18 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 718 #N/A 718.18 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 17,140.00 #N/A 17140.00 #N/A #N/A #N/A #N/A #N/A #N/A #N/A Try this… =SUMIF(B1:J1,"<>#N/A",B1:J1) Change the cell reference of B1:J1 to your desired range, if required. If this post helps,...

Can I obtain x values from y values on an Excel chart?
I've constructed an exponential curve, is there a function whereby I can enter new y values to obtain the corresponding x values, instead of having to print it off and read off manually? Many thanks from a novice -Helen Moore if Y=A*Exp(BX); then Ln(Y) - Ln(A) = BX or X= (Ln(Y) -Ln(A))/B Also remember you can use LOGEST or LINEST to extract the trendline coefficients into cells rather than copying them form equation on chart; see http://www.stfx.ca/people/bliengme/ExcelTips/ExponentialTrendline&%20LOGEST.htm http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm best wishes...

Can I show a reference value with a string?
Hi All, For a single field, can I show a refernce value (ex: =B2+B3) and then follow up some strings? the final result looks like this: The price is 20 Thank you very much. Best regards, Boki. ="The price is "&B2+B3 -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=486137 If you want to use a format like currency you need to use the TEXT function ="The price is &q...

Why am I getting #VALUE! when working with a TIMEVALUE formula?
This formula is working on other pages within the same workbook, yet it does not work on a newly inserted worksheet. NOTE: this orginial workbook was created in an early version of Excel (95 or 98), now using 2000. Hi what is your exact formula and what is in the referenced cells? -- Regards Frank Kabel Frankfurt, Germany TIMEVALUE ERROR wrote: > This formula is working on other pages within the same workbook, yet > it does not work on a newly inserted worksheet. NOTE: this orginial > workbook was created in an early version of Excel (95 or 98), now > using 2000. The for...

Can you script preset values for forms?
I have a Third Party software that is based on Access 2003. They have a login screen that requires a few pieces of information (SQL Server Name, Port number, SQL Database name, ID and Password) in addition to the Database User Name and Password. Depending on which office I'm in, there is a different SQL Server I will need to connect to. What would be the easiest way to PREPOPULATE the infomation for the specific office so that a user only needs to click on an Icon? I know how to connect the approprieate drive via scripting, just unsure how to get the info populated. I'd ask ...

Cost basis < market value = gain?
I have a fund in Money that is showing: - cost basis of $500 - market value of $475 and gain of $65 How is that possible? I did have a large gain in this fund at one time and sold most of the holding. I thought maybe somehow that threw off Money but if that's the case, then there is a bug. In microsoft.public.money, brett wrote: >I have a fund in Money that is showing: > >- cost basis of $500 >- market value of $475 >and gain of $65 > >How is that possible? Distributions (from typical mutual funds, that would be dividends and LT CG paid) of $100 would explain i...

Formula for getting values in File-Properties-Custom
Is there a formula for retrieving values stored in the variables in File-Properties-Custom? Thx ...

Best way to get return value from a .bat file
Platform: VC7, MFC, windows 2000 Hi, What is best way to get return value from a .bat to the calling program. I am using bat file to get return from a bat file For example bat file test.bat sets a value in environment variable as given below set %1 = 12345 when i execute bat file from cmd shell c:\>test AAA this sets value AAA=12345. If I execute same command from our program using CreateProcess. GetEnvironmetVariable function fails for the environment variable AAA. May i know what mistake am i doing? Regards Vijay vijay wrote: > What is best way to get return value from a ...

Blank cell if no value
I have a spreadsheet in which I enter numbers in, then calculate a certain range - sum(f4:f10). The cell that contains this formula always shows 0 if nothing is entered in this range. How can I edit this forumla that will display nothing in the cell if nothing is entered, if something is entered, then it will display the sum Any suggestions? -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24735 View this thread: http://www.excelforum.com/showthread.php?threadid=397250...

Populating a UserForm'sTextBox with a value
Hi, I would like to know how to get a UserForm's TextBox to show the value of a cell when the UserForm is opened. I named the TextBox GiftTextBox, and I was hoping something like GiftTextBox.Value = ActiveCell.Offset(0, 6).Value would do it but I can't get that to work. Is there a simple way for the ActiveCell.Offset(0, 6).Value to be shown somehow on the UserForm? I don't care whether it's in a TextBox or some other box, as long as I can get the value to appear on the UserForm. Rob Rob, Something like... UserForm1.GiftTextBox.Value = ActiveCell.Offset(0, 6).Value...

Change the value of a text box in Form
Hi. I have a textbox named PseudoCLLI that and i want this Auto-generated suggested value equal to the ShortCLLI. How do i do this? PLUS, it is suggested meaning i can overwrite what's there. emerlita@shaw.ca wrote: > Hi. > > I have a textbox named PseudoCLLI that and i want this Auto-generated > suggested value equal to the ShortCLLI. How do i do this? PLUS, it is > suggested meaning i can overwrite what's there. In the AfterUpdate event of the ShortCLLI TextBox... Me.PseudoCLLI = Me.ShortCLLI -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBra...

How to get the corresponding X value for a given Y value?
I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/y1p8aB149EOMrBPPjH7oxLQUtlTXmrhiIspRq3r8fuZBNtdjVL_9ckuCh8zH_ZKlZwXt4POp6d2ay6fhDehApx_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya Hi Yahya, >I have a chart in Excel 2007 and I want to provide a Y value (that is not >one >...

Custom Format for Null or Zero Values
I am using the following custom format for null and zero values... #,##0.00;(#,##0.00)[Red];-;- ....but when my report runs all the columns do not line up. The dash ("-") for null or zero values is much farther right than the rest fo the values. I would like it to line up with the last decimal place showing. Please help... ...

Plotting multiple y values for the same x value in a scatter graph.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, i have been struggling with the scatter chart. I want excel to plot many to one values on a scatter graph. In other words, i have many y-values assigned to each x-value data point. Excel is averaging them out and producing one point. I would like to display all the points separately and not have them averaged out. Is this possible? In article <59bb6378.-1@webcrossing.JaKIaxP2ac0>, Kudatr@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.6 (Snow Leopard) > Proc...

#VALUE! problem; not enough cond formatting slots to fix.
I usu. deal with #VALUE! with conditional formatting and "iserror". In a current spreadsheet, I can't fix errors because I need alternating row colours which take 2 of the 3 conditional formatting slots. To fix this for the 2 colours of rows, I'd need an additional conditional formatting slot (i.e., 4 instead of 3, total) so that the font colours could be accommodated against 2 backgrounds. .... the cell that gets a #VALUE! in it is a calcuation between dates. The first date is typed into a cell to the left of this calculation one and it corresponds to the date tha...

Self Join Query / Second highest value in query
I have a table containing service history for vehicles. I'm trying to put together a report to display what services have been completed on each vehicle for the current period & if the services were carried out within a tolerance based on the vehicle mileage. I've tried a few combinations of dlookup, attempted self join queries etc. to determine the mileage of the service prior to the current one to work out these intervals between services. The second highest value in a query or dlookup/dmax seems to be beyond my reach. Could anyone point me in the best direction to overco...

Default Value on Form Help
I have a query that calculates the total of a field in my table - Field Name in my Table is Total Value which calculates the total for a series of records that has the same invoice number. Field in in my query is Aggregate Value. I now have a form where I bring in the Aggregate Value. I need to create another Text box Value Verified where I bring in the Aggregate Value as a default, but I may have to change it if the Value has changed - but the Aggregate Value has to remain the same. I tried using default value on the form for the Value Verified Text control that brings in ...

Find Value; Return Row number
Goodmorning everybody! I was wondering if someone could help me with the following problem: "I wanted to make a macro which would find the first value (counting from a1) that matches the value stated in C14 of "FORM". When it gives a match I want to return the row number to "Sheet2". I tested it with dates of which I was certain it had to find a match (as I entered them myself in "Sheet1") Still it returns: "Named Argument not Found" (Runtime error 448)" I hope someon sees my mistake(s). Thanks in Advance!! Sub TestDelete() l = Sheet...

posting values
on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use t...

Problem with .Values
Hi all, I have a Sub for construct graphic but if the number of vx is too large, ERROR at .Values = Y. Why ? Thank you for your help, Serge Sub ConstruireGraphiqueParTableauxVBA() Dim X() As Double Dim Y() As Double, i As Long, vx As Double ReDim X(i), Y(i) Application.ScreenUpdating = False ici = ActiveSheet.Name '********************************************* For vx = -15 To 15 'If the number of vx is too large, ERROR 'at .Values = Y. Why ? '********************************************* X(i) = vx Y(i) = vx ^ 2 i = i + 1 ReDim Preserve X(i), Y(i) Next vx Cha...

Changing positive values to negative
I’m using excel 2007with windows xp pro. I use excel very infreuently and not since upgrading to 2007. I have a long column of dollar values that I need to change from positive to negative values such as: $123.45 ($123.45) I can accomplish the feat manually over many hours by F2>.HOME>- > ENTER> DOWN ARROW and repeat and repeat and repeat. Or I can use a macro. But using a macro has changed from years ago. When I tried the macro inserted a specific value of cell rather than the value of the selected cell. So that didn’t work. What can make this conversion easier? If t...

adding values from 2 columns
I am trying to compose a personal cash flow chart where I can enter outgoings and income for every day of the month and see what the balance is on any day. I need to get a value in Col F which adds the value in Col C (daily income), subtracts the value in Col E (daily outgoings) and shows the balance. Also I may have to specify several rows if I have multiple outgoings on a particular day. Any ideas gratefully received. Gaby Sounds like you want a checkbook register. Something like this? A B C D E Start Bal $1,023.32 Date Item Expense Deposit Balance $10.12 $1,013.20 $23.3...