I have a mathematical expression in one cell. Say, A1 contains “300×1 + 200×2”. There is no equal sign in front of the expression. I want an equal sign to be put in front of the expression and obtain the result (ie. 700) in another cell. Is there a formula available for the same? It is not manually possible to put “=” in front of every expressions as the data contains huge no. of such expressions.
Do not enter any direct calculations inside the cell.
Clear would be e.g:
A1 contains 3001; B1 contains 2002
write in C1, =A1+B1
The issue in doing so, is that, I need to show the calculation so that it is visible. Hence, omitted “=” in front of it. In the next cell I would like to have the result, obtained. by putting an “=” in front of it. Is it possible to achieve this?
This is not difficult to do with a macro if you upload an example file.
There is no “Evaluate”-like functions in Calc.
You may, however, reverse your order: put =300*1+200*2 in B1, and =FORMULA(B1) in A1. You may further post-process the result of FORMULA function, like removing the leading = character, using usual spreadsheet functions.
2 Likes
1 Like
That is also convenient for me. Thank you.
Just for fun I made a “useless thing”. The code contained in the attached example can create working formulas from their equal-sign-less representation for a complete range of cells.
disask80459makeFormulasFromExpressions.ods (15.9 KB)
[Creating the working formulas in a probably hidden helper document can even be used to get an EVALUATE() function. It can, of course, not handle references correctly without a lot of additional overhead.]
1 Like
@JohnSUN:
I only joined that other forum in 2014, and didn’t know the linked post, but I like to joke sometimes in a similar way. “Brothers in Mind”? The trick to get a result shown in a sheet despite its being blocked against direct side-effects of user-functions may sometimes actually be useful. In fact I even used a complete hidden SpreadsheetDocument [created by loadComponentFromURL(“private:factory/scalc”, …)] now and then for efficient sorting and filtering by user code when I wanted to be sure on a paranoic level that no formula results could be changed due to an additionally inserted hidden sheet in the original document. [SHEETS() function? Of course, a user function may even return the number of loaded documents. There are no limits to paranoia?]
1 Like
Thank you. However, I have no idea about macros yet and have never used them. So I am going to learn.
We don’t know for what purpose you needed to calculate the value of the text expression. If this is a visual aid for children learning arithmetic, then the joke at the link above should completely satisfy you. If this is some kind of business project with calculated values and you need to explain where these numbers come from, then use FORMULA () (in this case, you do not need to know anything about macros)