SOLVED

Calculate text formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2244636%22%20slang%3D%22en-US%22%3ECalculate%20text%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2244636%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20have%20been%20looking%20all%20the%20options%20for%20this%20scenario%20but%20haven't%20found%20any%20solution%2C%20or%20at%20least%20I%20have%20not%20been%20able%20to%20understand%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20set%20that%20I%20wish%20to%20calculate%2C%20where%20x%20should%20be%20replaced%20by%20a%20value%2C%20let's%20say%2010%2C%20and%20calculated%20as%20per%20this%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EFormula%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EResult%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3Ex%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3Ex%2B2%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E12%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2x%2B1%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E3x%2B5%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E35%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E10x%2B20%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E120%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2244636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2244649%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20text%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2244649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1012652%22%20target%3D%22_blank%22%3E%40Gerard_Jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20like%20in%20the%20attached%20workbook%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2244676%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20text%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2244676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20thanks%20for%20your%20prompt%20solution%2C%20but%20that%20wouldn't%20help%20as%20it%20is%20more%20of%20a%20manual%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20further%20elaborate%2C%20I%20have%201%2C750%20lines%2C%20so%20typing%20a%20formula%20for%20each%20line%20is%20not%20a%20practical%20solution%2C%20on%20the%20other%20hand%2C%20if%20some%20formulas%20change%2C%20the%20result%20should%20be%20recalculated%20accordingly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20something%20more%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(value_before_X)*(%22convert_x_to_value%22%2C10%2C)%2B(number)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20no%20value%20before%20X%2C%20assume%201%2C%20if%20no%20X%20at%20all%2C%20use%20only%20number%20after%20%2B.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2245112%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20text%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2245112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1012652%22%20target%3D%22_blank%22%3E%40Gerard_Jimenez%3C%2FA%3E%26nbsp%3BThen%20you'd%20need%20som%20VBA%20coding.%20A%20small%20user%20defined%20function%20that%20I%20called%20%3CFONT%20color%3D%22%23FF0000%22%3ETxtForm.%3CFONT%20color%3D%22%23000000%22%3E%20It%20takes%3C%2FFONT%3E%3C%2FFONT%3E%26nbsp%3Ba%20text%20string%20that%20%3CEM%3E%3CSTRONG%3Elooks%20like%3C%2FSTRONG%3E%3C%2FEM%3E%20a%20formula%20and%20%22translates%22%20it%20into%20a%20real%20formula.%20See%20attached.%3C%2FP%3E%3CP%3EI%20left%20the%20helper%20columns%20in%20there%20so%20that%20you%20can%20see%20how%20the%20text%20string%20in%20column%20E%20is%20constructed.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-03-30%20at%2020.30.04.png%22%20style%3D%22width%3A%20491px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F268302iD93A91B8A854214B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-03-30%20at%2020.30.04.png%22%20alt%3D%22Screenshot%202021-03-30%20at%2020.30.04.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all, have been looking all the options for this scenario but haven't found any solution, or at least I have not been able to understand them.

 

I have a formula set that I wish to calculate, where x should be replaced by a value, let's say 10, and calculated as per this example:

 

FormulaResult
11
22
x10
x+212
2x+121
3x+535
10x+20120

 

Any help would be greatly appreciated.

5 Replies

@Gerard_Jimenez 

Perhaps like in the attached workbook?

 

@Riny_van_Eekelen 

 

Hi, thanks for your prompt solution, but that wouldn't help as it is more of a manual formula.

 

To further elaborate, I have 1,750 lines, so typing a formula for each line is not a practical solution, on the other hand, if some formulas change, the result should be recalculated accordingly.

 

I need something more like:

 

=(value_before_X)*("convert_x_to_value",10,)+(number)

 

If no value before X, assume 1, if no X at all, use only number after +.

best response confirmed by Gerard_Jimenez (New Contributor)
Solution

@Gerard_Jimenez Then you'd need som VBA coding. A small user defined function that I called TxtForm. It takes a text string that looks like a formula and "translates" it into a real formula. See attached.

I left the helper columns in there so that you can see how the text string in column E is constructed.

Screenshot 2021-03-30 at 20.30.04.png

 

 

 

That did it.

I just needed to modify it a bit, in cases where there's no X, just the number. So I modified the helper columns like this:

Column =IF(ISNUMBER(FIND("x",C11)),FIND("x",C11),0)

Columen E: =IF(D11=0,C11,(IF(D11=2,LEFT(C11)&$B$1,LEFT(C11,D11-1)&"*"&$B$1)&RIGHT(C11,LEN(C11)-D11)))

I guess it's not the most elegant solution, but I'm not an expert like you.

Thanks a lot for your help.

@Gerard_Jimenez Elegance doesn't really matter. Like beauty, it's in the eye of the beholder. All that matters is that you succeeded to adopt my formulae by yourself and that it works for you.