Forum Discussion
Hi! I Need help with a formula in Excel
Given(for example in cell A1): 1@50,10@100,15@250
Problem:
Perform the following formula (for example in cell A2): 1*50 + 10*100+15*250
Can anyone help and provide the combination of functions which can do the formula I intend to do? The given example is in a single cell and the solution I prefer should also be in a single cell (which should result to 4800). For Clarity I posted an attachment of the actual given and result I desire.
Thank you very much!
Hi Kristian,
Two ways what I know, both not quite suitable.
First, use macro with Evaluate VBA function https://www.extendoffice.com/documents/excel/1683-excel-convert-text-string-to-formula.html
Second use undocumented EVALUATE function without the macro https://www.vertex42.com/ExcelArticles/evaluate-function.html
In both cases you have to convert your string into the text with formula, for the second approach could be done as
=(SUBSTITUTE(SUBSTITUTE(A1,",","+"),"@","*"))
With that in name manager you could define the name, let say GetResult, as
=EVALUATE((SUBSTITUTE(SUBSTITUTE(Sheet1!$A$1,",","+"),"@","*")))
and receive that result as
=GetResult
Side effect in both cases you shall keep your workbook as macro enabled one.
5 Replies
If you have time, you can build the formula.
The common knowledge is that, with SUBSTITUTE-function (https://support.office.com/en-us/article/SUBSTITUTE-function-6434944e-a904-4336-a9b0-1e58df3bc332), you can replace a specific text with another text string. This function can help another function FIND-FINDB-functions (https://support.office.com/en-us/article/FIND-FINDB-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628).
To find the Nth occurance location of a specific text in another text cannot be done by Find function itself only. However we can do this, e.g. to find the 2nd "," in A1
=FIND("$", SUBSTITUTE(A1, ",", "$", 2))where SUBSTITUTE(A1, ",", "$", 2) will turn your A1 cell to "1@50,10@100$15@250", and since this text contains only 1 dollar sign ("$"), the find function can locate it, which was the original second comma.
In addition, LEFT-function, RIGHT-function and MID-MIDB-function can help you to extract a specific text for your operations. Then, you can get the formula.
=VALUE(LEFT(A1,FIND("@",A1)-1))*VALUE(RIGHT(LEFT(A1,FIND(",",A1)-1),LEN(LEFT(A1,FIND(",",A1)-1))-FIND("@",A1))+VALUE(LEFT(MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255),FIND("@",MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255))-1))*VALUE(RIGHT(MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255),LEN(MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255))-FIND("@",MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,",","$",2))-1),FIND(",",A1)+1,255))))+VALUE(LEFT(MID(A1,FIND("$",SUBSTITUTE(A1,",","$",2))+1,255),FIND("@",MID(A1,FIND("$",SUBSTITUTE(A1,",","$",2))+1,255))-1))*VALUE(RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,"@","$",3)))))I have no time and idea on how to simplify the above formula, but you can use it.
- Thanks for this. I used a macro instead of simplicity.
Hi Kristian,
Two ways what I know, both not quite suitable.
First, use macro with Evaluate VBA function https://www.extendoffice.com/documents/excel/1683-excel-convert-text-string-to-formula.html
Second use undocumented EVALUATE function without the macro https://www.vertex42.com/ExcelArticles/evaluate-function.html
In both cases you have to convert your string into the text with formula, for the second approach could be done as
=(SUBSTITUTE(SUBSTITUTE(A1,",","+"),"@","*"))
With that in name manager you could define the name, let say GetResult, as
=EVALUATE((SUBSTITUTE(SUBSTITUTE(Sheet1!$A$1,",","+"),"@","*")))
and receive that result as
=GetResult
Side effect in both cases you shall keep your workbook as macro enabled one.
- hi i didn't know how to it i think am gonna need moore help
- Thank you very much for the help!
