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

On Sep 1, 12:52*am, exceluser <ifmcqy7aias...@yahoo.com> wrote:
> 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

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

On Sep 1, 3:03*am, exceluser <ifmcqy7aias...@yahoo.com> wrote:
> 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

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/tutorials...eflection.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

On Sep 3, 8:05*am, exceluser <ifmcqy7aias...@yahoo.com> wrote:
> 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

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