I have a spreadsheet where I enter text values in a cell, e.g. (.5 x .5) x 2 x .009. I want to find a way to automatically populated the cell to the right with the formula version, e.g. =(.5 * .5) * 2 * x .009 and format as three decimal place number.
One other question, is there a way to enter a template for entry in a cell. It would be nice if the text value above would take care of the parenthesis and multiplication signs for me.
Thanks, Judy
If I understand correctly you want say, in cell A2 to have (.5 x .5) x 2 x .009 then in cell B2 to pull the individual values and place them into a formula that is essentially the same but has the parens in order. I know you can do a cell that would accomplish the same but each value would have to be in its own cells. So A2 would have .5, B2 .5, C2 2 and D2 .009, then you could put the formula =(A2*B2)*C2*D2 in E2 and copy down (formatting the results in E2 to 3 decimal places).
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Maybe not what you had in mind but it works...
A2 = (.5 x .5) x 2 x .009
Enter this formula in B2:
="="&SUBSTITUTE(A2,"x","*")
With B2 still selected, Right Click>Copy, Right Click>Paste Special>Values>OK
CRTL H
Find what: =
Replace with: =
Replace All
Format as Number, 3 decimal places
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hello,
I am by far a pro, but see if this works for your first query.
=SUM(MID(A2,2,2)*MID(A2,7,2))*MID(A2,13,1)*MID(A2,17,4)
This results in a value of 0.0045, based on the figures you provided. If you are wanting the 3 decimal place result, 0.005, right click -> Format Cells - > select Number under Category -> then change the decimal places to 3, now it should show 0.005.
Hope this helps
Andrew
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks