How to evaluate a string in google spreadsheet

Question

I wonder if this is possible in google sheet:


cell A2 has string  100*2 + 50

How to get the result in another cell, such as A3 = evaluate(A2) and get 250?

I have shared public google sheet "stringEvaluate" here:

Required

Get the mathematical result from another string cell

=query(A2)   This gives the same string, does not evaluate

score 3 · Accepted Answer · answered Jun 07 '21 at 21:07

3

try:

=INDEX(QUERY(, "select "&A2), 2)

answered Jun 07 '21 at 21:07

player0

Hello! I'm trying to use your method above to evaluate a more complicated formula with functions, instead of just `100*2 + 50`. My formula uses the `SUMIFS` function along with `INDIRECT` to reference a relative cell location to where the function is being called. But let's say we're just trying to do evaluate something like `100*2 + 50 + max(100,2)`. When I try to do this with your `INDEX(QUERY(` method above, I get a `#VALUE!` error with `unable to parse query string` detail . Any suggestions? – Kim Hopkins Apr 20 '23 at 16:48
@KimHopkins you can do it if you hardcode it like this: `=INDEX(QUERY(, "select "&100*2 + 50 + MAX(100, 2)), 2)` but if you want to reference it from cell you need to separate `MAX(100, 2)` from it like `=INDEX(QUERY(, "select "&A2&"+"&MAX(100, 2)), 2)` because query is not able to process external function and that throws you the value error. another option is to do it like: `=INDEX(QUERY(, "select "&A2&"+"&A3), 2)` where A2 is `100*2+50` an A3 is your `MAX(100, 2)` – player0 Apr 20 '23 at 22:57