How can you convert a text value to a numerical formula ?

  • Thread starter Thread starter exceluser
  • Start date Start date
E

exceluser

How can you convert a text value to a numerical formula ?

If A1 contains

ABC(20)

and you use SUBSTITUTE to replace ABC with 10, replace ( with * and
replace ) with "", A1 would display

10*20

How can you get the 10*20 value to return a numerical value of 200 ?


Exceluser
 
You could make use of a user-defined function like this one:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

and you would use it like:

=eval(A1)

where A1 contains an string which is a valid Excel expression.

Hope this helps.

Pete
 
Pete,

Thanks for the quick response.

I haven't used UDFs before and judging by what I've read regarding
their volatility, they have the notoriety of being processor
intensive.

Below is a “simplified” version of what I'm trying to do.

The following is on a worksheet called INVENTORY.

Date Item Qty Total_Inventory
1/1/2010 Apples 1 Apples(1)
1/2/2010 Oranges 1 Apples(1),Oranges(1)


The following is on a worksheet called PRICES.

Date 1/1/2010 1/2/2010
Apples $0.50 $0.25
Oranges $1.00 $0.75


On a worksheet called INV_CHART, I’m using the following as a data
source for a chart that tracks the value of the inventory by date.

Date Inventory_Value
1/1/2010 $0.50
1/2/2010 $1.00

The Inventory_Value column is populated with a formula that VLOOKUPs
items in the Total_Inventory column by date (on the INVENTORY
worksheet) and multiplies the quantity in the inventory by the price
of that item on a particular date (on the PRICES worksheet).

Basically, this would show the inventory’s value is depreciating over
time since the store isn’t selling out each item at full price.

Without success, I was trying to create a formula in the
Inventory_Value column on the INV_CHART worksheet. For example, to
determine the Inventory_Value on 1/2/2010:

1) Use VLOOKUP to get the contents of the Total_Inventory column
for 1/2/2010 = "Apples(1),Oranges(1)".

2) Create a formula that replaces the item names (apples, oranges,
etc.) with the price of that item for that day = ".25(1),.75(1)".

3) Create a formula that multiplies the item's price by its
quantity = ".25,.75".

4) SUM the values separated by commas.

See ... it's easy, right ?


Exceluser
 
I did advise you when you first posted this a few weeks ago that by
insisting on concatenating the inventory you would cause yourself
problems later on. I advised you to have a column for each inventory
item (which you can then concatenate easily if you want to see it in
that form), something along the lines of:

Date Item Qty Apples Oranges (Others...) Total
1/1/2010 Apples 1 1 0
(concat)
1/2/2010 Oranges 1 1 1

The appropriate column gets incremented or decremented by matching
with the item. These columns could be hidden and the total_inventory
column could concatenate from the others to put it in the form you
require.

Hope this helps.

Pete
 
The issue is that the field, when the contents is original ABC(10), is actually 'ABC(10). And what you want to do is change such to =ABC(10).

Though there is a function called VALUE(), this only works with results. So VALUE("12") [or VALUE(D1) where D1 contains '12] converts say '12 to =12, but VALUE ("12*10") does not work as EXCEL only transfers the final product.

You could consider a macro.

Public Sub sample()

Dim r As Range
Dim y As Range
Dim t As String

Set r = Selection

For Each y In r.Cells

t = y.Cells
t = Replace(Replace(Replace(t, "(", "*"), "ABC", "20"), ")", "")

If t <> y.Cells Then
t = "=" & t
End If

Debug.Print y.Address, "Original Contents=", y.Cells, ". New=", t

y = t

Next


End Sub


Here, instead of SUBSTITUTE, the macro has the terms you want to change.

It will process the cells that you highlight before you call the macro.



Submitted via EggHeadCafe - Software Developer Portal of Choice
ObjectDumper LINQ To Export Collection Via .NET Reflection
http://www.eggheadcafe.com/tutorial...-to-export-collection-via-net-reflection.aspx
 
Pete,

You're absolutely right.

What I'm trying to do with Excel is create a query that is more
suited to an SQL client and a relational database and not a
spreadsheet.

But that only applies if I'm trying to use Excel with regular
functions and not UDFs or macros - which is what I was hoping to do.

As Eric mentioned above, the key is inserting an equal sign in
front of the text to get Excel to recognize the cell's contents as a
formula.

Is that what your UDF above would do ?

Since, I've never used or created a UDF, I wouldn't know how to
register the code you wrote above.

But at least it would be easy to maintain. :)


Exceluser
 
Eric,

You are so on the money it's not even funny.

While it will make my lack of coding skills obvious (Visual C++ 1.0
many years ago), I can attest that trying to CONCATENATE an equal sign
before Apples(1) does not work.

Nor, as you mentioned, does attempting to convert the cell's
contents with the VALUE function.

Formatting the cell as a number is equally useless.

Hopefully, the Excel development team will extend the capabilities
of the VALUE function in a future version to support this type of
conversion.

I appreciate the time and effort you put into the macro above.

At this point, I just have to figure out how to get the macro
working in my workbook - since I don't have any experience with
macros.

Given the four steps I listed above, would this macro be able to
parse each unique item and its quantity ?

The standardized format for the Total_Inventory column is each
unique item's name is appended with its quantity in parentheses. For
example, Apples(1). Where there are multiple unique items, they are
separated by a comma. For example, Apples(1),Oranges(1).

For example, in the scenario above, the row containing
"1/2/2010 Oranges 1 Apples(1),Oranges(1)" has a
Total_Inventory value of "Apples(1),Oranges(1)".

Keep in mind the number of unique items in a Total_Inventory cell
will vary from no items to many. At this point, the maximum number of
items will probably be 10, but could be as high as 25.

Also, if a UDF is volatile, meaning that Excel will recalculate UDF
containing cells whether or not their dependencies have changed, how
might the use of this macro affect processing performance ? Would a
macro like this be equally volatile ?

Again, thank you for putting time in your post above.




Exceluser
 
If you don't want to use a UDF then you can use a named formula and
make use of the poorly-documented EVALUATE function (actually, this is
a throw-back to older versions of Excel).

As an example of how to use this, start with a new workbook and select
cell A1. If you are using XL2007 click on the Formulas tab and then
under the Defined Names section click on Define Name. If you are using
XL2003 or earlier, click on Insert | Name | Define. In both cases you
will then have a dialogue box, so put a suitable name in the top box
(eg text) and then in the Refers To section at the bottom (which will
show =Sheet1!$A$1) change this to:

=EVALUATE(Sheet1!$A$1)

Click OK to exit the dialogue box.

Now put some text in A1 which represents some Excel expression, eg
'2+3. In D1 you can enter the formula:

=text

(i.e. the name that you used to define A1), and you should see the
result 5 in D1. Note that you don't need the equals sign in A1, though
it will still work if you put:

'=2+3

You can also make use of cell references. Put 17 in B1 and 23 in C1
and then change the text in A1 to:

b1 + c1

and then the value displayed in D1 will change to 40 - it is
evaluating the string in A1 as if it were a valid Excel formula.

Can you see how you might apply this to your own situation?

Hope this helps.

Pete
 
Pete,

You know ... you should do this computer stuff for a living.

I used the EVALUATE defined name function with a relative row
reference ($A1) and was able to successfully copy down with sample
test data.

I haven't used it with the final formula yet since it doesn't exist
at this point.

Now that I see what EVALUATE does, I understand your UDF better and
would use the UDF over the defined name function.

My main concern is future compatibility. Microsoft has kept the
EVALUATE function around for a long time although it wouldn't surprise
me if support for it quietly ended on my next Excel version upgrade.
Currently, I'm using Excel 2007.

Your EVAL UDF solution is short, easy to read and works.

Is there a way to make the UDF non-volatile ? After filling down
50,000 rows of EVAL(10*20), it adds about 11 seconds of calculating
time after any cell change.

Is there a performance advantage between using this UDF and a macro
that does the same thing ?



Exceluser
 
Pete,

After working with the UDF, I made the following change:

Function EVAL(func As String)
Application.Volatile (False)
EVAL = Evaluate(func)
End Function


Making it non-volatile didn't seem to prevent it from coming up
with the correct result and it eliminated the unnecessary
recalculation.

Do you see a problem with this ?


Exceluser
 
The advantage of using a UDF is that you can put in a cell:

=UDF(whatever)

but you can't put:

=MACRO(whatever)

If you used a macro it would be a one-off thing, eg click a button and
it would evaluate all the strings in a range. That might be more
suitable for you, as your inventory would not change frequently (from
your examples, you seem to have one item per day !!)

I still think it would be better to have a table of numeric values for
each inventory item, and then form the concatenated string from that
table.

Hope this helps.

Pete
 
Pete,

Finally got the whole thing working in all its convoluted glory.

The key was the EVAL UDF.

Many thanks for your repeated support.

You earned your 5 stars.



Exceluser
 
Hi Pete_UK,

This is the first time I am seeing you that you have started to
provide Macro support. I am glad that you have learned macro also.
 
Hi Pete_UK,

Thanks for giving reply to me, may be you might have given the VBA
solution in March 2006, but I have started to participate in
Microsoft Discussion Groups from 2009 onwards. From 2009 onwards I am
read most of your posts and I never seen you in providing VBA
solution. In most of the cases you used to give solution using the
Worksheet Functions and many times I was surprised since I used to
read and leave the posts assuming that this can be done only by using
VBA. But in many instances you have provided extreme solutions using
the worksheet functions which I thought it can be solved only by using
VBA. I am the regular reader of all the experts answer and I didn't
noticed you in providing VBA solution from 2009 as far as I read. You
are the one of the Roll Model for me. In fact I was gained / gaining
lot of knowledge from the experts like you...
 
Well, thanks for the vote of confidence.

The people I work with tend to be suspicious of the word "macro", and
prefer formula solutions where possible, so I have not had much
practice with VBA during the last few years - I generally use it just
as a hobby for myself and, like you, read others' posts on it. Every
now and then I will answer with a VBA solution, so keep reading ...

I like to tackle bigger problems with VBA and am very interested in
improving algorithms to make things run faster. Here's another example
from 2007:

http://groups.google.com/group/micr...3c3576f48999b443?lnk=gst&q=madenning+dilemma#

Pete
 
Back
Top