Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula with text values
Hi,
In cell A1 I have "3*50+1*120+2*25", with och without ", that doesn't matter in this case. The value in A1 is for display for the user, so that the user can see what values being summarized into cell A2. Now, for the question. How can a write a formula that makes a SUM in cell A2 that uses the values in cell A1? If I put a = in cell A1 Excel will make the calculation but if I want that value to show in cell A2? Best regards, /Combo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula with text values
I don't know how to do that without the = sign in a1 but this may work. In A1
type '=3*50+1*120+2*25 You won't see the ' select A1 and Data|text to columns Fixed Width Next Next In the destination box type $A$2 Click finish Mike "Combo" wrote: Hi, In cell A1 I have "3*50+1*120+2*25", with och without ", that doesn't matter in this case. The value in A1 is for display for the user, so that the user can see what values being summarized into cell A2. Now, for the question. How can a write a formula that makes a SUM in cell A2 that uses the values in cell A1? If I put a = in cell A1 Excel will make the calculation but if I want that value to show in cell A2? Best regards, /Combo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula with text values
You question is confusing, in A2 you want to see a sum using A1 and other
cells AND the value of A1? You can't see both. "Combo" wrote: Hi, In cell A1 I have "3*50+1*120+2*25", with och without ", that doesn't matter in this case. The value in A1 is for display for the user, so that the user can see what values being summarized into cell A2. Now, for the question. How can a write a formula that makes a SUM in cell A2 that uses the values in cell A1? If I put a = in cell A1 Excel will make the calculation but if I want that value to show in cell A2? Best regards, /Combo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula with text values
If I understand your request correctly, you have a text representation of a
formula in A1, which may or may not include double quotes. In A2, you want to see what this formula would yield if if was a real formula, not just text. Try the following user-defined function (paste it into a VBA module in your workbook): Function RetVal(Target As Range) As Variant Dim str As String 'Target must be a single cell If Target.Count 1 Then RetVal = vbNullString Exit Function End If 'Remove double quotes, if any. str$ = Replace(Target.Value, """", vbNullString) 'Evaluate the cell entry. RetVal = Evaluate("=" & str$) End Function In A2, you would call this function like this: =RetVal(A1) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Hope this helps, Hutch "Combo" wrote: Hi, In cell A1 I have "3*50+1*120+2*25", with och without ", that doesn't matter in this case. The value in A1 is for display for the user, so that the user can see what values being summarized into cell A2. Now, for the question. How can a write a formula that makes a SUM in cell A2 that uses the values in cell A1? If I put a = in cell A1 Excel will make the calculation but if I want that value to show in cell A2? Best regards, /Combo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want a formula to ignore text values in cell references | New Users to Excel | |||
formula mixed text and values | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Formula to sort text values with spaces | Excel Discussion (Misc queries) | |||
How do I formula a percentage with text values in the data field | Excel Worksheet Functions |